Подготовленные запросы в SQLite: prepare, bind, step

Что такое подготовленный запрос на самом деле

Когда вы отдаёте SQLite строку с SQL, движку приходится проделать немалую работу ещё до того, как хоть одна строка результата сдвинется с места: разобрать текст на токены, распарсить, проверить наличие таблиц и колонок, построить план выполнения и скомпилировать его в байт-код для виртуальной машины SQLite. И только после этого запрос реально выполняется.

Подготовленный запрос (prepared statement) — это и есть результат остановки на стадии «скомпилировано в байт-код», который вы сохраняете и держите под рукой. В скомпилированной программе остаются «дырки» — плейсхолдеры, куда позже подставятся реальные значения. Одну и ту же программу можно прогонять много раз с разными значениями, причём безопасно — даже если данные пришли из недоверенного источника.

Удобная аналогия: либо каждый раз вслух зачитывать рецепт повару, либо один раз его выучить и в день готовки просто называть ингредиенты.

Жизненный цикл: prepare, bind, step, finalize

Любая обёртка над SQLite в любом языке программирования прячет под капотом одни и те же четыре вызова C-API. Названия стоит запомнить, даже если вы никогда не напишете ни строчки на C, — этот словарь встречается в сообщениях об ошибках и в документации:

1. sqlite3_prepare_v2 — скомпилировать строку SQL в дескриптор подготовленного запроса.
2. sqlite3_bind_* — подставить значения в плейсхолдеры (под каждый тип своя функция).
3. sqlite3_step — запустить программу. Для SELECT вызывается в цикле и выдаёт строки одну за другой. Для INSERT/UPDATE/DELETE достаточно одного вызова.
4. sqlite3_finalize — освободить скомпилированную программу, когда она больше не нужна.

Между прогонами есть ещё sqlite3_reset — он перематывает уже отработавший запрос в начало, чтобы можно было заново забиндить параметры и выполнить его без повторной компиляции.

Плейсхолдеры в SQL

Внутри SQL-строки каждое подставляемое значение помечается плейсхолдером, а не вклеивается в текст напрямую. SQLite поддерживает несколько форм записи:

-- Анонимные, позиционные:
INSERT INTO users (name, email) VALUES (?, ?);

-- Нумерованные:
INSERT INTO users (name, email) VALUES (?1, ?2);

-- Именованные:
INSERT INTO users (name, email) VALUES (:name, :email);
INSERT INTO users (name, email) VALUES (@name, @email);
INSERT INTO users (name, email) VALUES ($name, $email);

? чаще всего встречается в коде на уровне драйвера. Именованные плейсхолдеры (:name) читаются лучше, когда параметров несколько или одно и то же значение используется не один раз. Выберите один стиль в рамках проекта и придерживайтесь его.

А вот чего точно делать не стоит — собирать запрос конкатенацией строк:

-- НЕ ДЕЛАЙТЕ ТАК:
"INSERT INTO users (name) VALUES ('" + user_input + "')"

Это прямой путь к SQL-инъекциям, и заодно вы лишаетесь того самого переиспользования байт-кода, о котором речь пойдёт ниже.

Разбор на чистом SQL

Чтобы увидеть механику prepare/bind/step без участия хост-языка, посмотрим аналог на тех средствах, которые SQLite предоставляет сам по себе. Создадим таблицу и вставим строку, используя плейсхолдер в стиле параметров, который заполним литералом:

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

INSERT INTO users (name, email) VALUES ('Ada', 'ada@example.com');
INSERT INTO users (name, email) VALUES ('Boris', 'boris@example.com');

SELECT id, name, email FROM users ORDER BY id;

В реальном приложении значения не вшивают в запрос напрямую — вместо этого INSERT prepare-ится один раз с плейсхолдерами ?, ?, а дальше для каждого пользователя просто делаются bind пары имя/email и step. Скомпилированный байткод при этом одинаков для всех вызовов; меняются только привязанные значения.

Переиспользование подготовленного запроса (выигрыш в производительности)

Вот паттерн, который позволяет реализовать ваш драйвер. Это псевдокод — в каждом языке синтаксис чуть свой, — но общая форма везде одинаковая:

-- готовится один раз:
INSERT INTO users (name, email) VALUES (?, ?);

-- затем в цикле:
--   bind(1, name)
--   bind(2, email)
--   step()
--   reset()

