Requêtes préparées SQLite : prepare, bind, step, finalize

Ce qu'est réellement une requête préparée

Quand vous passez une chaîne SQL à SQLite, beaucoup de choses se passent avant qu'une seule ligne ne bouge : le moteur doit la tokeniser, l'analyser, vérifier que les tables et colonnes existent, planifier l'exécution, puis compiler ce plan en bytecode pour la machine virtuelle de SQLite. Ce n'est qu'après tout ça que la requête s'exécute vraiment.

Une requête préparée (ou prepared statement), c'est ce que vous obtenez quand vous vous arrêtez à l'étape « compilé en bytecode » et que vous conservez ce résultat. Le programme compilé contient des emplacements — des placeholders — où les vraies valeurs seront injectées plus tard. Vous pouvez exécuter ce même programme compilé de nombreuses fois avec des valeurs différentes, et le faire en toute sécurité même quand ces valeurs viennent d'une source non fiable.

Pour l'image : c'est la différence entre tendre une recette à quelqu'un pour qu'il la relise à voix haute à chaque fois qu'il cuisine, et lui apprendre la recette une bonne fois pour toutes en se contentant de lui annoncer les ingrédients le jour J.

Le cycle de vie : prepare, bind, step, finalize

Chaque driver SQLite, dans n'importe quel langage, encapsule les mêmes quatre appels de l'API C. Connaître ces noms est utile même si vous n'écrirez jamais de C, parce que les messages d'erreur et la documentation reprennent ce vocabulaire :

1. sqlite3_prepare_v2 — compile une chaîne SQL en un handle de statement.
2. sqlite3_bind_* — remplit les valeurs des placeholders (une fonction par type).
3. sqlite3_step — exécute le programme. Pour un SELECT, on l'appelle en boucle pour parcourir les lignes. Pour un INSERT/UPDATE/DELETE, un seul appel suffit.
4. sqlite3_finalize — libère le programme compilé quand vous avez fini.

Entre deux étapes, sqlite3_reset rembobine un statement déjà exécuté pour que vous puissiez re-binder et relancer sans tout recompiler.

Les placeholders dans le SQL

Dans la chaîne SQL, chaque emplacement de valeur est marqué par un placeholder, plutôt que d'injecter directement la valeur dans la chaîne. SQLite accepte plusieurs syntaxes :

-- Anonyme, positionnel :
INSERT INTO users (name, email) VALUES (?, ?);

-- Numéroté :
INSERT INTO users (name, email) VALUES (?1, ?2);

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

? reste le placeholder le plus courant dans le code qui touche directement au driver. Les placeholders nommés (:name) deviennent plus lisibles dès qu'il y a plusieurs paramètres ou qu'une même valeur revient à différents endroits. Choisissez un style par projet et tenez-vous-y.

Ce qu'il ne faut surtout pas faire, c'est construire la requête par concaténation de chaînes :

-- NE FAITES PAS CECI :
"INSERT INTO users (name) VALUES ('" + user_input + "')"

C'est la porte ouverte à l'injection SQL, et en prime ça gâche la réutilisation du bytecode dont on va parler juste après.

Un exemple concret en SQL

Pour bien voir la mécanique sans passer par un langage hôte, voici l'équivalent de prepare/bind/step en n'utilisant que ce que SQLite propose côté SQL. On crée une table puis on insère une ligne via un placeholder rempli par une valeur littérale :

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;

Dans une vraie application, vous n'écrivez jamais les valeurs en dur dans la requête. Tu prépares (prepare) votre INSERT une seule fois avec des placeholders ?, ?, puis pour chaque utilisateur vous faites un bind du nom et de l'email avant d'appeler step. Le bytecode compilé reste exactement le même à chaque appel : seules les valeurs liées changent.

Réutiliser une requête préparée (le vrai gain de perf)

Voici le pattern que votre driver te laisse écrire. C'est du pseudo-code — chaque langage a sa propre syntaxe — mais la structure est toujours la même :

-- préparé une seule fois :
INSERT INTO users (name, email) VALUES (?, ?);

-- puis, dans une boucle :
--   bind(1, name)
--   bind(2, email)
--   step()
--   reset()

La préparation analyse et compile le SQL une seule fois. Chaque itération ne fait ensuite qu'exécuter du bytecode et copier des valeurs dans des emplacements typés. Pour des insertions en masse (imaginez : importer 100 000 lignes), c'est nettement plus rapide que d'exécuter 100 000 requêtes parsées séparément — souvent d'un ordre de grandeur, surtout si on englobe le tout dans une seule transaction.

