python - MySQL 和 python 。如何查询 WHERE 'column' IN 元组

Question

我有一个整数元组，我想查询在元组中找到列值的所有行。构造查询很容易，但我希望它是 sql 注入(inject)证明。我通常使用准备好的语句，但我不知道如何处理这两种需求。

我的查询构造看起来像这样:

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])

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

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

Answer 1

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

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)