Когда вы готовите запрос, SQL разбирается и компилируется один раз. На каждой итерации выполняется только байт-код, а значения просто кладутся в нужные слоты. Для массовых вставок (скажем, импорт 100 000 строк) это работает в разы быстрее, чем гонять 100 000 отдельно разбираемых запросов — нередко на порядок, особенно если обернуть всё в одну транзакцию.

Типичная ошибка: люди пишут цикл и вызывают prepare внутри него. Так весь смысл подготовленных запросов теряется. Готовьте запрос снаружи цикла, а bind и step делайте внутри.

Почему так безопаснее

Параметры в bind — это не строки, которые подставляются в SQL. Это значения, которые передаются в байт-код через типизированные слоты: для целых чисел, для текста, для blob. SQLite никогда не разбирает их повторно как SQL, поэтому никакое значение в принципе не способно изменить структуру запроса.

Сравните:

-- Уязвимо. Если user_input равен:  '); DROP TABLE users;--
-- запрос становится разрушительным.
"SELECT * FROM users WHERE name = '" + user_input + "'"

-- Безопасно. user_input передаётся как значение TEXT и всегда
-- сравнивается как строка, что бы в нём ни содержалось.
SELECT * FROM users WHERE name = ?;

Второй вариант безопасен, даже если user_input равен '); DROP TABLE users;--. SQLite послушно поищет пользователя с именем, точно равным этой (странной) строке, не найдёт ни одного и вернёт ноль строк. Структура запроса при этом не меняется — что бы ни лежало в значении.

К теме SQL-инъекций мы ещё вернёмся в отдельной статье, но суть такая: подготовленные запросы — это не одна из защит от SQL-инъекций, а та самая защита.

Запросы, возвращающие строки

Для SELECT функция step отдаёт по одной строке за вызов. Драйвер обычно крутится в цикле, пока не получит «готово»:

CREATE TABLE products (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    price REAL NOT NULL
);

INSERT INTO products (name, price) VALUES
    ('Тетрадь',   4.50),
    ('Ручка',     1.20),
    ('Ластик',    0.80);

SELECT name, price FROM products WHERE price < 2.00 ORDER BY price;

В реальном коде драйвер взял бы этот SELECT, заменил 2.00 на ? через prepare, подставил пороговое значение через bind и в цикле вызывал step, считывая по одной строке за раз. Когда строки закончатся, step сообщит о завершении, и драйвер либо вызовет reset (чтобы запустить тот же запрос с новым порогом), либо finalize.

Не забывайте про finalize

Подготовленный запрос — это небольшой блок памяти внутри SQLite. Если их не освобождать, утекает память, а главное — удерживается внутренняя блокировка БД, которая может стопорить других писателей. В каждом драйвере есть способ почистить ресурсы автоматически: context manager в Python, блок using в C#, RAII в C++ — и пользоваться ими стоит обязательно:

• В Python модуль sqlite3 финализирует курсор при сборке мусора, но явный cursor.close() всё же чище.
• В better-sqlite3 (Node) Statement финализируется при сборке мусора; держать долгоживущие подготовленные запросы здесь нормально.
• В чистом C sqlite3_finalize приходится вызывать вручную. Забыли — получили реальный баг.

Правило простое: если вы его подготовили — кто-то должен его финализировать.

Когда вам это, возможно, и не понадобится

Напрямую вызывать sqlite3_prepare_v2 приходится редко. Высокоуровневые драйверы сами разворачивают connection.execute("SELECT ... WHERE id = ?", (42,)) в связку prepare/bind/step/finalize. Понимать жизненный цикл всё равно полезно — вот почему:

• Вы сразу поймёте, в чём дело, когда увидите ошибки вроде "statement is busy" или "cannot operate on a finalized statement".
• Будете знать, что в горячем цикле вставок имеет смысл закэшировать долгоживущий подготовленный запрос.
• Параметризованные запросы станут рефлексом — даже когда конкатенация строк выглядит соблазнительно.

ORM и построители запросов идут ещё дальше: они и SQL соберут, и подготовленными запросами поуправляют, и отдадут вам уже типизированные результаты. А под капотом — всё те же четыре вызова.

Дальше: привязка параметров

Про плейсхолдеры мы пока говорили в общих чертах. В следующий раз разберём bind подробно: позиционные и именованные параметры, обработку типов, NULL и мелкие подводные камни, которые всплывают, когда в запросы начинают улетать реальные данные приложения.