Hors sujet : bonnes pratiques et failles SQL

Écrire des requêtes propres

Une requête est lue bien plus souvent qu'elle n'est écrite. Quelques habitudes la rendent plus claire, et évitent des bugs qui n'arrivent qu'un mois plus tard.

-- difficile à relire
select * from affaires a, enqueteurs e where a.enqueteur_id=e.id and a.statut='ouverte';

-- claire
SELECT a.titre, e.nom AS enqueteur
FROM affaires AS a
JOIN enqueteurs AS e ON e.id = a.enqueteur_id
WHERE a.statut = 'ouverte'
ORDER BY a.titre;

Modifier des données sans casser

Les erreurs les plus coûteuses viennent presque toujours d'un UPDATE ou d'un DELETE lancé trop vite. Le réflexe : regarder avant d'agir.

-- 1. quelles lignes seront touchées ?
SELECT id, titre, statut FROM affaires WHERE statut = 'en cours';

-- 2. on modifie dans une transaction
BEGIN;
UPDATE affaires SET statut = 'classee' WHERE statut = 'en cours';

-- 3. le nombre de lignes modifiées est-il celui attendu ?
--    oui : COMMIT;   non : ROLLBACK;

• Toujours un WHERE sur UPDATE et DELETE (sauf si tu veux vraiment toucher toute la table) ;
• Tester sur une copie de la base avant de toucher à la production ;
• Sauvegarder avant une opération lourde, et tester la restauration : une sauvegarde jamais restaurée est une sauvegarde dont on ne sait pas si elle marche ;
• Privilégier la suppression logique quand l'historique compte : une colonne supprime ou date_suppression plutôt qu'un DELETE définitif.

Bien concevoir ses tables

Une base mal conçue coûte cher : chaque requête devient un contournement. Quelques règles de conception évitent l'essentiel des problèmes.

Garder des requêtes rapides

Sur 100 lignes, tout est rapide. Sur 10 millions, une requête mal écrite fait tomber un site. Un index est comme l'index d'un livre : il permet de retrouver une valeur sans lire toute la table.

-- on cherche souvent les suspects par nom : on l'indexe
CREATE INDEX idx_suspects_nom ON suspects (nom);

-- EXPLAIN montre comment la base compte exécuter la requête
EXPLAIN SELECT * FROM suspects WHERE nom = 'Marechal';

• Indexer les colonnes qui servent souvent dans WHERE, JOIN et ORDER BY ;
• Ne pas abuser : chaque index ralentit les INSERT, UPDATE et DELETE, et prend de la place ;
• Ne pas envelopper la colonne dans une fonction : WHERE UPPER(nom) = 'DUBOIS' ou WHERE YEAR(date) = 2024 empêchent souvent d'utiliser l'index ;
• Se méfier de LIKE '%mot' : un joker au début oblige à lire toute la table ;
• Limiter les résultats (LIMIT) plutôt que tout ramener pour filtrer ensuite dans le programme.

La faille n°1 : l'injection SQL

L'injection SQL arrive quand un programme fabrique une requête en collant du texte venu de l'utilisateur. Si ce texte contient du SQL, la base l'exécute comme s'il faisait partie de la requête. Cette faille est vieille de plus de vingt ans et figure toujours parmi les plus répandues.

Imagine un formulaire de connexion. Le programme construit la requête en collant les deux champs saisis, entre apostrophes.

-- l'utilisateur tape : vasseur / secret
SELECT * FROM utilisateurs
WHERE nom = 'vasseur' AND mdp = 'secret';

Mais si l'utilisateur tape ' OR '1'='1 comme mot de passe, l'apostrophe ferme la chaîne et la suite devient du SQL.

-- l'utilisateur tape : vasseur / ' OR '1'='1
SELECT * FROM utilisateurs
WHERE nom = 'vasseur' AND mdp = '' OR '1'='1';
-- '1'='1' est toujours vrai : la condition passe, la connexion est acceptée

Pire : en ajoutant des instructions, un attaquant peut lire toutes les tables (avec UNION SELECT), modifier des données, voire supprimer des tables si le compte de l'application en a le droit.