Le piège classique : on écrit une boucle et on appelle prepare à l'intérieur. Du coup, tout le bénéfice part à la poubelle. La règle est simple : prepare en dehors de la boucle, bind et step à l'intérieur.

Pourquoi cette approche est la plus sûre

Les paramètres liés ne sont pas des chaînes injectées dans le SQL. Ce sont des valeurs transmises au programme bytecode via des emplacements typés — emplacements entier, texte, blob. SQLite ne les réinterprète jamais comme du SQL, donc aucune valeur ne peut modifier la structure de la requête.

Comparez :

-- Vulnérable. Si user_input vaut :  '); DROP TABLE users;--
-- la requête devient destructrice.
"SELECT * FROM users WHERE name = '" + user_input + "'"

-- Sûr. user_input est lié comme une valeur TEXT et n'est jamais
-- comparé qu'en tant que chaîne, quel que soit son contenu.
SELECT * FROM users WHERE name = ?;

La seconde version reste sûre même si user_input vaut '); DROP TABLE users;--. SQLite va sagement chercher un utilisateur dont le nom correspond exactement à cette chaîne (bizarre, certes), n'en trouvera aucun, et renverra zéro ligne. Aucun élément de la structure de la requête ne peut être altéré par la valeur passée.

On reviendra plus en détail sur l'injection SQL dans un autre document, mais retenez ceci : les requêtes préparées ne sont pas une protection parmi d'autres contre l'injection SQL — c'est la protection.

Les requêtes qui renvoient des lignes

Pour un SELECT, step renvoie une ligne à la fois. Le driver boucle généralement jusqu'à recevoir le signal « terminé » :

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

INSERT INTO products (name, price) VALUES
    ('Cahier',   4.50),
    ('Stylo',    1.20),
    ('Gomme',    0.80);

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

Côté code applicatif, le driver va prepare ce SELECT en mettant un ? à la place de 2.00, lier la valeur du seuil, puis appeler step dans une boucle pour lire une ligne à chaque appel. Une fois la dernière ligne atteinte, step signale la fin, et le driver peut alors soit reset la requête (pour la rejouer avec un nouveau seuil), soit la finalize.

Ne pas oublier le finalize

Une requête préparée occupe un petit espace mémoire à l'intérieur de SQLite. Si vous en laissez fuir, ça consomme de la RAM, mais surtout ça maintient un verrou interne sur la base qui peut bloquer d'autres écrivains. Chaque driver propose un moyen de nettoyer automatiquement — gestionnaires de contexte en Python, blocs using en C#, RAII en C++ — et il faut s'en servir :

• Le module sqlite3 de Python finalise au moment où le curseur est ramassé par le GC, mais un cursor.close() explicite reste plus propre.
• better-sqlite3 (Node) finalise quand l'objet Statement passe au GC ; garder des requêtes préparées sur la durée ne pose pas de souci.
• En C pur, c'est à vous d'appeler sqlite3_finalize. L'oublier, c'est un vrai bug.

La règle à retenir : si vous l'avez préparée, quelque chose doit la finaliser.

Quand vous n'aurez pas à le faire vous-même

Vous appellerez rarement sqlite3_prepare_v2 directement. Les drivers de haut niveau transforment connection.execute("SELECT ... WHERE id = ?", (42,)) en prepare/bind/step/finalize à votre place. L'intérêt de comprendre ce cycle de vie, c'est :

• Vous identifierez tout de suite ce qui se passe quand vous tomberez sur une erreur du type « statement is busy » ou « cannot operate on a finalized statement ».
• Vous saurez qu'il faut mettre en cache les requêtes préparées qui durent, surtout pour des insertions en masse dans une boucle serrée.
• Vous écrirez des requêtes paramétrées par réflexe, même quand une concaténation de chaînes paraît tentante.

Les ORM et query builders poussent l'idée encore plus loin. Ils construisent le SQL, gèrent les requêtes préparées et vous renvoient des résultats typés. En dessous, ce sont toujours les quatre mêmes appels.

La suite : le binding des paramètres

On a parlé des placeholders de façon abstraite. Dans la suite, on va se pencher en détail sur le binding — paramètres positionnels ou nommés, gestion des types, NULL, et les petits pièges qui apparaissent dès que vous commencez à passer de vraies données applicatives à vos requêtes.