内爆列表以在 python MySQLDB IN 子句中使用

2022-01-30 00:00:00 python mysql

我知道如何将列表映射到字符串:

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 没有作为参数传递给执行,所以它很容易受到攻击.我还必须在 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.

相关文章