Prepared statements w SQLite: prepare, bind, step, finalize

Czym właściwie jest prepared statement

Gdy przekazujesz SQLite tekst zapytania SQL, zanim ruszy jakikolwiek wiersz, musi on wykonać sporo pracy: podzielić tekst na tokeny, sparsować go, sprawdzić, czy tabele i kolumny istnieją, zaplanować wykonanie i skompilować plan do kodu bajtowego dla maszyny wirtualnej SQLite. Dopiero wtedy zapytanie naprawdę się wykonuje.

Prepared statement (instrukcja przygotowana) to to, co dostajesz, gdy zatrzymasz się na etapie "skompilowane do kodu bajtowego" i zachowasz ten wynik. Skompilowany program ma miejsca, czyli symbole zastępcze, w które później trafią prawdziwe wartości. Ten sam skompilowany program możesz uruchomić wiele razy z różnymi wartościami i możesz go bezpiecznie uruchamiać z wartościami, które pochodzą z niezaufanego wejścia.

To jak różnica między wręczaniem komuś przepisu do przeczytania na głos przy każdym gotowaniu a nauczeniem go przepisu raz i podawaniem tylko składników danego dnia.

Cykl życia: prepare, bind, step, finalize

Każdy sterownik SQLite w każdym języku opakowuje te same cztery wywołania API w C. Znajomość ich nazw pomaga, nawet jeśli nigdy nie napiszesz linijki w C, bo komunikaty błędów i dokumentacja używają tego słownictwa:

1. sqlite3_prepare_v2: kompiluje tekst SQL do uchwytu instrukcji.
2. sqlite3_bind_*: wstawia wartości w symbole zastępcze (jedna funkcja na typ).
3. sqlite3_step: uruchamia program. Przy SELECT wywołujesz go wielokrotnie, by przejść przez wiersze. Przy INSERT/UPDATE/DELETE jedno wywołanie wykonuje całą pracę.
4. sqlite3_finalize: zwalnia skompilowany program, gdy już skończysz.

Pomiędzy wykonaniami sqlite3_reset przewija zakończoną instrukcję, dzięki czemu możesz ponownie związać wartości i ją wykonać bez ponownego przygotowywania.

Symbole zastępcze w SQL

W tekście SQL każde miejsce na wartość oznaczasz symbolem zastępczym, zamiast wklejać tam samą wartość. SQLite obsługuje kilka form:

-- Anonimowe, pozycyjne:
INSERT INTO users (name, email) VALUES (?, ?);

-- Numerowane:
INSERT INTO users (name, email) VALUES (?1, ?2);

-- Nazwane:
INSERT INTO users (name, email) VALUES (:name, :email);
INSERT INTO users (name, email) VALUES (@name, @email);
INSERT INTO users (name, email) VALUES ($name, $email);

? to najczęstsza forma w kodzie na poziomie sterownika. Nazwane symbole zastępcze (:name) czyta się lepiej, gdy parametrów jest kilka albo ta sama wartość występuje więcej niż raz. Wybierz jeden styl na projekt i trzymaj się go.

Czego nie robić: budować zapytania przez sklejanie tekstów:

-- NIE RÓB TEGO:
"INSERT INTO users (name) VALUES ('" + user_input + "')"

To prosta droga do SQL injection, a do tego przekreśla ponowne użycie kodu bajtowego, o którym przeczytasz za chwilę.

Przykład krok po kroku w SQL

Żeby zobaczyć mechanikę bez języka programowania, oto odpowiednik prepare/bind/step z użyciem wyłącznie funkcji SQL, które daje SQLite. Utwórz tabelę i wstaw wiersz, używając symbolu zastępczego w stylu parametru wypełnionego literałem:

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;

W prawdziwej aplikacji wartości nie wpisuje się bezpośrednio. Robisz raz prepare na INSERT z symbolami ?, ?, a potem dla każdego użytkownika bind wiąże parę imię i e-mail, po czym następuje step. Skompilowany kod bajtowy jest identyczny przy każdym wywołaniu, zmieniają się tylko związane wartości.

Ponowne użycie instrukcji (zysk wydajności)

Oto wzorzec, który pozwala zapisać twój sterownik. To pseudokod, bo każdy język zapisuje go trochę inaczej, ale kształt jest uniwersalny:

-- przygotowane raz:
INSERT INTO users (name, email) VALUES (?, ?);

-- potem, w pętli:
--   bind(1, name)
--   bind(2, email)
--   step()
--   reset()

