MySQLX Python执行查询时SELECT被替换为AS？求排查原因

问题详情 #

使用mysql-connector-python的MySQLX API执行查询时触发SQL语法错误，错误显示语句中的SELECT被替换为AS。调用查询对象的.get_sql()方法时生成的语句正常，但调用.execute()时语句被篡改。

代码示例 #

报错的查询代码 #

def queryOrg(self, OrgID):
        session = mysqlx.get_session(
            {'host': db.HOST, 'port': db.PORT, 'user': db.USER, 'password': db.PASSWORD})
        org_schema = session.get_schema('Organizations')
        org_table = org_schema.get_table('Organizations')
        result = org_table.select(["*"]).where('Organization_ID = ' + str(OrgID)).execute()
        print(result)

查看生成SQL的代码 #

def queryOrg(self, OrgID):
        session = mysqlx.get_session(
            {'host': db.HOST, 'port': db.PORT, 'user': db.USER, 'password': db.PASSWORD})
        org_schema = session.get_schema('Organizations')
        org_table = org_schema.get_table('Organizations')
        result = org_table.select(["*"]).where('Organization_ID = ' + str(OrgID)).get_sql()
        print(result)

错误信息 #

mysqlx.errors.OperationalError: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS `*` FROM `Organizations`.`Organizations` WHERE (`Organization_ID` = 2)' at line 1

排查发现 #

• .get_sql()输出的SQL语句正常：SELECT * FROM Organizations.Organizations WHERE Organization_ID = 2
• .execute()执行时生成错误语句：AS *FROMOrganizations.Organizations WHERE (Organization_ID = 2)

解决方案 #

1. 升级mysql-connector-python版本 #

该问题是mysql-connector-python 8.0.30的已知bug，在8.0.31及以上版本中已修复。执行以下命令升级：

pip install --upgrade mysql-connector-python

2. 临时替代方案（无需升级） #

将select(["*"])中的列表参数改为字符串参数select("*")，修改后的代码：

def queryOrg(self, OrgID):
        session = mysqlx.get_session(
            {'host': db.HOST, 'port': db.PORT, 'user': db.USER, 'password': db.PASSWORD})
        org_schema = session.get_schema('Organizations')
        org_table = org_schema.get_table('Organizations')
        # 替换列表["*"]为字符串"*"
        result = org_table.select("*").where('Organization_ID = ?', OrgID).execute()
        print(result)

额外建议 #

避免直接拼接SQL参数（'Organization_ID = ' + str(OrgID)），存在SQL注入风险，改用参数绑定.where('Organization_ID = ?', OrgID)，如上述代码所示。

环境信息 #

• 系统：Ubuntu树莓派
• MySQL版本：8.0.31-0ubuntu0.22.04.1
• Python版本：3.8.10
• mysql-connector-python版本：8.0.30
• Flask版本：2.2.2

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