Python MySQL参数化查询无法编码代理字符问题求助

环境信息 #

• Python版本：3.7、3.8
• mysql-connector-python版本：8.0.13（Python3.7/3.8均测试）、8.1.0（仅Python3.8测试）
• MySQL版本：5.7.42
• 数据库排序规则：utf8mb4_unicode_520_ci

数据库连接代码 #

db = None
db = mysql.connector.connect(
    host="localhost",
    user=username,
    passwd=password,
    database=eventdb,
    charset="utf8mb4",
    use_unicode=True
)

cur = None
cur = db.cursor(dictionary=True)

问题复现 #

存在一个来自json.dump的字符串，尝试执行参数化查询：

data["name"] = '\udced\udca0\udcbe\udced\udcb7\udca1\n\n\udced\udca0\udcbe\udced\udcb7\udca1\n\n♡ADANA♡♡EOMON♡'

sql = "SELECT db_name_id FROM db_name WHERE name = %s"
val = (data["name"],)
cur.execute(sql, val)

错误表现 #

• 在Python3.7/3.8搭配mysql-connector-python 8.0.13时，报错：

  UnicodeEncodeError: 'utf-8' codec can't encode characters in position 0-5: surrogates not allowed
  

• 在Python3.8搭配mysql-connector-python 8.1.0时，报错：

  _mysql_connector.MySQLInterfaceError: Failed converting Python 'str'
  

非参数化查询无异常 #

直接将字符串硬编码到SQL语句中执行，无报错：

cur.execute("SELECT db_name_id FROM db_name WHERE name = '\udced\udca0\udcbe\udced\udcb7\udca1\n\n\udced\udca0\udcbe\udced\udcb7\udca1\n\n♡ADANA♡♡EOMON♡'")

直接调用C扩展复现错误 #

仅在mysql-connector-python 8.1.0环境下测试，代码如下：

import _mysql_connector

ccnx = _mysql_connector.MySQL()
ccnx.connect(
                host="localhost",
                user="user",
                password="password",
                database="database"
            )

bad_str = 'just_an_��_example'

try:
    str_converted = ccnx.convert_to_mysql(*[bad_str])
    print('str converted is %s', str_converted)
except Exception as e:
    print('cant convert bad str %s',bad_str)
    print(e)

临时测试修复 #

参考MySQL相关Bug信息，修改连接字符集后convert_to_mysql可正常工作：

import _mysql_connector

ccnx = _mysql_connector.MySQL()
ccnx.connect(
                host="localhost",
                user="user",
                password="password",
                database="database"
            )
ccnx.set_character_set('utf8')
bad_str = 'just_an_��_example'

try:
    str_converted = ccnx.convert_to_mysql(*[bad_str])
    print('str converted is %s', str_converted)
except Exception as e:
    print('cant convert bad str %s',bad_str)
    print(e)

需求 #

由于是用户输入字段，无法使用非参数化查询，希望找到正确的配置项或解决方法，处理含代理字符的参数化字符串转换问题。

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