SQLite "IN" 子句的参数替换

Question

我正在尝试在Python中使用SQLite进行参数替换，特别是在IN子句中。下面是一个完整的示例，展示了这个过程：

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

它的输出是：

(1, u'Ubuntu')
(2, u'Fedora')
(5, u'SuSE')

文档中提到“[y]你不应该用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 ] )

我得到了：

InterfaceError: 绑定参数0时出错 - 可能是类型不支持。

虽然我希望任何对这个简化问题的回答都能奏效，但我想指出我实际想执行的查询是一个双重嵌套的子查询。具体来说：

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
        ) 
    )

Answer 1

更新：这个方法有效：

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)' % ("?," * len(desired_ids))[:-1], desired_ids)
for result in result_set:
  print result

问题在于，你需要为输入列表中的每个元素准备一个问号（?）。

这段代码 ("?," * len(desired_ids))[:-1] 的意思是先生成一个重复的字符串 "?,", 然后把最后一个逗号去掉。这样就能确保每个 desired_ids 中的元素都有一个对应的问号。

Answer 2

根据http://www.sqlite.org/limits.html（第9项），SQLite默认情况下不能处理超过999个参数的查询。所以这里的解决方案（生成所需的占位符列表）如果你有成千上万的项目要查找IN，就会失败。如果是这种情况，你需要把列表拆分开，然后逐个处理这些部分，最后把结果合并起来。

如果你在IN条件中不需要成千上万的项目，那么Alex的解决方案就是正确的做法（看起来Django也是这么做的）。

Answer 3

你确实需要正确数量的?，但这并不会造成SQL注入的风险：

>>> 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')]