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 注入风险:

<预><代码>>>>result_set = c.execute('SELECT * FROM distro WHERE id IN (%s)' %','.join('?'*len(desired_ids)),desired_ids)>>>打印 result_set.fetchall()[(1, u'Ubuntu'), (2, u'Fedora'), (5, u'SuSE')]

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屋！