在python中使用sqlite3注入安全的参数化查询 [英] Injection safe parameterized queries with sqlite3 in python
问题描述
我一直在阅读 sqlite 的文档,发现许多来源强烈建议避免在查询中使用 python 字符串替换,因为这会使它们容易受到注入攻击:
I've been reading documentation for sqlite and found that many sources strongly recommend avoiding python string substitution in queries since it makes them vulnerable to injection attacks:
避免:
conn.execute("SELECT * FROM %s" % table_name)
conn.execute("SELECT * FROM {}".format(table_name))
我见过几个简单的例子,像这样的字符串格式被替换为 slqlite 参数:
I've seen several simple examples where string formatting like this is replaced with slqlite parameters:
values = ('my_table', '1')
conn.execute("SELECT * FROM ? WHERE ROWID = ?", values)
<小时>
这适用于简单的情况,但当我想从列表中检索参数值时失败,如下例所示:
This works in the simple case, but fails when I want to retrieve the parameter values from a list as in this example:
具有字符串格式的工作代码:
Working code with string formatting:
c.execute("BEGIN TRANSACTION")
for i in range(len(amt_l)):
c.execute("""
INSERT INTO transactions ({}, {}, {}, {}, {})
VALUES ('{}', '{}', '{}', '{}', '{}')
""".format(header[0], header[1], header[2], header[3], header[4],
date_l[i], party_l[i], direction_l[i], ctr_party_l[i], amt_l[i]))
db.commit()
使用参数的非工作代码:
Non-working code using parameters:
c.execute("BEGIN TRANSACTION")
for i in range(len(amt_l)):
values = (header[0], header[1], header[2], header[3], header[4],
date_l[i], party_l[i], direction_l[i], ctr_party_l[i], amt_l[i])
c.execute("""
INSERT INTO transactions (?, ?, ?, ?, ?)
VALUES (?, ?, ?, ?, ?)
""", values)
当从列表中检索参数值时,有没有办法使用?
在sql查询中填充参数?
Is there a way to use ?
to fill in parameters in the sql query when the parameter values are retrieved from a list?
推荐答案
您可以使用绑定变量来参数化值.您不能参数化列名(或表名,或其他 SQL 对象的名称).
You can use bind variables to parameterize values. You can't parameterize column names (or table names, or the names of other SQL objects).
为这些名称使用不受信任的数据将天生不安全 -- 而且,使这成为可能会阻止预先分析(可以使用哪些索引或以其他方式有效执行查询),这是准备好的语句的与安全无关的好处.
Using untrusted data for these names would be innately insecure -- and moreover, making this possible would prevent up-front analysis (of which indexes can be used or otherwise how to efficiently execute a query), which is a non-security-related benefit of prepared statements.
这篇关于在python中使用sqlite3注入安全的参数化查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!