Подстановка параметров для предложения SQLite "IN"

Я пытаюсь использовать подстановку параметров с SQLite внутри Python для предложения 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] ou не должен собирать ваш запрос с помощью строковых операций Pythons, потому что это небезопасно, что делает вашу программу уязвимой для атаки 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
        ) 
    )

Ответы

Ответ 1

Вам нужно правильное число ? s, но это не создает риск инъекции 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')]

Ответ 2

Согласно http://www.sqlite.org/limits.html (элемент 9), SQLite не может (по умолчанию) обрабатывать более 999 параметров для запроса, поэтому решения здесь (создание нужного списка заполнителей) потерпят неудачу, если у вас есть тысячи предметов, которые вы ищете IN. Если это произойдет, вам нужно будет разбить список, а затем перебрать его части и объединить результаты самостоятельно.

Если вам не нужны тысячи элементов в предложении IN, тогда решение Alex - это способ сделать это (и, похоже, это делает Django).

Ответ 3

Обновление: это работает:

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] делается повторяющаяся строка "?", а затем отсекает последнюю запятую. так что есть один знак вопроса для каждого элемента в желаемых_средствах.

Ответ 4

Я всегда делаю что-то вроде этого:

query = 'SELECT * FROM distro WHERE id IN (%s)' % ','.join('?' for i in desired_ids)
c.execute(query, desired_ids)

Там нет риска инъекций, потому что вы не помещаете строки запроса желаемого в запрос напрямую.

Ответ 5

В случае, если sqlite имеет проблему с длиной запроса sql, неопределенное количество вопросительных знаков может быть своего рода способом бить вещи.