pymysql.err.InternalError: (1054, "Unknown column '27D24A3B' in 'where clause'")之错误解决-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_38383877/article/details/87933873

本文解决了一个因使用不当的字符串拼接导致的SQL注入错误，通过调整字符串格式化方式避免了错误的发生，确保了数据库查询的安全性和正确性。

原代码：

query_image_path1='SELECT imageTempFilename_0,imageTempFilename_1 FROM %s'% tablename
query_image_path2=' where IDprojectTemp=%s and IDproject=%s and cameraID=%s and imageflag=%s'%(IDprojectTemp,IDproject,cameraID,imageflag)
query_image_path_sql=query_image_path1+query_image_path2
print(query_image_path_sql)
cursor.execute(query_image_path_sql)
value_list = cursor.fetchall()


######################################
#query_image_path_sql:SELECT imageTempFilename_0,imageTempFilename_1 FROM engineeringdata20190124 where IDprojectTemp=27D24A3B-ECF3-40ff-BC79-9A0B8417A0CD and IDproject=C3A9B395-12A5-4821-AD00-B8C02E5C3904 and cameraID=1 and imageflag=0

运行出现错误：pymysql.err.InternalError: (1054, "Unknown column '27D24A3B' in 'where clause'")

将query_image_path2中的单引号改为双引号。%s上加双引号，则不报错。改正后如下：

query_image_path1='SELECT imageTempFilename_0,imageTempFilename_1 FROM %s'% tablename
query_image_path2=" where IDprojectTemp='%s' and IDproject='%s' and cameraID='%s' and imageflag='%s'"%(IDprojectTemp,IDproject,cameraID,imageflag)
query_image_path_sql=query_image_path1+query_image_path2
print(query_image_path_sql)
cursor.execute(query_image_path_sql)
value_list = cursor.fetchall()
print('value_list:', value_list)