Bounded UTF-16 text fetches drop leading U+FEFF or corrupt leading U+FFFE payloads

[jahnvi480]

Describe the bug

Bounded SQL text values beginning with U+FEFF or U+FFFE are not consistently preserved when fetched as UTF-16. Leading U+FEFF can be silently removed, while leading U+FFFE can trigger byte-order autodetection and corrupt the remaining characters.

SQL UNICODE, DATALENGTH, and binary evidence independently confirm these code points are part of the stored SQL value. They must not be consumed as encoding metadata by the Python driver.

The affected paths are the bounded NVARCHAR and CHAR/VARCHAR-as-wide row-wise conversions, plus the Linux/macOS bounded wide-text batch conversions. Before the correction, Windows batch fetching preserved the payload, while Windows row-wise fetching did not. Adding an unrelated MAX column could change the result by selecting row-wise fetching.

Exception message: N/A for the reproduced valid payloads; this is silent data corruption.
Stack trace: N/A.

To reproduce

Build/install the affected driver and set DB_CONNECTION_STRING through the environment. This SELECT-only reproduction prints independent SQL evidence and results for both prefixes, all three fetch methods, and bounded columns with/without a MAX neighbor. It does not print credentials or require persistent tables.

import os
from mssql_python import connect

with connect(os.environ["DB_CONNECTION_STRING"]) as connection:
    for prefix in (0xFEFF, 0xFFFE):
        expected = chr(prefix) + "BOM"
        expression = f"CAST(NCHAR({prefix}) + N'BOM' AS nvarchar(32))"
        relation = f"FROM (VALUES ({expression})) AS p(payload)"
        with connection.cursor() as cursor:
            cursor.execute(
                "SELECT UNICODE(payload), DATALENGTH(payload), "
                f"CAST(payload AS varbinary(64)) {relation}"
            )
            first, length, raw = cursor.fetchone()
            assert first == prefix
            assert length == len(expected.encode("utf-16le"))
            assert raw == expected.encode("utf-16le")
            print("SQL evidence:", hex(first), length, raw.hex())

        for method in ("fetchone", "fetchmany", "fetchall"):
            for beside_max in (False, True):
                extra = ", CAST(N'route' AS nvarchar(max))" if beside_max else ""
                with connection.cursor() as cursor:
                    cursor.execute(
                        f"SELECT payload {extra} {relation} "
                        "CROSS JOIN (VALUES (1), (2)) AS r(row_id) ORDER BY row_id"
                    )
                    rows = []
                    while True:
                        if method == "fetchone":
                            row = cursor.fetchone()
                            batch = [] if row is None else [row]
                        elif method == "fetchmany":
                            batch = cursor.fetchmany(2)
                        else:
                            batch = cursor.fetchall()
                        if not batch:
                            break
                        rows.extend(batch)
                    actual = [row[0] for row in rows]
                    print(
                        hex(prefix), method, beside_max,
                        "expected:", repr(expected),
                        "actual:", repr(actual),
                        "matches:", actual == [expected, expected],
                    )

Observed examples: stored \ufeffBOM returns BOM; stored \ufffeBOM can return \u4200\u4f00\u4d00.

Expected behavior

Return the exact SQL text payload, including leading U+FEFF/U+FFFE, independently of fetch method and unrelated columns in the result. An ODBC wide-character value has a defined representation and explicit length; its first character must not silently select endianness or disappear.

Preserve existing NULL/empty handling, valid Unicode/astral/embedded-NUL payloads, and the existing platform-specific malformed-input error/fallback behavior. Applications that intentionally strip a leading U+FEFF should do so explicitly.

Further technical details

Python version: reproduced locally on CPython 3.13.15 with pybind11 3.0.1.
SQL Server version: local reproduction SQL Server 15.0.4382.1; additional PR CI SQL Server 2022/2025 jobs.
Operating system: Windows AMD64 local reproduction; Ubuntu/RHEL/Alpine x64 CI independently exposed the still-unfixed Unix batch paths in the initial fix revision. Linux/macOS share those batch conversion sites; no unobserved macOS success is claimed.

Additional context

• Draft fix: FIX: Preserve leading BOM-like characters in bounded text fetches #793. Current follow-up commit: c706b5647b039e3742aec8253eac6bb9c922d4ac.
• Original Windows bounded-BOM matrix: 4/12 baseline -> 12/12 after the row-wise correction; expanded Windows suite: 34/50 -> 50/50.
• Original ADO run 176087 exposed eight bounded batch BOM failures on inspected Ubuntu/RHEL/Alpine x64 jobs (42/50 new tests passed), while Windows LocalDB/Python 3.14 passed all 50. Those failures motivated the two-site Unix follow-up; tests were not skipped or weakened.
• Follow-up run 176093 validates 54 cases: the original 50 unchanged, plus four platform-aware malformed NVARCHAR batch controls. Consult the run for current results; earlier Windows-only results are not a claim of full cross-platform readiness.
• This is a correctness bug, not a demonstrated latency or memory optimization.
• Actual NVARCHAR(MAX) leading-BOM loss and VARCHAR/NVARCHAR(MAX) trailing-NUL loss are separate existing defects and remain outside this bounded-text fix.
• Tracking ADO Bug: 48253, a child of user story 43702, in mssql-python\Rubidium\Sept 2026. The bug links this issue and draft PR FIX: Preserve leading BOM-like characters in bounded text fetches #793.

[github-actions]

Hi Jahnvi Thakkar (@jahnvi480), thank you for opening this issue!

Our team will review it shortly. We aim to triage all new issues within 24-48 hours and get back to you.

If you have additional information to share, please feel free to update the issue.

Thank you for your patience!