Prepared statement in SQLite: prepare, bind, step, finalize

Cos'è davvero un prepared statement

Quando passi a SQLite una stringa SQL, prima che si muova una sola riga deve fare parecchio lavoro: suddividerla in token, analizzarla, verificare che tabelle e colonne esistano, pianificare come eseguirla e compilare il piano in bytecode per la macchina virtuale di SQLite. Solo a quel punto la query viene davvero eseguita.

Un prepared statement è ciò che ottieni quando ti fermi a "compilato in bytecode" e tieni da parte il risultato. Il programma compilato ha degli spazi, i segnaposto, in cui i valori veri verranno inseriti più tardi. Puoi eseguire lo stesso programma compilato molte volte con valori diversi, e puoi eseguirlo in sicurezza con valori che arrivano da input non affidabile.

Pensala come la differenza tra dare a qualcuno una ricetta da leggere ad alta voce ogni volta che cucina e insegnargliela una volta sola, per poi dirgli solo gli ingredienti del giorno.

Il ciclo di vita: prepare, bind, step, finalize

Ogni driver SQLite in ogni linguaggio incapsula le stesse quattro chiamate della C API. Conoscerne i nomi aiuta anche se non scriverai mai in C, perché i messaggi di errore e la documentazione usano questo vocabolario:

1. sqlite3_prepare_v2: compila una stringa SQL in un handle di statement.
2. sqlite3_bind_*: inserisce i valori dei segnaposto (una funzione per tipo).
3. sqlite3_step: esegue il programma. Per SELECT, chiamala più volte per scorrere le righe. Per INSERT/UPDATE/DELETE, basta una chiamata.
4. sqlite3_finalize: libera il programma compilato quando hai finito.

Tra un'esecuzione e l'altra, sqlite3_reset riavvolge uno statement concluso così puoi ricollegare i valori ed eseguirlo di nuovo senza prepararlo da capo.

I segnaposto nell'SQL

Dentro la stringa SQL segni ogni punto in cui va un valore con un segnaposto, invece di incollarci il valore. SQLite supporta alcune forme:

-- Anonimi, posizionali:
INSERT INTO users (name, email) VALUES (?, ?);

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

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

? è il più comune nel codice a livello di driver. I segnaposto con nome (:name) si leggono meglio quando i parametri sono tanti o quando lo stesso valore compare più di una volta. Scegli uno stile per progetto e restaci fedele.

Quello che non devi fare è costruire la query concatenando stringhe:

-- NON FARLO:
"INSERT INTO users (name) VALUES ('" + user_input + "')"

È la strada verso la SQL injection, e in più vanifica il riuso del bytecode di cui leggerai tra poco.

Un esempio pratico in SQL

Per vedere il meccanismo senza un linguaggio ospite, ecco l'equivalente di prepare/bind/step usando solo le funzioni SQL che SQLite ti mette a disposizione. Crea una tabella e inserisci una riga con un segnaposto in stile parametro riempito da un letterale:

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;

In una vera applicazione non scriveresti i valori direttamente nella query: faresti prepare dell'INSERT una volta con i segnaposto ?, ?, poi bind della coppia nome ed email per ogni utente e step. Il bytecode compilato è identico per ogni chiamata; cambiano solo i valori collegati.

Riutilizzare uno statement (il guadagno di prestazioni)

Ecco lo schema che il tuo driver ti permette di scrivere. È pseudocodice, ogni linguaggio lo scrive in modo un po' diverso, ma la forma è universale:

-- preparato una volta:
INSERT INTO users (name, email) VALUES (?, ?);

-- poi, in un ciclo:
--   bind(1, name)
--   bind(2, email)
--   step()
--   reset()

La preparazione analizza e compila l'SQL una volta sola. Ogni iterazione esegue solo bytecode e copia i valori negli spazi. Per gli insert in blocco (pensa all'importazione di 100.000 righe) è molto più veloce che eseguire 100.000 statement analizzati uno per uno: spesso di un ordine di grandezza, soprattutto se racchiusi in un'unica transazione.