Przygotowanie parsuje i kompiluje SQL tylko raz. Każda iteracja jedynie uruchamia kod bajtowy i kopiuje wartości w miejsca. Przy masowych wstawieniach (na przykład import 100 000 wierszy) to dramatycznie szybsze niż wykonanie 100 000 osobno parsowanych instrukcji, często o rząd wielkości, zwłaszcza w jednej transakcji.

Częsta pułapka: pętla, w której prepare jest wywoływane wewnątrz. To wyrzuca całą korzyść. Przygotuj instrukcję poza pętlą, a wiąż i wykonuj w środku.

Dlaczego to bezpieczny sposób

Związane parametry nie są tekstem wstawianym do SQL. To wartości przekazywane programowi w kodzie bajtowym przez miejsca z typami: dla liczb całkowitych, tekstu, blobów. SQLite nigdy nie parsuje ich ponownie jako SQL, więc żadna wartość nie może zmienić struktury zapytania.

Porównaj:

-- Podatne. Jeśli user_input to:  '); DROP TABLE users;--
-- zapytanie staje się niszczące.
"SELECT * FROM users WHERE name = '" + user_input + "'"

-- Bezpieczne. user_input jest wiązane jako wartość TEXT i zawsze
-- porównywane jako tekst, bez względu na zawartość.
SELECT * FROM users WHERE name = ?;

Druga forma jest bezpieczna, nawet jeśli user_input to '); DROP TABLE users;--. SQLite posłusznie poszuka użytkownika, którego imię jest dokładnie tym (dziwnym) tekstem, nie znajdzie żadnego i zwróci zero wierszy. Nic w strukturze zapytania nie może się zmienić z powodu wartości.

Wstrzykiwaniu SQL przyjrzymy się dokładniej na dalszej stronie, ale wniosek jest taki: prepared statements to nie jedna z metod obrony przed SQL injection, tylko ta metoda.

Instrukcje, które zwracają wiersze

Przy SELECT każde step zwraca jeden wiersz. Sterownik zwykle wywołuje je w pętli, aż dostanie informację "koniec":

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

INSERT INTO products (name, price) VALUES
    ('Zeszyt', 4.50),
    ('Długopis', 1.20),
    ('Gumka',    0.80);

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

W kodzie aplikacji sterownik zrobiłby prepare na tym SELECT z ? w miejscu 2.00, związał wartość progu i wywoływał step w pętli, odczytując jeden wiersz na wywołanie. Po ostatnim wierszu step zgłasza zakończenie, a sterownik albo robi reset instrukcji (by uruchomić ją ponownie z nowym progiem), albo finalize.

Nie zapomnij o finalize

Prepared statement to niewielka alokacja wewnątrz SQLite. Wycieki zjadają pamięć i, co ważniejsze, utrzymują wewnętrzną blokadę bazy, która może blokować innych piszących. Każdy sterownik daje sposób na automatyczne sprzątanie: menedżery kontekstu w Pythonie, bloki using w C#, RAII w C++. Korzystaj z nich:

• sqlite3 w Pythonie finalizuje instrukcję, gdy kursor zostanie usunięty przez garbage collector, ale jawne cursor.close() jest czystsze.
• better-sqlite3 (Node) finalizuje, gdy Statement zostanie usunięty przez garbage collector, więc długo żyjące prepared statements są w porządku.
• W czystym C wywołujesz sqlite3_finalize samodzielnie. Zapomnienie o tym to prawdziwy błąd.

Praktyczna zasada: jeśli coś przygotowano, coś musi to sfinalizować.

Kiedy nie musisz robić tego sam

Rzadko wywołasz sqlite3_prepare_v2 bezpośrednio. Sterowniki wysokiego poziomu zamieniają connection.execute("SELECT ... WHERE id = ?", (42,)) na prepare/bind/step/finalize za ciebie. Cykl życia warto rozumieć, bo:

• Rozpoznasz, co się dzieje, gdy zobaczysz błędy "statement is busy" albo "cannot operate on a finalized statement".
• Będziesz wiedzieć, że przy wstawianiu w ciasnej pętli warto trzymać w pamięci długo żyjące prepared statements.
• Będziesz odruchowo pisać zapytania parametryzowane, nawet gdy sklejanie tekstów wydaje się kuszące.

ORM i konstruktory zapytań idą jeszcze dalej. Budują SQL, zarządzają prepared statements i oddają ci wyniki z typami. Pod spodem to wciąż te same cztery wywołania.

Dalej: wiązanie parametrów

O symbolach zastępczych mówiliśmy do tej pory abstrakcyjnie. Następnie przyjrzymy się szczegółowo stronie wiązania: parametrom pozycyjnym i nazwanym, obsłudze typów, NULL i drobnym pułapkom, które pojawiają się, gdy zaczynasz przekazywać do zapytań prawdziwe dane aplikacji.