内爆列表以用于 Python MySQL IN 子句

问题描述：

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

foostring = ",".join( map(str, list_of_ids) )

并且我知道我可以使用以下命令将该字符串放入 IN 子句中：

cursor.execute("DELETE FROM foo.bar WHERE baz IN ('%s')" % (foostring))

如何使用 MySQL 数据库安全地完成同一件事（避免SQL 注入）？

在上面的例子中，由于 foostring 没有作为参数传递给 execute，因此存在漏洞。我还必须在 MySQL 库之外引用和转义。

（有一个相关的 Stack Overflow 问题，但是那里列出的答案要么不适用于 MySQL 数据库，要么容易受到 SQL 注入。）

解决方案 1：

直接使用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 注入。

请注意，数据 ( list_of_ids) 将直接作为参数（而不是查询文本）发送到 MySQL 的驱动程序，因此不存在任何注入。您可以在字符串中保留任何您想要的字符；无需删除或引用字符。

解决方案 2：

当我们有很多参数或者我们想要使用命名参数时，接受的答案就会变得混乱。

经过一些尝试，

ids = [5, 3, ...]  # List of ids
cursor.execute('''
SELECT
...
WHERE
  id IN %(ids)s
  AND created_at > %(start_dt)s
''', {
  'ids': tuple(ids), 'start_dt': '2019-10-31 00:00:00'
})

它与 Python 2.7 和 pymysql 0.7.11 兼容。

解决方案 3：

正如 Rubms 对 markk 的回答的评论中所指出的那样，这似乎仍然是 2021 年 Python3 的一个问题。

在 mysql 连接器包中的“cursor.py”中的方法“_process_params_dict”中添加大约 9 行代码来处理元组，解决了我的问题：

def _process_params_dict(self, params):
    """Process query parameters given as dictionary"""
    try:
        to_mysql = self._connection.converter.to_mysql
        escape = self._connection.converter.escape
        quote = self._connection.converter.quote
        res = {}
        for key, value in list(params.items()):
            if type(value) is tuple: ### BEGIN MY ADDITIONS
                res[key.encode()] = b''
                for subvalue in value:
                    conv = subvalue
                    conv = to_mysql(conv)
                    conv = escape(conv)
                    conv = quote(conv)
                    res[key.encode()] = res[key.encode()] + b',' + conv if len(res[key.encode()]) else conv
            else: ### END MY ADDITIONS
                conv = value
                conv = to_mysql(conv)
                conv = escape(conv)
                conv = quote(conv)
                res[key.encode()] = conv
    except Exception as err:
        raise errors.ProgrammingError(
            "Failed processing pyformat-parameters; %s" % err)
    else:
        return res

解决方案 4：

也许这个问题有点晚了，但是我偶然发现了一个类似的问题，但我想使用命名参数的字典而不是元组（因为如果我想修改参数以添加或删除一些参数，我不想重新构造元组，弄乱顺序可能非常容易并且容易引起错误......）。

我的解决方案是格式化查询字符串以将参数分解为几个参数，然后使用这些新参数构造参数字典：

from typing import Iterable

query = """
SELECT *
FROM table
WHERE id IN (%(test_param)s)
"""

parameters = {"test_param": [1, 2, 3])

new_params = {}

for k, v in parameters.items():
    if isinstance(v, Iterable):
        iterable_params = {f"{k}_{i}": value for i, value in enumerate(v)}
        iterable_params_formatted = [f"%({k}_{i})s" for i in range(0, len(v))]
        query = query.replace(f"%({k})s", ", ".join(iterable_params_formatted))
        new_params.update(iterable_params)
    else:
        new_params[k] = v

print(query)
print(new_params)

结果：

> SELECT *
FROM table
WHERE id IN (%(test_param_0)s, %(test_param_1)s, %(test_param_2)s)

> {'test_param_0': 1, 'test_param_1': 2, 'test_param_2': 3}

可以做得更好，但我找不到使用命名参数字典而不是有序元组的解决方案。

解决方案 5：

我知道很多人提供了这个答案的版本，但这对我来说是有用的。本质上是在列表末尾添加两个空值。

v=[1,2,3]
query= f""" SELECT * FROM table IN {tuple(list(v)+['',''])};"""

解决方案 6：

还有另一种方法。

myList = [
  "this","andthis","butalsothis","anddontforgetaboutthis"
]

myListStr = '"' + '","'.join(myList) + '"'


query = (
            ' SELECT'
            '  col1,'
            '  col2,'
            '  col3
            ' FROM'
            '  myTable'
            ' WHERE'
            '   col3 IN ('+ myListStr +')'
          )

解决方案 7：

如果您使用 Django 2.0 或 2.1 和 Python 3.6，则这是正确的方法：

from django.db import connection
RESULT_COLS = ['col1', 'col2', 'col3']
RESULT_COLS_STR = ', '.join(['a.'+'`'+i+'`' for i in RESULT_COLS])
QUERY_INDEX = RESULT_COLS[0]

TABLE_NAME = 'test'
search_value = ['ab', 'cd', 'ef']  # <-- a list
query = (
    f'SELECT DISTINCT {RESULT_COLS_STR} FROM {TABLE_NAME} a '
    f'WHERE a.`{RESULT_COLS[0]}` IN %s '
    f'ORDER BY a.`{RESULT_COLS[0]}`;'
)  # <- 'SELECT DISTINCT a.`col1`, a.`col2`, a.`col3` FROM test a WHERE a.`col1` IN %s ORDER BY a.`col1`;'
with connection.cursor() as cursor:
    cursor.execute(query, params=[search_value])  # parameters is a list with a list as its element

参考

• 如何在 Django 中传递 SQL 子句中的值列表

• 将参数传递给 raw()

解决方案 8：

虽然这个问题已经很老了。如果它能帮助到别人，我会分享我的解决方案。

`list_to_check = ['A', 'B']
cursor.execute("DELETE FROM foo.bar WHERE baz IN ({})".format(str(list_to_check)[1:-1])`

经过测试Python=3.6

解决方案 9：

使用：

list_of_ids = [ 1, 2, 3]
query = "select * from table where x in %s" % str(tuple(list_of_ids))
print query

如果您不想担心必须传递参数来完成查询字符串的方法而只想调用cursror.execute(query)，那么这可能适用于某些用例。

另一种方法可能是：

"select * from table where x in (%s)" % ', '.join(str(id) for id in list_of_ids)

解决方案 10：

使用列表理解的另一个简单的解决方案：

# Creating a new list of strings and convert to tuple
sql_list = tuple([ key.encode("UTF-8") for key in list_of_ids ])

# Replace "{}" with "('id1','id2',...'idlast')"
cursor.execute("DELETE FROM foo.bar WHERE baz IN {}".format(sql_list))

解决方案 11：

非常简单：只需使用下面的格式

rules_id = ["9","10"]

sql1 = "SELECT * FROM attendance_rules_staff WHERE id in(" + ", ".join(map(str, rules_id)) + ")"

笔记：

", ".join(map(str, rules_id))