构建SQL Server TDS响应包遇collation空值错误及无查询结果问题

架构老林

2026-6-17

TDS响应包构建错误排查与修复方案

问题概述

我正在开发一款针对SQL Server的SQL代理，流程为接收DBeaver的TDS请求包，解析后通过pyodbc执行SQL，再重构TDS响应包返回客户端。目前未启用SSL，直接用pyodbc连接SQL Server。

代理核心流程：

客户端向代理发送数据包
代理解析并判断是否为SQL批处理（如SELECT db_name(), schema_name(), original_login();）
代理通过pyodbc执行查询
重构TDS响应包返回客户端

当前遇到两个关键问题：

DBeaver断开连接并报错：Cannot invoke "com.microsoft.sqlserver.jdbc.SQLCollation.writeCollation(com.microsoft.sqlserver.jdbc.TDSWriter)" because "collation" is null
生成的TDS包中无SQL查询结果

现有响应包构建代码

def construct_tds_response(columns, rows):
     # Step 1: Create Packet Header
    packet_type = 0x04  # Type: Response
    packet_status = 0x01  # Status: End of message
    packet_length = 0x0033  # Length: Placeholder value for now, will be updated later
    header = construct_tds_header(packet_type, packet_status, packet_length)

    # Step 2: Create COLMETADATA (Token Type: 0x81)
    colmetadata_token = pack('<B', 0x81)  # COLMETADATA Token Type
    col_count = pack('<H', len(cursor.description))  # Number of columns

    colmetadata = colmetadata_token + col_count

    # Add metadata for each column
    for col in cursor.description:
        col_name = col[0]
        user_type = pack('<L', 0x00000000)  # UserType (0)
        flags = pack('<H', 0x0020)  # Flags (Nullable)
        col_type = pack('<B', 0xA7)  # Data Type (NVARCHAR)
        col_max_length = pack('<H', 0x1F40)  # Max Length (8000)
        # Use a proper collation value; this should be dynamically determined if needed
        collation = bytes([0x09, 0x04, 0xD0, 0x00, 0x34])  # Collation (5 bytes)
        col_name_len = pack('<B', len(col_name))  # Column Name Length
        col_name_encoded = col_name.encode('utf-16le')  # Column Name (UTF-16LE)

        colmetadata += user_type + flags + col_type + col_max_length + collation + col_name_len + col_name_encoded

    # Step 3: Create ROW Data (Token Type: 0xD1)
    rows = cursor.fetchall()
    row_data = b''
    for row in rows:
        row_token = pack('<B', 0xD1)  # ROW Token Type
        row_content = b''
        for col_value in row:
            if col_value is None:
                row_content += pack('<H', 0xFFFF)  # Null value indicator
            elif isinstance(col_value, str):
                col_data = col_value.encode('utf-16le')
                col_len = len(col_data)  # UTF-16 length in bytes
                row_content += pack('<H', col_len) + col_data
            else:
                col_data = str(col_value).encode('utf-16le')
                col_len = len(col_data)
                row_content += pack('<H', col_len) + col_data

        row_data += row_token + row_content
    # Step 4: Create DONE Token (Token Type: 0xFD)
    done_token = pack('<B', 0xFD)  # DONE Token Type
    done_status = pack('<H', 0x0010)  # DONE Status
    cur_cmd = pack('<H', 0x00C1)  # Current Command
    done_row_count = pack('<Q', len(rows))  # Row count

    done = done_token + done_status + cur_cmd + done_row_count

    # Combine all parts to form the complete packet
    tds_data = colmetadata + row_data + done

    # Update packet length
    packet_length = len(tds_data) + 8  # Header length is always 8 bytes
    header = construct_tds_header(packet_type, packet_status, packet_length)

    # Combine header and data to form the final TDS packet
    tds_packet = header + tds_data

    return tds_packet

生成的TDS数据包

b'\x04\x01\x00E\x00\x00\x01\x00\x81\x03\x00\x00\x00\x00\x00 \x00\xa7@\x1f\t\x04\x00\x00\x00\x00\x00\x00\x00\x00 \x00\xa7@\x1f\t\x04\x00\x00\x00\x00\x00\x00\x00\x00 \x00\xa7@\x1f\t\x04\x00\x00\x00\x00\xfd\x10\x00\xc1\x00\x01\x00\x00\x00\x00\x00\x00\x00'

错误分析与修复方案

1. Collation空值错误修复

错误原因：COLMETADATA中的collation字段长度不符合MS-TDS规范。NVARCHAR类型对应的collation结构应为6字节（LCID(4字节) + Flags(1字节) + Version(1字节)），但当前硬编码的是5字节，导致客户端解析时无法识别为有效collation。
修复方式：将collation改为6字节的合法值，例如对应SQL_Latin1_General_CP1_CI_AS的collation：
```
collation = bytes([0x09, 0x04, 0xD0, 0x00, 0x00, 0x01])
```

进阶优化：可以从SQL Server获取真实的collation值，避免硬编码：