Un errore comune: fare il ciclo chiamando prepare dentro il ciclo. Così butti via tutto il vantaggio. Prepara fuori dal ciclo, fai bind e step dentro.

Perché è il modo sicuro

I parametri collegati non sono stringhe sostituite nell'SQL. Sono valori passati al programma bytecode tramite spazi tipizzati: spazi per interi, per testo, per blob. SQLite non li analizza mai di nuovo come SQL, quindi nessun valore può cambiare la struttura della query.

Confronta:

-- Vulnerabile. Se user_input è:  '); DROP TABLE users;--
-- la query diventa distruttiva.
"SELECT * FROM users WHERE name = '" + user_input + "'"

-- Sicura. user_input viene collegato come valore TEXT e viene
-- sempre confrontato come stringa, qualunque cosa contenga.
SELECT * FROM users WHERE name = ?;

La seconda forma è sicura anche se user_input è '); DROP TABLE users;--. SQLite cercherà diligentemente un utente il cui nome è esattamente quella (strana) stringa, non ne troverà nessuno e restituirà zero righe. Niente nella struttura della query può cambiare in base al valore.

Approfondiremo la injection in una pagina successiva, ma il messaggio è questo: i prepared statement non sono solo una difesa contro la SQL injection, sono la difesa.

Statement che restituiscono righe

Per SELECT, step restituisce una riga alla volta. Di solito il driver continua il ciclo finché non segnala "finito":

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

INSERT INTO products (name, price) VALUES
    ('Quaderno', 4.50),
    ('Penna',    1.20),
    ('Gomma',    0.80);

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

Nel codice dell'applicazione, il driver farebbe prepare di quella SELECT con un ? al posto di 2.00, collegherebbe il valore di soglia e chiamerebbe step in un ciclo, leggendo una riga per chiamata. Dopo l'ultima riga, step segnala la fine e il driver fa reset dello statement (per eseguirlo di nuovo con una nuova soglia) oppure lo chiude con finalize.

Non dimenticare finalize

Un prepared statement è una piccola allocazione dentro SQLite. Lasciarli in giro consuma memoria e, cosa più importante, mantiene un lock interno sul database che può bloccare altri writer. Ogni driver ti offre un modo per fare pulizia in automatico, come i context manager in Python, i blocchi using in C# e RAII in C++, e dovresti usarli:

• Il sqlite3 di Python esegue finalize quando il cursore viene raccolto dal garbage collector, ma un cursor.close() esplicito è più pulito.
• better-sqlite3 (Node) esegue finalize quando lo Statement viene raccolto dal garbage collector; i prepared statement di lunga durata vanno benissimo.
• In C puro chiami tu sqlite3_finalize. Dimenticarlo è un bug vero.

La regola pratica: se l'hai preparato, qualcosa deve chiuderlo con finalize.

Quando potresti non doverlo fare tu

Raramente chiamerai sqlite3_prepare_v2 direttamente. I driver di alto livello trasformano connection.execute("SELECT ... WHERE id = ?", (42,)) in prepare/bind/step/finalize al posto tuo. Capire il ciclo di vita serve perché:

• Riconoscerai cosa sta succedendo quando vedi errori come "statement is busy" o "cannot operate on a finalized statement".
• Saprai di dover tenere in cache i prepared statement di lunga durata quando inserisci dati in un ciclo stretto.
• Scriverai query parametrizzate d'istinto, anche quando una concatenazione di stringhe sembra allettante.

ORM e query builder vanno ancora oltre. Costruiscono l'SQL, gestiscono i prepared statement e ti restituiscono risultati tipizzati. Sotto, sono sempre le stesse quattro chiamate.

Prossimo passo: collegare i parametri

Finora abbiamo parlato dei segnaposto in modo astratto. Ora vedremo nel dettaglio il lato del binding: parametri posizionali e con nome, gestione dei tipi, NULL e i piccoli tranelli che saltano fuori quando inizi a passare dati reali dell'applicazione nelle query.