Параметризованные запросы¶

Picodata поддерживает как обычные, так и параметризованные DQL- и DML-запросы. При использовании протокола PostgreSQL параметризация позволяет создать подготовленный запрос — то есть, запланировать SQL-запрос один раз, а затем многократно исполнять его, подставляя разные значения параметров.

Параметры используются в роли "заполнителей данных" для SQL-выражений, в которых они позволяют передать нужные значения. Для указания параметров используется синтаксис PostgreSQL, причем каждый параметр имеет порядковый номер и начинается со знака $ (например, $1). Параметры нумеруются по возрастанию, начиная с 1. Пропуски в нумерации не допускаются.

Пример использования параметризации из кода приложения:

SELECT item FROM warehouse WHERE id = $1;

Пример использования параметризации в консоли psql:

user=> SELECT $1 \bind 'foo' \g
 col_1
-------
 foo
(1 строка)

SELECT 5 * $1 \bind 10 \g
 col_1
-------
    50
(1 строка)

Примечание

Обратите внимание, что технологический стек может влиять на то, как будут указаны параметры в SQL-запросе. Например, psql и rust-postgres придерживаются стандартной конвенции PostgreSQL, тогда как jdbc (Java) требует указывать параметры через ?. В популярном драйвере psycopg (Python) для параметров используется C-подобная конвенция (%s). При наличии сомнений рекомендуем обратиться к документации вашего технологического стека (jdbc, odbc, db-api и т.д.).

Другое полезное применение параметризации запросов — защита от SQL-инъекций, когда происходит примитивная подстановка значения параметра на уровне приложения. Рассмотрим следующую ситуацию:

SELECT item FROM warehouse WHERE id = $1;

Неосторожная подстановка строки вместо $1 позволит атакующему внедрить в запрос деструктивные действия. При $1 == '1; DROP TABLE warehouse;' получим:

SELECT item FROM warehouse WHERE id = 1; DROP TABLE wharehouse;

Параметризация позволяет защититься от этой уязвимости: СУБД обрабатывает параметры исключительно как значения переменных, а не как команды, предотвращая таким образом выполнение вредоносного кода.

Протокол PostgreSQL позволяет клиентским библиотекам не указывать типы параметров при создании подготовленных запросов, поэтому крайне желательно, чтобы СУБД умела выводить типы параметров и валидировать пользовательские запросы до их исполнения. Picodata до версии 25.2 обладала крайне ограниченным механизмом вывода типов параметров, в результате чего их часто приходилось явно уточнять через приведение типа:

SELECT item FROM wharehouse WHERE id = $1::int;

В версии 25.2 была представлена новая система типов, которая позволила выводить типы параметров и более качественно валидировать пользовательские запросы.

Например, в следующих случаях приведение типов более не требуется:

SELECT A + $1 FROM t;
SELECT COALESCE(int_col, $1) FROM t;
INSERT INTO t (int_col, datetime_col) VALUES ($1, $2);

Тем не менее, существуют ситуации, когда тип параметра нужно уточнить при помощи явного приведения типа:

пользователю не подходит тип, который автоматически вывела Picodata, потому что бинарный режим передачи параметров в протоколе PostgreSQL требует строгого соответствия типа и формата данных, но по каким-то причинам приложение не готово передавать параметры в этом формате. Например, если Picodatа вывела, что $1 имеет тип int, то при попытке передать по сети double в формате IEEE 754 пользователь получит ошибку при исполнении запроса.
запрос содержит выражения, которые конфликтуют друг с другом с точки зрения системы вывода типов. Например:
пример конфликтующих запросов
```
CREATE TABLE t (id int primary key, val text);
SELECT * FROM t WHERE id = $1 AND val != $1;
```
сообщение о невозможности прочитать данные с указанным фильтром
```
ERROR:  sbroad: inconsistent types text and int deduced for parameter $1,
consider using transitive type casts through a common type, e.g. $1::text::int and $1::text
```