Статья основана на двадцать третьем видео из 31 темы курса SQL 2.0 — PL/pgSQL в PostgreSQL от Аристова Евгения, который является логическим продолжением курса SQL c 0. Ссылки на видео на платформах RUTUBE и VK video.
В данной статье подробно разбираются динамический SQL, оператор EXECUTE, подготовленный запрос и защита от SQL инъекции.
В прошлой статье мы разобрали SQL инъекции, SQL injection, примеры защиты инъекции и её ключевые принципы.
Презентация и исходники доступны по ссылке.
Динамический SQL
Часто требуется динамически формировать команды внутри функций на PL/pgSQL, то есть такие команды, в которых при каждом выполнении могут использоваться разные таблицы или типы данных. Обычно PL/pgSQL кеширует планы выполнения процедур, но в случае с динамическими командами это не будет работать.
Для исполнения динамических команд предусмотрен оператор EXECUTE:
EXECUTE строка [ INTO [STRICT] цель ] [ USING выражение [, ... ] ];где строка это выражение, формирующее текст SQL команды, которую нужно выполнить.
Необязательная цель — это переменная или разделённый запятыми список простых переменных и полей записи/кортежа, куда будут помещены результаты команды.
Необязательные выражения в USING формируют значения, которые будут вставлены в команду.
В сформированном тексте команды замена имён переменных PL/pgSQL на их значения проводиться не будет.
Все необходимые значения переменных должны быть вставлены в командную строку при её построении, либо нужно использовать параметры, как описано ниже.
Обратите внимание, что нет никакого плана кеширования для команд, выполняемых с помощью EXECUTE. Вместо этого план создаётся каждый раз при выполнении.
То есть, строка команды может динамически создаваться внутри функции для выполнения действий с различными таблицами и столбцами.
В тексте команды можно использовать значения параметров, ссылки на параметры обозначаются как $1, $2 и т. д. Эти символы указывают на значения, находящиеся в предложении USING. Такой метод зачастую предпочтительнее, чем вставка значений в команду в виде текста: он позволяет исключить во время исполнения дополнительные расходы на преобразования значений в текст и обратно, и не открывает возможности для SQL-инъекций, не требуя применять экранирование или кавычки для спецсимволов.
SQL injection для начинающих. Часть 1
Пример:
EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2'
INTO c
USING checked_user, checked_date;При работе с динамическими командами часто приходится иметь дело с экранированием одинарных кавычек, чтобы не допустить SQL инъекций.
Можно напрямую вызывать функции заключения в кавычки:
EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
|| ' = '
|| quote_literal(newvalue)
|| ' WHERE key = '
|| quote_literal(keyvalue);
Динамический SQL. Подготовленный запрос
Мы можем заранее создать запрос.
Постгрес его оптимально подготовит и закеширует план выполнения — не нужно будет каждый раз его строить. В дальнейшем мы можем использовать этот подготовленный запрос и передавать туда параметры, не изменяя структуры запроса. Если в запросе изменится набор полей и тд, нужно будет создавать новый подготовленный запрос:
PREPARE запрос AS имя
EXECUTE имя(параметры)Примеры:
PREPARE fooplan (int, text, bool, numeric) AS
INSERT INTO foo VALUES($1, $2, $3, $4);
EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);
PREPARE usrrptplan (int) AS
SELECT * FROM users u, logs l WHERE u.usrid=$1 AND u.usrid = l.usrid AND l.date = $2;
EXECUTE usrrptplan(1, current_date);Итоги
Помним про опасность SQL инъекций и методы защиты от них
Подготовленные запросы — прямой путь для оптимизации производительности
Больше примеров доступно на гитхабе и в видео.
В следующей статье мы разберём циклы.
Добавить комментарий