python - 在 MySQL + Python 中使用 AND 运算符参数化子字符串查询的安全方法

Question

我正在尝试从给定的字符串安全地构建 SQL 查询。该字符串可以包含空格。

string = "chair"

query = """SELECT *
             FROM albums
             WHERE title LIKE %s"""
values = ('%' + string + '%',)
cur.execute(query,values)
# >> SELECT * FROM albums WHERE title LIKE '%chair%'

但我不知道如何安全地扩展它以使用 AND 运算符搜索多个单词。

string = "big blue chair"
# >> SELECT * FROM albums WHERE title LIKE '%big%' AND
#                               title LIKE '%blue%' AND
#                               title LIKE '%chair%'

(我知道这就是 FULLTEXT 搜索的目的，等等......但这是针对非常小的数据集并且速度足够快。)

Answer 1

如果你想根据string动态扩展query和values，你可以这样做:

string = "big blue chair"
query = "SELECT * FROM albums WHERE title LIKE %s"
values = ["%{}%".format(val) for val in string.split()]
for _ in values[1:]:  # iterate over len(values) - 1
    query += " AND title LIKE %s"
print(query)
print(values)

输出:

SELECT * FROM albums WHERE title LIKE %s AND title LIKE %s AND title LIKE %s
['%big%', '%blue%', '%chair%']