Как правильно подставлять значения из переменных / списков / словарей в SQL запрос?
1,00
р.
Все чаще стали появляться вопросы о том, как правильно подставить в SQL запрос значения из переменных и об ошибках, связанных с неправильной подстановкой значений.
Вот несколько примеров таких вопросов:
Почему при вставке в таблицу передаваемые значения воспринимаются как названия столбцов? Как устранить ошибку "sqlite3.OperationalError: no such column: ..."? Как экранировать SQL запрос, чтобы избежать SQL injections? Как передавать параметр типа string в SQL запрос? Как правильно заменить местозаполнители в запросе? SQL запрос с несколькими условиями
Поэтому я решил создать новый обобщённый вопрос и более-менее канонический ответ для того, чтобы ссылаться на него в будущем. Данный ответ планируется расширять новыми подробностями и примерами.
Ответ
Лучше всего использовать SQL запросы с использованием параметров:
cursor.execute("SELECT * FROM my_table WHERE id = ?", [123]) # parameter placeholder ------------------------> ^ # список/кортеж со значениями параметров -----------> ^^^^^
Какие преимущества приносит использование параметров?
Защита от SQL Injections. Смотрите пример SQL Injectionвнизу. правильное квотирование литералов в зависимости от их типа (пример со строками, пример с датами). Оптимизация - сокращение времени работы SQL запроса. Благодаря использованию параметров следующие шаги не выполняются при повторном запуске (В зависимости от типа базы данных и от их настроек либо все либо некоторые из данных шагов будут пропущены при повторном выполнении):
проверка синтаксиса SQL запроса проверка прав доступа к объектам БД построение плана выполнения SQL запроса
Защита от переполнения/вытеснения кеша SQL запросов. Например "безобидный" запрос qry = f"SELECT first_name, last_name FROM users WHERE id = {user_id}", который часто выполняется в нагруженной системе с различными значениями user_id может вытеснить из кеша запросов полезные запросы. Если использовать qry = f"SELECT first_name, last_name FROM users WHERE id = ?" - в кеше запросов сохранится только один запрос, т.к. его checksum не изменяется вне зависимости от значения параметра.
Пример использования параметров в SQL запросе:
import sqlite3
con = sqlite3.connect(":memory:") cur = con.cursor() cur.execute("create table lang (name, first_appeared)")
# This is the qmark style: cur.execute("insert into lang values (?, ?)", ("C", 1972))
# The qmark style used with executemany(): lang_list = [ ("Fortran", 1957), ("Python", 1991), ("Go", 2009), ] cur.executemany("insert into lang values (?, ?)", lang_list)
# And this is the named style: cur.execute("select * from lang where first_appeared=:year", {"year": 1972}) print(cur.fetchall())
con.close()
При таком подходе можно использовать cursor.executemany() - это значительно быстрее и эффективнее по сравнению с вставкой в цикле по одной строке.
Стоит упомянуть о том, что параметризировать можно только значения, но не наименования объектов БД (таблиц, столбцов, индексов, constraints, etc.). Для подстановки наименований БД придется использовать форматирование строк или же "хардкодировать" их.
Пример:
table_name = "my_table" column_name = "id" qry = f"SELECT max({column_name}) as max_val FROM {table_name}" cursor.execute(qry)
В этом запросе невозможно параметризировать имя таблицы или имя столбца - их можно подставить при помощи форматирования строк.
К сожалению в Python нет единого стандарта стиля параметров (paramstyle) БД - выбор paramstyle зависит от реализации драйвера БД. В PEP-0249 указаны все возможные виды paramstyle:
paramstyle Meaning
qmark Question mark style, e.g. ...WHERE name = ?
numeric Numeric, positional style, e.g. ...WHERE name = :1
named Named style, e.g. ...WHERE name = :name
format ANSI C printf format codes, e.g. ...WHERE name = %s
pyformat Python extended format codes, e.g. ...WHERE name = %(name)s
Вот список поддерживаемых paramstyle для наиболее популярных драйверов БД в Python:
DB driver paramstyle
sqlite3 qmark (?), numeric (:1)
cx_Oracle named (:name), numeric (:1)
pymysql format (%s)
mysql.connector format (%s), pyformat (%(name)s)
psycopg2 format (%s), pyformat (%(name)s)
pymssql format (%s), pyformat (%(name)s)
PyODBC qmark (?)
Как динамически сформировать запрос с заранее неизвестным количеством параметров (значения параметров заданы в виде списка)?
Например как передать параметры в следующий запрос с использованием named "paramstyle":
SELECT data FROM table WHERE data in (:param_name1, :param_name2, ...)
Можно динамически сформировать запрос и словарь с параметрами:
values = ['val_1', 'val_2', 'val_3'] param_names = [f"p{i}" for i in range(len(values))] in_str = ", ".join(":" + p for p in param_names)) params = dict(zip(param_names, values)) qry = f"""SELECT * FROM table WHERE data in ({in_str})""" cursor.execute(qry, params)
получим такие запрос и параметры:
In [82]: qry Out[82]: 'SELECT * FROM table WHERE data in (:p0, :p1, :p2)'
In [83]: params Out[83]: {'p0': 'val_1', 'p1': 'val_2', 'p2': 'val_3'}
Пример SQL Injection (никогда так не делайте - это опасно):
id_ = input("Введите ID клиента: ") cur.execute(f"SELECT first_name, last_name FROM customers WHERE id = {id_}") print(cur.fetchall())
"Злобный хакер" может ввести следующее значение "id": 123 UNION ALL SELECT username, password FROM secure_logins
В итоге в базе данных выполнится следующий запрос:
SELECT first_name, last_name FROM customers WHERE id = 123 UNION ALL SELECT username, password FROM secure_logins
вместо того, чтобы вывести только имя и фамилию введенного клиента - программа выведет еще и все логины и пароли на экран.
Как сделать правильно (безопасно):
id_ = int(input("Введите ID клиента: ")) cur.execute(f"SELECT first_name, last_name FROM customers WHERE id = ?", [id_])
В таком варианте сервер БД вернет сообщение об ошибке на попытку хакерской атаки.
Вот несколько примеров таких вопросов:
Почему при вставке в таблицу передаваемые значения воспринимаются как названия столбцов? Как устранить ошибку "sqlite3.OperationalError: no such column: ..."? Как экранировать SQL запрос, чтобы избежать SQL injections? Как передавать параметр типа string в SQL запрос? Как правильно заменить местозаполнители в запросе? SQL запрос с несколькими условиями
Поэтому я решил создать новый обобщённый вопрос и более-менее канонический ответ для того, чтобы ссылаться на него в будущем. Данный ответ планируется расширять новыми подробностями и примерами.
Ответ
Лучше всего использовать SQL запросы с использованием параметров:
cursor.execute("SELECT * FROM my_table WHERE id = ?", [123]) # parameter placeholder ------------------------> ^ # список/кортеж со значениями параметров -----------> ^^^^^
Какие преимущества приносит использование параметров?
Защита от SQL Injections. Смотрите пример SQL Injectionвнизу. правильное квотирование литералов в зависимости от их типа (пример со строками, пример с датами). Оптимизация - сокращение времени работы SQL запроса. Благодаря использованию параметров следующие шаги не выполняются при повторном запуске (В зависимости от типа базы данных и от их настроек либо все либо некоторые из данных шагов будут пропущены при повторном выполнении):
проверка синтаксиса SQL запроса проверка прав доступа к объектам БД построение плана выполнения SQL запроса
Защита от переполнения/вытеснения кеша SQL запросов. Например "безобидный" запрос qry = f"SELECT first_name, last_name FROM users WHERE id = {user_id}", который часто выполняется в нагруженной системе с различными значениями user_id может вытеснить из кеша запросов полезные запросы. Если использовать qry = f"SELECT first_name, last_name FROM users WHERE id = ?" - в кеше запросов сохранится только один запрос, т.к. его checksum не изменяется вне зависимости от значения параметра.
Пример использования параметров в SQL запросе:
import sqlite3
con = sqlite3.connect(":memory:") cur = con.cursor() cur.execute("create table lang (name, first_appeared)")
# This is the qmark style: cur.execute("insert into lang values (?, ?)", ("C", 1972))
# The qmark style used with executemany(): lang_list = [ ("Fortran", 1957), ("Python", 1991), ("Go", 2009), ] cur.executemany("insert into lang values (?, ?)", lang_list)
# And this is the named style: cur.execute("select * from lang where first_appeared=:year", {"year": 1972}) print(cur.fetchall())
con.close()
При таком подходе можно использовать cursor.executemany() - это значительно быстрее и эффективнее по сравнению с вставкой в цикле по одной строке.
Стоит упомянуть о том, что параметризировать можно только значения, но не наименования объектов БД (таблиц, столбцов, индексов, constraints, etc.). Для подстановки наименований БД придется использовать форматирование строк или же "хардкодировать" их.
Пример:
table_name = "my_table" column_name = "id" qry = f"SELECT max({column_name}) as max_val FROM {table_name}" cursor.execute(qry)
В этом запросе невозможно параметризировать имя таблицы или имя столбца - их можно подставить при помощи форматирования строк.
К сожалению в Python нет единого стандарта стиля параметров (paramstyle) БД - выбор paramstyle зависит от реализации драйвера БД. В PEP-0249 указаны все возможные виды paramstyle:
paramstyle Meaning
qmark Question mark style, e.g. ...WHERE name = ?
numeric Numeric, positional style, e.g. ...WHERE name = :1
named Named style, e.g. ...WHERE name = :name
format ANSI C printf format codes, e.g. ...WHERE name = %s
pyformat Python extended format codes, e.g. ...WHERE name = %(name)s
Вот список поддерживаемых paramstyle для наиболее популярных драйверов БД в Python:
DB driver paramstyle
sqlite3 qmark (?), numeric (:1)
cx_Oracle named (:name), numeric (:1)
pymysql format (%s)
mysql.connector format (%s), pyformat (%(name)s)
psycopg2 format (%s), pyformat (%(name)s)
pymssql format (%s), pyformat (%(name)s)
PyODBC qmark (?)
Как динамически сформировать запрос с заранее неизвестным количеством параметров (значения параметров заданы в виде списка)?
Например как передать параметры в следующий запрос с использованием named "paramstyle":
SELECT data FROM table WHERE data in (:param_name1, :param_name2, ...)
Можно динамически сформировать запрос и словарь с параметрами:
values = ['val_1', 'val_2', 'val_3'] param_names = [f"p{i}" for i in range(len(values))] in_str = ", ".join(":" + p for p in param_names)) params = dict(zip(param_names, values)) qry = f"""SELECT * FROM table WHERE data in ({in_str})""" cursor.execute(qry, params)
получим такие запрос и параметры:
In [82]: qry Out[82]: 'SELECT * FROM table WHERE data in (:p0, :p1, :p2)'
In [83]: params Out[83]: {'p0': 'val_1', 'p1': 'val_2', 'p2': 'val_3'}
Пример SQL Injection (никогда так не делайте - это опасно):
id_ = input("Введите ID клиента: ") cur.execute(f"SELECT first_name, last_name FROM customers WHERE id = {id_}") print(cur.fetchall())
"Злобный хакер" может ввести следующее значение "id": 123 UNION ALL SELECT username, password FROM secure_logins
В итоге в базе данных выполнится следующий запрос:
SELECT first_name, last_name FROM customers WHERE id = 123 UNION ALL SELECT username, password FROM secure_logins
вместо того, чтобы вывести только имя и фамилию введенного клиента - программа выведет еще и все логины и пароли на экран.
Как сделать правильно (безопасно):
id_ = int(input("Введите ID клиента: ")) cur.execute(f"SELECT first_name, last_name FROM customers WHERE id = ?", [id_])
В таком варианте сервер БД вернет сообщение об ошибке на попытку хакерской атаки.
