Wiązanie parametrów w SQLite: ?, :name i bezpieczne wartości

Wiązanie wprowadza wartości do prepared statement

Prepared statement to SQL z dziurami. Wiązanie (binding) to wypełnianie tych dziur wartościami: bezpiecznie, jedna po drugiej, przez API sterownika, a nie przez sklejanie ciągów znaków.

Schemat zawsze wygląda tak samo: piszesz SQL z symbolami zastępczymi, a wartości przekazujesz osobno.

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

-- SQL, który widzi baza danych:
INSERT INTO users (name, email) VALUES (?, ?);

W CLI nie da się tak naprawdę pokazać wiązania (powłoka nie ma podpiętego kodu aplikacji), ale powyższy SQL to dokładnie to, co wysyła twoja aplikacja. Znaki ? to symbole zastępcze. Twój sterownik (sqlite3 w Pythonie, better-sqlite3 w Node, rusqlite w Rust) wypełnia je osobnym wywołaniem bind.

Model myślowy: SQL to przepis, a powiązane wartości to składniki. Nigdy się nie mieszają.

Symbole zastępcze pozycyjne: ?

Najprostszy symbol zastępczy to ?. Każdy odpowiada kolejnej wiązanej wartości, po kolei.

INSERT INTO users (name, email) VALUES (?, ?);

W Pythonie wygląda to tak:

cursor.execute(
    "INSERT INTO users (name, email) VALUES (?, ?)",
    ("Rosa", "rosa@example.com"),
)

Pierwszy ? dostaje "Rosa", drugi dostaje "rosa@example.com". Przekaż za mało lub za dużo wartości, a sterownik zgłosi błąd, zanim instrukcja się wykona.

Możesz też jawnie je numerować: ?1, ?2, ?3. To przydatne, gdy ta sama wartość występuje więcej niż raz:

SELECT ?1 AS greeting, ?1 AS still_the_same;

?1 używa ponownie pierwszej powiązanej wartości. Bez numerowania trzeba by wiązać tę samą wartość dwa razy.

Symbole zastępcze nazwane: :name

Gdy instrukcja ma więcej niż dwie lub trzy dziury, wiązanie pozycyjne zamienia się w zgadywankę. Parametry nazwane to naprawiają:

INSERT INTO users (name, email)
VALUES (:name, :email);

W Pythonie:

cursor.execute(
    "INSERT INTO users (name, email) VALUES (:name, :email)",
    {"name": "Boris", "email": "boris@example.com"},
)

Kolejność kluczy w słowniku nie ma znaczenia, liczą się tylko nazwy. SQLite akceptuje też prefiksy @name i $name jako alternatywy i wszystkie działają tak samo. :name jest zdecydowanie najpopularniejszy.

Parametry nazwane opłacają się w chwili, gdy masz UPDATE z pięcioma kolumnami albo zapytanie, które używa tej samej wartości w WHERE i RETURNING.

Wiązanie NULL

Właściwy sposób na wstawienie NULL to przekazanie wartości null swojego języka przez API wiązania. Sterownik zajmie się tłumaczeniem:

INSERT INTO users (name, email) VALUES (?, ?);
-- Bind: ("Cyrus", None)   w Pythonie
-- Bind: ["Cyrus", null]   w Node

SELECT id, name, email FROM users;

None, null, nil, jakkolwiek nazywa to twój język: sterownik zamienia to na prawdziwe SQL-owe NULL. Nie wiąż ciągu "NULL", bo zapiszesz czteroznakowy tekst "NULL". I nie wstawiaj słowa NULL do tekstu SQL, bo to całkowicie niweczy wiązanie.

Ta sama zasada dotyczy liczb, blobów i dat: przekazuj natywną wartość i pozwól sterownikowi ją powiązać.

Ponowne użycie instrukcji z innymi wartościami

Wiązanie w naturalny sposób łączy się z prepared statements. Przygotowujesz raz, wiążesz i wykonujesz wiele razy. Parser wykonuje swoją pracę jeden raz, a baza używa skompilowanego planu dla każdego zestawu powiązanych wartości.

