内爆一个在 python MySQLDB IN 子句中使用的列表 [英] imploding a list for use in a python MySQLDB IN clause

问题描述

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

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.

这篇关于内爆一个在 python MySQLDB IN 子句中使用的列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！