Rocky Linux 8下FreeTDS+pyodbc含变音字符SQL查询报错问题

环境信息 #

环境1（CentOS 6） #

• 操作系统：CentOS 6
• FreeTDS版本：freetds-0.91-2.el6.x86_64
• Python库：pyodbc 4.0.34

环境2（Rocky Linux 8） #

• 操作系统：Rocky Linux 8
• FreeTDS版本：freetds-1.3.3-1.el8.x86_64、freetds-libs-1.3.3-1.el8.x86_64
• Python库：pyodbc 4.0.34

问题现象 #

• 同一含变音字符的SQL查询脚本，在CentOS 6连接数据库可正常执行；
• 在Rocky Linux 8执行时抛出错误：

('42000', "[42000] [FreeTDS][SQL Server]Unclosed quote before the character string ','.
 (105) (SQLExecDirectW)")

• 移除变音字符后，SQL查询可在Rocky Linux 8正常执行。

相关配置文件内容 #

Rocky Linux 8的/etc/freetds.conf #

#
# This file is installed by FreeTDS if no file by the same
# name is found in the installation directory.
#
# For information about the layout of this file and its settings,
# see the freetds.conf manpage "man freetds.conf".

# Global settings are overridden by those in a database
# server specific section
[global]
        # TDS protocol version
        tds version = auto

        # Whether to write a TDSDUMP file for diagnostic purposes
        # (setting this to /tmp is insecure on a multi-user system)
;       dump file = /tmp/freetds.log
;       debug flags = 0xffff

        # Command and connection timeouts
;       timeout = 10
;       connect timeout = 10

        # To reduce data sent from server for BLOBs (like TEXT or
        # IMAGE) try setting 'text size' to a reasonable limit
;       text size = 64512

        # If you experience TLS handshake errors and are using openssl,
        # try adjusting the cipher list (don't surround in double or single quotes)
        # openssl ciphers = HIGH:!SSLv2:!aNULL:-DH

# A typical Sybase server
[egServer50]
        host = symachine.domain.com
        port = 5000
        tds version = 5.0

# A typical Microsoft server
[egServer73]
        host = ntmachine.domain.com
        port = 1433
        tds version = 7.3

CentOS 6的/etc/freetds.conf #

#   $Id: freetds.conf,v 1.12 2007/12/25 06:02:36 jklowden Exp $
#
# This file is installed by FreeTDS if no file by the same
# name is found in the installation directory.
#
# For information about the layout of this file and its settings,
# see the freetds.conf manpage "man freetds.conf".

# Global settings are overridden by those in a database
# server specific section
[global]
        # TDS protocol version
;       tds version = 4.2

        # Whether to write a TDSDUMP file for diagnostic purposes
        # (setting this to /tmp is insecure on a multi-user system)
;       dump file = /tmp/freetds.log
;       debug flags = 0xffff

        # Command and connection timeouts
;       timeout = 10
;       connect timeout = 10

        # If you get out-of-memory errors, it may mean that your client
        # is trying to allocate a huge buffer for a TEXT field.
        # Try setting 'text size' to a more reasonable limit
        text size = 64512

# A typical Sybase server
[egServer50]
        host = symachine.domain.com
        port = 5000
        tds version = 5.0

# A typical Microsoft server
[egServer70]
        host = ntmachine.domain.com
        port = 1433
        tds version = 7.0

/etc/odbcinst.ini相关配置 #

[TDS-ASE]
Description=Sybase ODBC TDS Driver
Driver=/usr/lib64/libtdsodbc.so.0
Setup=/usr/lib64/libtdsS.so.2
TDS_Version=5
UsageCount=3
CPTimeout=
CPReuse=

问题分析与解决方案 #

核心差异分析 #

1. TDS版本协商逻辑变化：
   • CentOS 6的FreeTDS 0.91默认未指定全局TDS版本，实际连接时会与odbcinst.ini中TDS_Version=5协商使用TDS 5.0，这是Sybase ASE的标准协议版本。
   • Rocky Linux 8的FreeTDS 1.3.3使用tds version = auto，新版本的auto逻辑可能优先选择更高版本的TDS协议（如7.x），而Sybase ASE对高版本TDS的字符编码处理与SQL Server存在差异，导致变音字符转义错误，破坏SQL语句结构。
2. 字符编码处理严格性提升：
   FreeTDS从0.91到1.3.3对非ASCII字符的编码转换逻辑更严格，若客户端与数据库的字符集不匹配，会导致变音字符被错误解析，进而触发SQL语法错误（如未闭合引号）。

解决方案 #

1. 强制指定TDS版本为5.0：
   修改Rocky Linux 8的/etc/freetds.conf中[global]段的配置：

   tds version = 5.0
   

   确保与odbcinst.ini中的TDS_Version=5保持一致，匹配Sybase ASE的协议要求。

2. 明确指定客户端字符集：
   在freetds.conf的[global]段添加字符集配置（根据数据库实际使用的字符集调整，示例为UTF-8）：

   client charset = UTF-8
   

   确保客户端与数据库的字符编码一致，避免变音字符转换错误。

3. 使用参数化查询替代SQL拼接：
   在pyodbc中采用参数化查询方式，彻底避免字符转义问题：

   # 错误方式：直接拼接含变音字符的字符串
   # cursor.execute("SELECT * FROM users WHERE name = 'Müller'")
   # 正确方式：参数化查询
   cursor.execute("SELECT * FROM users WHERE name = ?", ('Müller',))
   

内容的提问来源于stack exchange，提问作者Maciej Szymonowicz