INSERT INTO users (name, email) VALUES (?, ?);
-- Powiąż ("Ada",   "ada@example.com")    -> wykonaj
-- Powiąż ("Boris", "boris@example.com")  -> wykonaj
-- Powiąż ("Cyrus", NULL)                 -> wykonaj

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

Większość sterowników opakowuje to w executemany (Python) albo pętlę .run() (Node). Tak czy inaczej oszczędzasz koszt parsowania: mały dla jednej instrukcji, ale odczuwalny, gdy wstawiasz tysiące wierszy.

Nie mieszaj stylów w jednej instrukcji

Technicznie SQLite pozwala na symbole pozycyjne i nazwane w tej samej instrukcji. Oprzyj się pokusie.

-- Dozwolone, ale to proszenie się o kłopoty:
INSERT INTO users (name, email) VALUES (?, :email);

Czytelnik musi w głowie śledzić dwa API wiązania naraz, a większość sterowników nie obsługuje dobrze formy mieszanej. Wybierz jeden styl na instrukcję: ? dla jednej lub dwóch wartości, :name dla całej reszty.

Typowa pułapka: wiązanie to nie formatowanie ciągów

Cały sens wiązania polega na tym, że wartości nie przechodzą przez parsowanie SQL. Porównaj te dwie linie w Pythonie:

# Źle (formatowanie ciągów):
cursor.execute(f"SELECT * FROM users WHERE name = '{name}'")

# Dobrze (wiązanie parametrów):
cursor.execute("SELECT * FROM users WHERE name = ?", (name,))

Pierwsza linia buduje SQL przez konkatenację. Jeśli name to "'; DROP TABLE users; --", baza chętnie sparsuje i wykona wstrzykniętą instrukcję. Druga linia wysyła SQL i wartość różnymi kanałami: wartość jest wiązana jako ciąg znaków i koniec, niezależnie od tego, jakie znaki zawiera. Dlatego każdy poradnik każe wiązać: nie chodzi o styl, tylko o to, co widzi parser.

Stroną związaną z wstrzykiwaniem zajmiemy się na następnej stronie.

Kolejna pułapka: nie da się wiązać identyfikatorów

Symbole zastępcze działają dla wartości: ciągów, liczb, blobów, NULL. Nie działają dla nazw tabel, nazw kolumn ani słów kluczowych SQL:

-- To NIE robi tego, czego chcesz:
SELECT * FROM ? WHERE id = ?;
-- Pierwszy ? wiąże się jako literał tekstowy, a nie nazwa tabeli.

Jeśli naprawdę potrzebujesz dynamicznej nazwy tabeli lub kolumny (rzadkość w kodzie aplikacji), sprawdź ją względem listy dozwolonych wartości i sam wklej ją do SQL, nigdy prosto z danych od użytkownika. We wszystkich innych przypadkach wiąż.

Przykład w praktyce

Składając wszystko razem: mała tabela users zapisywana i odczytywana wyłącznie przez wiązanie:

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

-- Aplikacja powiązałaby te wartości, tu pokazane jako literały:
INSERT INTO users (name, email)        VALUES ('Ada',   'ada@example.com');
INSERT INTO users (name, email)        VALUES ('Boris', 'boris@example.com');
INSERT INTO users (name, email, active) VALUES ('Cyrus', NULL, 0);

-- Prawdziwe zapytanie sterownika, z powiązanymi :name i :active:
SELECT id, name, email
FROM users
WHERE active = :active AND name LIKE :name
ORDER BY id;

W prawdziwym kodzie zarówno INSERT, jak i SELECT używałyby symboli zastępczych. CLI po prostu nie ma aplikacji, z której można wiązać, więc literały zastępują to, co dałoby wiązanie.

Dalej: ochrona przed SQL injection

Wiązanie parametrów to mechanizm. Dlaczego zatrzymuje SQL injection i w jakich nielicznych miejscach samo wiązanie nie wystarcza, o tym jest następna strona.