Подстановка параметра для предложения 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 не следует собирать свой запрос с использованием строковых операций 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
        ) 
    )

python sqlite
person Clinton Blackmore    schedule 21.08.2009    source источник
comment
Спасибо за ответы на все вопросы. Это имело большой смысл, когда я наконец увидел, что мне просто нужен вопросительный знак для каждого параметра, который я заменяю.   -  person Clinton Blackmore    schedule 21.08.2009

Ответы (6)


arrow_upward
82
arrow_downward

Вам действительно нужно правильное количество ?, но это не создает риска внедрения 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')]
person Alex Martelli    schedule 21.08.2009
comment
+1 за лучшее решение для создания строки списка заполнителей :-) - person Ferdinand Beyer; 21.08.2009
comment
Есть ли простой способ использовать вместо этого именованные параметры? Что-то вроде :id1 :id2 :id3 и т. Д. Я использую это в контексте более крупного запроса с несколькими другими именованными параметрами. - person User; 11.12.2012
comment
Я подхожу к этому несколько лет спустя, но мне тоже нужны были именованные параметры. Я только что сделал это: query = "SELECT * FROM my_table WHERE my_param = :my_param AND id IN ({})".format(', '.join(':{}'.format(i) for i in range(len(desired_ids)))) ; params = {'my_param': 'foo'} ; params.update({str(i): id for i, id in enumerate(desired_ids)}) ; result = cursor.execute(query, params) Модуль sqlite3 вполне доволен такими вещами, как :0, :1, :2 в качестве параметров подстановки строк. (Переполнение стека действительно убивает форматирование кода в комментариях; извините, это так трудно читать.) - person geekofalltrades; 04.06.2015
comment
Спасибо за этот ответ, geekofalltrades. Я действительно хотел бы, чтобы это был разборчивый ответ верхнего уровня, который подошел бы к остальным, которые не могут обрабатывать именованные параметры. - person rspeer; 20.04.2019

arrow_upward
27
arrow_downward

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

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

person cibyr    schedule 17.08.2010
comment
Судя по той же ссылке, этот предел увеличился до 32766 для версий SQLite после 3.32.0. - person yodavid; 11.09.2020

arrow_upward
13
arrow_downward

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

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

person Mark Rushakoff    schedule 21.08.2009
comment
Это было отличное объяснение. Спасибо. - person Clinton Blackmore; 21.08.2009

arrow_upward
5
arrow_downward

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

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

Нет риска внедрения, потому что вы не помещаете строки из желаемых_идей в запрос напрямую.

person John Fouhy    schedule 21.08.2009
comment
Значения, которые я буду использовать в предложении IN, фактически взяты из файла, экспортированного из другой системы. Я полагаю, что риск укола ничтожен, но никогда не знаешь, когда появится Bobby Tables. - person Clinton Blackmore; 21.08.2009
comment
Риск injecton равен 0, потому что единственное, что вы программно вставляете в свой запрос, - это набор вопросительных знаков. Все, что может сделать гипотетический злоумышленник, - это контролировать количество вопросительных знаков - это не вектор атаки. Фактические данные, предоставленные извне, проходят через? механизм передачи параметров как обычно. - person John Fouhy; 21.08.2009

arrow_upward
0
arrow_downward

Можно использовать очень тонкий слой, например, нотанорм

https://pypi.org/project/notanorm/

... и тогда ваш код выглядит так:

import notanorm

c = notanorm.SqliteDb(":memory:")
c.query('CREATE TABLE distro (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)')

for name in 'Ubuntu Fedora Puppy DSL SuSE'.split():
  c.insert('distro', name=name)

desired_ids = ["1", "2", "5", "47"]
result_set = c.select('distro', id=desired_ids)

for result in result_set:
  print(result)

Это легко читать, а также позволяет когда-нибудь переключать базы данных.

{'id': 1, 'name': 'Ubuntu'}
{'id': 2, 'name': 'Fedora'}
{'id': 5, 'name': 'SuSE'}

HN: https://news.ycombinator.com/item?id=26733028

person Erik Aronesty    schedule 07.04.2021

arrow_upward
-1
arrow_downward

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

person n800s    schedule 28.08.2009