MySQL和PYTHON。如何在tup中查询WHERE'column'

Question

我有一个整数元组，我想查询在这个元组中找到的列值的所有行。构造查询很容易，但我希望它是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)

我想问一个更像：

^{pr2}$

并用准备好的语句执行它：

sqlResult = cursor.execute(sqlRequest, filterList[0], filterList[1], filterList[2])

但是filterList是可变长度的。有没有办法做类似的事情？在

sqlResult = cursor.execute(sqlRequest, filterList) #where filterList is the whole tuple

Answer 1

您应该能够将它作为一个完整的字符串插入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)

Answer 2

您可以使用字符串格式来生成占位符：

statement = "SELECT * FROM table WHERE column IN ({0})".format(
    ', '.join(['%s'] * len(filterList)))
cursor.execute(statement, filterList)

您可能仍然想测试filterList不是空的，并且在这种情况下完全省略WHERE子句：

^{pr2}$