内爆一个在 python MySQLDB IN 子句中使用的列表
我知道如何将列表映射到字符串:
I know how to map a list to a string:
foostring = ",".join( map(str, list_of_ids) )
而且我知道我可以使用以下内容将该字符串放入 IN 子句中:
And I know that I can use the following to get that string into an IN clause:
cursor.execute("DELETE FROM foo.bar WHERE baz IN ('%s')" % (foostring))
我需要的是使用 MySQLDB 安全地完成同样的事情(避免 SQL 注入).在上面的例子中，因为 foostring 没有作为参数传递给 execute，所以它很容易受到攻击.我还必须在 mysql 库之外引用和转义.
What I need is to accomplish the same thing SAFELY (avoiding SQL injection) using MySQLDB. In the above example because foostring is not passed as an argument to execute, it is vulnerable. I also have to quote and escape outside of the mysql library.
(有一个相关SO问题，但是那里列出的答案要么不适用于 MySQLDB，要么容易受到 SQL 注入的影响.)
(There is a related SO question, but the answers listed there either do not work for MySQLDB or are vulnerable to SQL injection.)
推荐答案
直接使用list_of_ids:
format_strings = ','.join(['%s'] * len(list_of_ids))
cursor.execute("DELETE FROM foo.bar WHERE baz IN (%s)" % format_strings,
tuple(list_of_ids))
这样你就不用自己引用了，也避免了各种sql注入.
That way you avoid having to quote yourself, and avoid all kinds of sql injection.
请注意，数据 (list_of_ids) 作为参数(不在查询文本中)直接进入 mysql 的驱动程序，因此没有注入.您可以在字符串中保留您想要的任何字符，无需删除或引用字符.
Note that the data (list_of_ids) is going directly to mysql's driver, as a parameter (not in the query text) so there is no injection. You can leave any chars you want in the string, no need to remove or quote chars.
相关文章