Python向SQL Server插入数据时报Invalid column name错误如何排查

问题根源 #

你遇到的报错是直接拼接SQL字符串导致的语法解析异常：当你从irow中提取的strObj、strGrp字段值中包含单引号'时，会直接破坏你拼接出的INSERT语句结构，数据库会把被单引号截断的后续内容误识别为列名。

举个实际的异常场景：如果strObj的取值为User'Id，你拼接出的SQL会变成如下形式：

INSERT INTO dbo.ApiResults ([Object], [Group], json) VALUES ('User'Id','测试分组','Stuff');

数据库解析时会认为User是第一个插入值，后续的Id会被识别为列名匹配项，直接抛出你遇到的“incorrect column name”错误。

修复方案 #

不要手动拼接SQL字符串，改用SQL驱动支持的参数化查询写法即可，SQL Server对应的Python连接库（pyodbc、pymssql等）都支持?作为参数占位符，修改后的代码如下：

for irow in cursor.execute(storedProc):
    strObj = str(irow[0])
    strGrp = str(irow[1])
    print(strObj)
    print(strGrp)
    try:
        response = requests.get(irow[2], timeout=30, auth=('user', 'pw'))
        response.raise_for_status()
        strJson = "Stuff"
        # 替换原有拼接SQL的写法，使用参数化查询
        insert_sql = "INSERT INTO dbo.ApiResults ([Object], [Group], json) VALUES (?, ?, ?)"
        cursor.execute(insert_sql, (strObj, strGrp, strJson))

参数化查询会自动转义处理字段值中的特殊字符，除了解决当前的语法报错问题，还能避免SQL注入安全风险。

额外注意事项 #

你当前使用同一个cursor同时遍历存储过程的查询结果、执行插入操作，部分驱动下可能会出现未遍历完的结果集被插入操作覆盖的问题，如果后续遇到遍历提前终止的情况，可以单独创建一个新的cursor对象专门用于执行插入语句。

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