Sapphire Project Sp. z o.o.
Brilliancy of quality
Практический синтаксис SQLMacros для управления запросами БД
Документация

Практический синтаксис SQLMacros для управления запросами БД

Sapphire I.C.D.S.

Назначение и границы SQLMacros

SQLMacros — шаблонный слой для SQL в Sapphire I.C.D.S. Обычный запрос хранится в каталоге SQL/<Module>/<dialect>/, получает подготовленную таблицу значений, формирует итоговый SQL-текст и передаёт его активному драйверу базы данных. SQLMacros не открывает соединение, не выбирает backend, не начинает транзакцию и не решает, какой запрос разрешено выполнить. Эти обязанности остаются у модуля и DB-слоя.

Для MySQL и PostgreSQL поддерживайте отдельные файлы, даже если запросы похожи. Кавычки идентификаторов, функции дат, преобразования типов, upsert и агрегаты различаются. Попытка спрятать два диалекта в один сложный шаблон обычно хуже двух коротких проверяемых файлов.

Базовая подстановка Table.Data

В следующих примерах используется иллюстративная схема с таблицами example_articles и example_article_notes; это не реальные таблицы Sapphire I.C.D.S.

Главный источник входных данных — Table.Data("field"). Для одной строки курсор обычно уже подготовлен вызывающей стороной. Пример выборки с числовыми параметрами:

SELECT id, topic, created_at
FROM example_articles
WHERE layout_id = {{ Table.Data("layout_id") }}
  AND language_id = {{ Table.Data("language_id") }}
ORDER BY id DESC
LIMIT {{ Table.Data("limit") }} OFFSET {{ Table.Data("offset") }};

Так можно выводить только значения, которые модуль создал как целые числа и проверил по диапазону. Пользовательская строка "10; DELETE ..." не должна доходить до числового поля. Макрос не превращает произвольный текст в безопасное число.

Строковые литералы и escape

Строку помещают в SQL-кавычки, а значение пропускают через активный DB escape provider:

SELECT id, topic
FROM example_articles
WHERE status = '{{ escape(Table.Data("status")) }}'
  AND LOWER(topic) LIKE CONCAT('%', LOWER('{{ escape(Table.Data("query")) }}'), '%');

escape(...) и совместимый вариант Escape(...) экранируют содержимое для выбранного драйвера. Кавычки вокруг строкового литерала остаются частью SQL-шаблона. Не применяйте HTML escaping, ручную замену апострофов или URL encoding вместо DB escape.

Экранирование значения не разрешает делать его идентификатором. Никогда не пишите ORDER BY {{ Table.Data("sort") }}, FROM {{ ... }} или динамическое имя колонки из запроса пользователя. Допустимые варианты сортировки и колонок выбираются в модуле по allowlist, после чего вызывается конкретный статический шаблон либо передаётся заранее известный безопасный фрагмент по отдельному контракту.

Условия в шаблоне

Управляющие конструкции записываются между {% и %}. Они полезны для небольшого числа необязательных фильтров:

SELECT id, topic, status
FROM example_articles
WHERE layout_id = {{ Table.Data("layout_id") }}
{% if Table.Data("status") %}
  AND status = '{{ escape(Table.Data("status")) }}'
{% endif %}
{% if Table.Data("query") %}
  AND LOWER(topic) LIKE CONCAT('%', LOWER('{{ escape(Table.Data("query")) }}'), '%')
{% endif %}
ORDER BY id DESC
LIMIT {{ Table.Data("limit") }};

Следите, чтобы любой набор условий оставлял синтаксически корректный запрос. Удобно начинать с обязательного WHERE, а необязательные части добавлять через AND. Если ветвей много, лучше создать несколько логических запросов: это проще тестировать и безопаснее сопровождать.

Комментарий шаблонизатора имеет форму {# пояснение #} и не попадает в итоговый SQL. Обычный -- SQL comment остаётся в запросе. Не храните в комментариях секреты, реальные персональные данные или временные обходы прав.

INSERT и UPDATE

INSERT INTO example_article_notes (article_id, note, created_at)
VALUES ({{ Table.Data("article_id") }},
        '{{ escape(Table.Data("note")) }}',
        NOW());

Здесь article_id должен быть положительным числом из доверенной структурированной таблицы, а note — строкой через escape. Проверка владельца, прав пользователя, существования статьи и допустимой длины заметки выполняется до запроса. SQLMacros не добавляет эту политику автоматически.

Для update/delete всегда включайте точное ограничение сущности и, где требуется, владельца или layout. Массовое изменение без WHERE не становится приемлемым из-за того, что оно хранится в шаблоне. Для конкурентного редактирования используйте принятый в модуле revision/transaction contract, а не самодельную строку.

Pager: запрос данных и count

Список с пагинацией обычно имеет два шаблона: основной запрос с LIMIT/OFFSET и файл *.count.sql. Условия фильтрации в них должны совпадать. Иначе Pager покажет неверное число страниц. Удобный порядок проверки:

  1. скопировать только блок фильтров в count-запрос;
  2. проверить пустой поиск;
  3. проверить строку с апострофом и символами процента;
  4. проверить limit 1, последнюю страницу и offset за пределами результата;
  5. сравнить число строк основного запроса с count при одинаковом входе.

Environment, POST и Cookie

SQLMacros технически предоставляет значения окружения и запроса через вызовы вроде Env.Get(...), Post.Get(...) и Cookie.Get(...). Для обычной прикладной SQL-логики не обращайтесь к ним напрямую. Модуль должен прочитать HTTP-ввод, проверить тип, нормализовать значение, применить права и положить результат в Table. Такой путь делает SQL независимым от транспорта и пригодным для CGI, FastCGI, AJAX, сервиса или теста.

Что считать безопасным шаблоном

  • Структура SQL, таблицы, колонки и операторы записаны статически.
  • Каждая строка проходит escape(Table.Data(...)) внутри строкового литерала.
  • Числа, флаги, limit и offset типизированы и ограничены до рендера.
  • Шаблон лежит в правильном модуле и dialect-каталоге.
  • Выбор запроса и разрешение на выполнение остаются у модуля.
  • Тесты и диагностика подтверждают, что успешный результат не содержит нераскрытых {{ ... }} или {% ... %}.
  • Текущий runtime при сбое SQLMacros может вернуть исходный текст шаблона; вызывающий DB-контур не гарантирует отдельную fail-closed блокировку. Не рассматривайте рендер как security boundary: такие ошибки необходимо выявлять тестами и журналами до production.

Проверяйте SELECT на тестовых данных, а modify-запросы — внутри контролируемой транзакции или на изолированной базе. Не тестируйте новый DELETE/UPDATE на production. SQLMacros уменьшает дублирование и централизует экранирование, но безопасность возникает только вместе с типизированным входом, серверной авторизацией и статической структурой запроса.

Диагностика результата рендера

При ошибке сначала зафиксируйте логическое имя запроса, dialect и набор входных полей без секретных значений. Затем получите итоговый SQL в безопасном диагностическом контуре и убедитесь, что все макросы раскрылись, строковые кавычки сбалансированы, а необязательные ветви дали ожидаемый текст. Не исправляйте проблему добавлением случайных кавычек вокруг чисел: вернитесь к типу поля и контракту вызывающего модуля.

Для нового запроса подготовьте граничные случаи: пустая строка, апостроф, Unicode, нулевой результат, максимальная разрешённая длина, минимальный и максимальный идентификатор. Проверка должна охватывать оба поддерживаемых dialect-файла. Если один backend не используется локально, запрос всё равно не следует считать готовым до синтаксической или интеграционной проверки в соответствующем окружении.