-- l'utilisateur tape comme nom : x'; DROP TABLE utilisateurs; --
SELECT * FROM utilisateurs WHERE nom = 'x'; DROP TABLE utilisateurs; --';

Se protéger de l'injection

La parade est simple et fiable : les requêtes préparées (ou paramétrées). On envoie à la base la requête avec des trous, puis les valeurs séparément. Les valeurs ne sont jamais interprétées comme du SQL, quel que soit leur contenu.

SELECT * FROM utilisateurs
WHERE nom = ? AND mdp_hash = ?;

-- les valeurs sont transmises à part par le programme :
-- ici, ' OR '1'='1 est simplement un nom bizarre, sans effet

• Toujours utiliser les requêtes préparées de ton langage et de ta bibliothèque, jamais l'assemblage de texte (+, .format, interpolation de chaîne) ;
• Valider les saisies (type, longueur, format), en complément et non à la place ;
• Limiter les droits du compte de l'application (voir le chapitre 9) : même en cas de faille, il ne pourra pas faire de DROP TABLE ;
• Ne jamais afficher les messages d'erreur SQL bruts à l'utilisateur : ils révèlent le nom des tables et des colonnes ;
• Utiliser un ORM ou un constructeur de requêtes, qui préparent les requêtes par défaut (mais restent vulnérables si on y colle du texte brut).

Les autres failles classiques

L'injection n'est pas la seule porte d'entrée. Voici les erreurs qui reviennent le plus souvent autour des bases de données.

Les pièges du langage lui-même

Certaines surprises ne sont pas des failles de sécurité, mais des comportements de SQL qui donnent un résultat faux sans aucune erreur. Ce sont les plus sournois.

NULL n'est égal à rien, pas même à lui-même. Comparer avec = NULL ne renvoie jamais de ligne : il faut écrire IS NULL.

-- ne renvoie JAMAIS rien, sans erreur
SELECT * FROM suspects WHERE alibi = NULL;

-- correct
SELECT * FROM suspects WHERE alibi IS NULL;

NOT IN et NULL font mauvais ménage. Si la liste contient un seul NULL, NOT IN ne renvoie plus aucune ligne.

-- si une affaire a un enqueteur_id NULL, le résultat est vide !
SELECT nom FROM enqueteurs
WHERE id NOT IN (SELECT enqueteur_id FROM affaires);

-- plus sûr : NOT EXISTS
SELECT nom FROM enqueteurs e
WHERE NOT EXISTS (SELECT 1 FROM affaires a WHERE a.enqueteur_id = e.id);

• Les agrégats ignorent NULL : AVG ne compte pas les lignes vides, la moyenne peut donc surprendre ; COUNT(*) compte les lignes, COUNT(colonne) seulement les valeurs renseignées ;
• La division entière : sur certains SGBD, 7 / 2 vaut 3, pas 3.5 ; on convertit un des deux nombres en décimal ;
• Les jointures qui multiplient les lignes : joindre deux tables liées par une relation « plusieurs » fausse un SUM ou un COUNT ; COUNT(DISTINCT ...) ou une sous-requête règle le problème ;
• Les comparaisons de texte : la casse et les accents comptent ou non selon le SGBD ('Dubois' = 'dubois' ?) ;
• Le WHERE filtre avant le regroupement, le HAVING après : mettre une condition d'agrégat dans le WHERE provoque une erreur.

En résumé

• Écris des requêtes lisibles : colonnes nommées, jointures explicites, alias parlants ;
• Regarde avant de modifier, travaille en transaction, sauvegarde et teste la restauration ;
• Conçois des tables propres : clés, contraintes, bons types, une information à un seul endroit ;
• Indexe ce qui est souvent cherché, sans abuser ;
• Ne colle jamais de saisie dans une requête : requêtes préparées, toujours ;
• Donne à chaque application le moindre privilège, hache les mots de passe, cache les erreurs ;
• Méfie-toi de NULL : il est la source de la plupart des résultats faux mais silencieux.