Transactions et injection SQL

Ce que tu vas apprendre

• Regrouper plusieurs modifications dans une transaction qui réussit en entier ou pas du tout.
• Comprendre comment fonctionne une injection SQL.
• Écrire des requêtes paramétrées pour t'en protéger.

Pourquoi des transactions

Tu veux enregistrer une recette et ses ingrédients. Cela demande plusieurs INSERT. Si le deuxième échoue, tu te retrouves avec une recette sans ingrédients.

Une transaction regroupe plusieurs commandes en un bloc indivisible : soit toutes réussissent, soit aucune n'a d'effet. C'est l'atomicité.

BEGIN, COMMIT, ROLLBACK

BEGIN;

INSERT INTO recettes (titre, temps_minutes, categorie_id)
VALUES ('Crêpes', 30, 3);

INSERT INTO ingredients (recette_id, nom, quantite, unite)
SELECT id, 'farine', 250, 'g' FROM recettes WHERE titre = 'Crêpes';

COMMIT;

BEGIN ouvre la transaction. COMMIT la valide : toutes les modifications deviennent définitives et visibles pour les autres. Tant que tu n'as pas validé, les autres connexions ne voient rien.

Pour tout annuler, remplace COMMIT par ROLLBACK. La base revient à l'état d'avant BEGIN.

Une transaction pour tester

Voici une manière sûre d'essayer une modification risquée :

BEGIN;
DELETE FROM ingredients WHERE recette_id = 2;
SELECT COUNT(*) FROM ingredients;
ROLLBACK;

Tu constates l'effet du DELETE dans ta session, puis ROLLBACK annule tout. C'est le filet de sécurité évoqué au cours 02.

L'injection SQL

Dans une application, la requête est souvent construite avec du texte saisi par un utilisateur. Imagine une recherche de recette :

const titre = req.query.titre
const sql = "SELECT * FROM recettes WHERE titre = '" + titre + "'"

Si l'utilisateur tape Tarte, tout va bien. Mais s'il tape ' OR '1'='1, la requête devient :

SELECT * FROM recettes WHERE titre = '' OR '1'='1'

La condition '1'='1' est toujours vraie : toute la table est renvoyée. Un attaquant peut aussi lire d'autres tables ou, selon les droits, supprimer des données.

C'est une injection SQL : du texte saisi par l'utilisateur est interprété comme du code SQL.

La parade : les requêtes paramétrées

On sépare la requête du texte de l'utilisateur. La requête contient des emplacements numérotés $1, $2, et les valeurs voyagent à part :

const resultat = await client.query(
  'SELECT * FROM recettes WHERE titre = $1',
  [titre]
)

Ici, client est une connexion créée avec la bibliothèque pg (node-postgres). La valeur de titre est toujours traitée comme une simple donnée, jamais comme du SQL. Même ' OR '1'='1 est cherché comme un titre de recette.

PostgreSQL propose lui-même ce mécanisme avec PREPARE :

PREPARE recette_par_titre (text) AS
  SELECT * FROM recettes WHERE titre = $1;

EXECUTE recette_par_titre('Tarte aux pommes');

Une instruction préparée n'existe que pendant la session. Dans ton code, tu n'as pas besoin de l'écrire à la main : ta bibliothèque de base de données s'en charge.

Autres bonnes pratiques

• Donne à ton application un compte de base avec les droits minimaux, pas un compte administrateur.
• Valide aussi les données côté serveur : type attendu, longueur, valeurs permises.
• Un paramètre remplace une valeur, pas un nom de table ou de colonne. Pour un tri choisi par l'utilisateur, compare-le à une liste de noms autorisés écrite dans ton code.

À retenir

• Une transaction (BEGIN ... COMMIT) fait réussir ou échouer plusieurs commandes ensemble.
• ROLLBACK annule tout ce qui a été fait depuis BEGIN.
• Hors transaction explicite, chaque commande est validée immédiatement.
• L'injection SQL survient quand du texte d'utilisateur est collé dans une requête.
• Les requêtes paramétrées ($1, $2) séparent le code SQL des données et neutralisent l'injection.