SQLite“IN"的参数替换条款 [英] Parameter substitution for a SQLite "IN" clause
问题描述
我正在尝试将参数替换与 Python 中的 SQLite 一起用于 IN 子句.这是一个完整的运行示例,用于演示:
导入 sqlite3c = sqlite3.connect(":memory:")c.execute('CREATE TABLE distro (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)')'Ubuntu Fedora Puppy DSL SuSE'.split() 中的名称:c.execute('INSERT INTO distro (name) VALUES (?)', [ name ] )required_ids = ["1", "2", "5", "47"]result_set = c.execute('SELECT * FROM distro WHERE id IN (%s)' % (", ".join(desired_ids)), ())对于 result_set 中的结果:打印结果
打印出来:
<块引用>(1,你是Ubuntu)(2, 你'Fedora')(5, u'SuSE')
由于文档指出[你]你不应该使用 Python 的字符串操作来组装你的查询,因为这样做是不安全的;它会使你的程序容易受到 SQL 注入攻击,"我希望使用参数替换.
当我尝试时:
result_set = c.execute('SELECT * FROM distro WHERE id IN (?)', [ (", ".join(desired_ids)) ])
我得到一个空的结果集,当我尝试时:
result_set = c.execute('SELECT * FROM distro WHERE id IN (?)', [ desired_ids ] )
我明白了:
<块引用>接口错误:错误绑定参数 0 - 可能是不受支持的类型.
虽然我希望这个简化问题的任何答案都能奏效,但我想指出我想要执行的实际查询是在双重嵌套子查询中.即:
UPDATE dir_x_user SET user_revision = user_attempted_revisionWHERE user_id IN(选择 user_id FROM(SELECT user_id, MAX(revision) FROM users WHERE obfuscated_name IN("Argl883", "Manf496", "Mook657") GROUP BY user_id))
您确实需要正确数量的 ?
,但这不会造成 sql 注入风险:
I am trying to use parameter substitution with SQLite within Python for an IN clause. Here is a complete running example that demonstrates:
import sqlite3
c = sqlite3.connect(":memory:")
c.execute('CREATE TABLE distro (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)')
for name in 'Ubuntu Fedora Puppy DSL SuSE'.split():
c.execute('INSERT INTO distro (name) VALUES (?)', [ name ] )
desired_ids = ["1", "2", "5", "47"]
result_set = c.execute('SELECT * FROM distro WHERE id IN (%s)' % (", ".join(desired_ids)), ())
for result in result_set:
print result
It prints out:
(1, u'Ubuntu') (2, u'Fedora') (5, u'SuSE')
As the docs state that "[y]ou shouldn’t assemble your query using Python’s string operations because doing so is insecure; it makes your program vulnerable to an SQL injection attack," I am hoping to use parameter substitution.
When I try:
result_set = c.execute('SELECT * FROM distro WHERE id IN (?)', [ (", ".join(desired_ids)) ])
I get an empty result set, and when I try:
result_set = c.execute('SELECT * FROM distro WHERE id IN (?)', [ desired_ids ] )
I get:
InterfaceError: Error binding parameter 0 - probably unsupported type.
While I hope that any answer to this simplified problem will work, I would like to point out that the actual query I want to perform is in a doubly-nested subquery. To wit:
UPDATE dir_x_user SET user_revision = user_attempted_revision
WHERE user_id IN
(SELECT user_id FROM
(SELECT user_id, MAX(revision) FROM users WHERE obfuscated_name IN
("Argl883", "Manf496", "Mook657") GROUP BY user_id
)
)
You do need the right number of ?
s, but that doesn't pose a sql injection risk:
>>> result_set = c.execute('SELECT * FROM distro WHERE id IN (%s)' %
','.join('?'*len(desired_ids)), desired_ids)
>>> print result_set.fetchall()
[(1, u'Ubuntu'), (2, u'Fedora'), (5, u'SuSE')]
这篇关于SQLite“IN"的参数替换条款的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!