我有一个整数元组,我想查询在元组中找到列值的所有行。构造查询很容易,但我希望它是 sql 注入证明。我通常使用预准备语句,但我看不出如何处理这两种需求。
我的查询结构如下所示:
filterList = (1, 2, 4) #Taken as input. Should be integers
sqlRequest = 'SELECT * FROM table'
if filterList != None and len(filterList) > 0:
sqlRequest += ' WHERE column IN ('
addComa = False
for filter in filterList:
if addComa:
sqlRequest += ','
else:
addComa = True
sqlRequest += '%s'%(int(filter)) #casted to int to avoid SQL injection. Still not as good as I would like
sqlRequest += ')'
#At this point sqlRequest == 'SELECT * FROM table WHERE column IN (1,2,4)'
sqlResult = cursor.execute(sqlRequest)
我很想有一个更像的查询:
sqlRequest = 'SELECT * FROM table WHERE column IN (%s, %s, %s)'
并用预准备的语句执行它:
sqlResult = cursor.execute(sqlRequest, filterList[0], filterList[1], filterList[2])
但过滤器列表作为可变长度。有什么办法可以做这样的事情吗?
sqlResult = cursor.execute(sqlRequest, filterList) #where filterList is the whole tuple
您可以使用字符串格式来生成占位符:
statement = "SELECT * FROM table WHERE column IN ({0})".format(
', '.join(['%s'] * len(filterList)))
cursor.execute(statement, filterList)
您可能仍希望测试filterList
是否为空,并在这种情况下完全省略WHERE
子句:
statement = "SELECT * FROM table"
if filterList:
statement += " WHERE column IN ({0})".format(
', '.join(['%s'] * len(filterList)))
cursor.execute(statement, filterList)
您应该能够像这样将其作为完整字符串插入到mysql in子句中。
inList = "','".join(str(i) for i in filterList)
inList = r"'%s'" % inList
sqlRequest = 'SELECT * FROM table WHERE column IN (%s)'
sqlResult = cursor.execute(sqlRequest, inList)