cursor.execute("SELECT SERVERPROPERTY('CollationID')")
collation_id = cursor.fetchone()[0]
# 转换为TDS格式的6字节collation
collation = pack('<LBB', collation_id, 0x00, 0x01)

2. 查询结果缺失修复

错误点1：重复fetch导致数据为空
代码中rows = cursor.fetchall()覆盖了函数参数传入的rows，如果调用此函数前cursor已经执行过fetch，会导致row_data为空。
修复：移除函数内的rows = cursor.fetchall()，直接使用传入的rows参数，或确保cursor在调用前未被fetch。
错误点2：行数据长度格式错误
NVARCHAR类型的行数据长度字段应表示字符数（而非字节数），当前代码传入的是UTF-16LE的字节长度，导致客户端无法正确解析数据。
修复：将长度计算改为字符数：
```
col_len = len(col_data) // 2  # UTF-16LE每个字符占2字节
```
错误点3：数据包长度计算验证
确保construct_tds_header函数将packet_length以2字节小端序打包，因为TDS头部的长度字段是2字节小端格式。

3. 其他潜在问题修复

COLMETADATA列名长度：当前用len(col_name)是正确的（表示字符数），但需确保列名编码为UTF-16LE。
DONE Token的CurCmd值：0x00C1属于特定场景值，建议改为通用的0x0001（默认命令ID）。
函数参数冗余：原函数的columns参数未被使用，可删除并调整函数签名为construct_tds_response(cursor, rows)。

修正后的代码示例

def construct_tds_response(cursor, rows):
    # Step 1: Create Packet Header
    packet_type = 0x04  # Type: Response
    packet_status = 0x01  # Status: End of message
    packet_length = 0  # Placeholder, will be updated later
    header = construct_tds_header(packet_type, packet_status, packet_length)

    # Step 2: Create COLMETADATA (Token Type: 0x81)
    colmetadata_token = pack('<B', 0x81)  # COLMETADATA Token Type
    col_count = pack('<H', len(cursor.description))  # Number of columns
    colmetadata = colmetadata_token + col_count

    # Add metadata for each column
    for col in cursor.description:
        col_name = col[0]
        user_type = pack('<L', 0x00000000)  # UserType (0)
        flags = pack('<H', 0x0020)  # Flags (Nullable)
        col_type = pack('<B', 0xA7)  # Data Type (NVARCHAR)
        col_max_length = pack('<H', 0x1F40)  # Max Length (8000 characters)
        # 6-byte collation: LCID(0x0409) + Flags(0x00) + Version(0x01) -> SQL_Latin1_General_CP1_CI_AS
        collation = bytes([0x09, 0x04, 0xD0, 0x00, 0x00, 0x01])
        col_name_len = pack('<B', len(col_name))  # Column Name Length (character count)
        col_name_encoded = col_name.encode('utf-16le')  # Column Name (UTF-16LE)

        colmetadata += user_type + flags + col_type + col_max_length + collation + col_name_len + col_name_encoded

    # Step 3: Create ROW Data (Token Type: 0xD1)
    row_data = b''
    for row in rows:
        row_token = pack('<B', 0xD1)  # ROW Token Type
        row_content = b''
        for col_value in row:
            if col_value is None:
                row_content += pack('<H', 0xFFFF)  # Null value indicator
            elif isinstance(col_value, str):
                col_data = col_value.encode('utf-16le')
                col_len = len(col_data) // 2  # NVARCHAR uses character count for length
                row_content += pack('<H', col_len) + col_data
            else:
                col_str = str(col_value)
                col_data = col_str.encode('utf-16le')
                col_len = len(col_data) // 2
                row_content += pack('<H', col_len) + col_data

        row_data += row_token + row_content

    # Step 4: Create DONE Token (Token Type: 0xFD)
    done_token = pack('<B', 0xFD)  # DONE Token Type
    done_status = pack('<H', 0x0010)  # DONE Status (DONE_FINAL)
    cur_cmd = pack('<H', 0x0001)  # Current Command ID (default)
    done_row_count = pack('<Q', len(rows))  # Row count

    done = done_token + done_status + cur_cmd + done_row_count

    # Combine all parts to form the complete packet
    tds_data = colmetadata + row_data + done

    # Update packet length (total bytes: header(8) + data)
    packet_length = 8 + len(tds_data)
    # Reconstruct header with correct length (ensure construct_tds_header uses <H for length)
    header = construct_tds_header(packet_type, packet_status, packet_length)

    # Combine header and data to form the final TDS packet
    tds_packet = header + tds_data

    return tds_packet

内容的提问来源于stack exchange，提问作者Sylvia Onwukwe

构建SQL Server TDS响应包遇collation空值错误及无查询结果问题

问题概述 #

现有响应包构建代码 #

生成的TDS数据包 #

错误分析与修复方案 #

1. Collation空值错误修复 #

2. 查询结果缺失修复 #

3. 其他潜在问题修复 #

修正后的代码示例 #