Concevoir et sécuriser

Le problème : avant de lire, il faut construire (sans laisser la porte ouverte)

Jusqu'ici, les tables existaient déjà. Mais c'est vous qui les créez. Et le jour où votre site connecte ces tables à un formulaire, une faille béante peut s'ouvrir : un visiteur mal intentionné peut, via une simple zone de texte, lire ou détruire toute votre base. Ça s'appelle l'injection SQL, et c'est encore aujourd'hui l'une des failles les plus exploitées du web.

Cette leçon couvre les deux faces : concevoir une table propre, puis la sécuriser quand des données utilisateur entrent en jeu.

CREATE TABLE : types, clé primaire, clé étrangère

On définit une table en listant ses colonnes, chacune avec un type :

CREATE TABLE clients (
    id         INT          PRIMARY KEY AUTO_INCREMENT,
    nom        VARCHAR(100) NOT NULL,
    email      VARCHAR(150) NOT NULL UNIQUE,
    ville      VARCHAR(80),
    inscrit_le DATE         DEFAULT (CURRENT_DATE)
);

Les types les plus courants : INT (entier), DECIMAL(8,2) (nombre à décimales, idéal pour un prix), VARCHAR(n) (texte court), TEXT (texte long), DATE et DATETIME. Les contraintes NOT NULL, UNIQUE et DEFAULT protègent la cohérence des données dès l'écriture.

Une table reliée déclare sa clé étrangère :

CREATE TABLE commandes (
    id        INT     PRIMARY KEY AUTO_INCREMENT,
    client_id INT     NOT NULL,
    montant   DECIMAL(8,2) NOT NULL,
    FOREIGN KEY (client_id) REFERENCES clients(id)
);

ALTER TABLE : faire évoluer sans tout casser

Le schéma du premier jour ne survit jamais. Trois semaines après le lancement, le client veut stocker le numéro de téléphone. Pas question de tout refaire : ALTER TABLE modifie une table existante sans toucher aux données.

ALTER TABLE clients ADD COLUMN telephone TEXT;

La colonne s'ajoute à droite, toutes les lignes existantes ont NULL dans ce champ jusqu'à la prochaine mise à jour. Vous pouvez aussi renommer une colonne (RENAME COLUMN) ou en changer le type (MODIFY COLUMN en MySQL, ALTER COLUMN en PostgreSQL).

Testez ALTER TABLE : créez une table, insérez une ligne, ajoutez une colonne, puis relisez. Observez que la colonne ajoutée contient NULL sur la ligne existante.

CREATE TABLE clients (id INTEGER PRIMARY KEY, nom TEXT);
INSERT INTO clients VALUES (1, 'Alice Durand');

ALTER TABLE clients ADD COLUMN telephone TEXT;

SELECT * FROM clients;

L'index : l'annuaire de ta table

Sans index, chercher un client par ville oblige la base à lire chaque ligne de la table, une par une, jusqu'à la fin. C'est le scan complet : acceptable sur 100 lignes, catastrophique sur un million.

Un index crée une structure séparée et triée sur la colonne, comme un annuaire alphabétique. La base y saute directement à la bonne entrée au lieu de tout parcourir.

CREATE INDEX idx_clients_ville ON clients(ville);

Quand créer un index ? Sur les colonnes qui apparaissent fréquemment dans les WHERE ou les JOIN. Si vous filtrez souvent par ville, un index sur ville accélère toutes ces requêtes d'un coup.

L'injection SQL : la faille qui vide votre base

Imaginez ce code PHP qui cherche un client par email, en collant directement la saisie de l'utilisateur dans la requête :

// CODE VULNÉRABLE : NE JAMAIS FAIRE ÇA
$email = $_POST['email'];
$sql = "SELECT * FROM clients WHERE email = '$email'";
$resultat = $pdo->query($sql);

Si l'utilisateur tape un email normal, ça marche. Mais s'il saisit :

' OR '1'='1

La requête envoyée devient :

SELECT * FROM clients WHERE email = '' OR '1'='1'

'1'='1' est toujours vrai : la requête renvoie tous les clients. Avec une saisie plus agressive ('; DROP TABLE clients; --), l'attaquant peut carrément supprimer la table.

Le remède : les requêtes préparées

La solution s'appelle requête préparée (prepared statement). Le principe : on envoie d'abord la structure de la requête avec des placeholders, puis les valeurs séparément. La base traite alors la saisie comme une simple donnée, jamais comme du code SQL.

// CODE SÉCURISÉ : requête préparée (PDO)
$email = $_POST['email'];
$stmt = $pdo->prepare("SELECT * FROM clients WHERE email = :email");
$stmt->execute(['email' => $email]);
$resultat = $stmt->fetchAll();

Ici, :email est un placeholder. Même si l'utilisateur tape ' OR '1'='1, cette chaîne est cherchée telle quelle comme valeur d'email : elle ne devient jamais du code. La faille disparaît.

Récapitulatif : tout le SQL en main

C'est la dernière leçon du cours. Vous êtes parti de zéro et vous savez désormais lire, filtrer, agréger, relier, modifier et concevoir une base. Le meilleur moyen de l'ancrer n'est pas de relire : c'est de vérifier, de tête, que chaque brique répond présent. Sans remonter dans les leçons, sauriez-vous écrire :

• Lire (leçon 2, SELECT) : une requête qui sort le nom et l'email de tous les clients ?
• Filtrer et trier (leçon 3, WHERE / ORDER BY) : les clients de Paris, du plus récent inscrit au plus ancien ?
• Agréger (leçon 4, GROUP BY / HAVING) : le total commandé par client, en ne gardant que ceux qui dépassent 100 € ?
• Relier (leçon 5, JOIN) : le nom du client à côté du montant de chacune de ses commandes ?
• Modifier (leçon 6, INSERT / UPDATE / DELETE) : insérer un client, corriger son email, puis supprimer une commande ?
• Concevoir et sécuriser (cette leçon, CREATE TABLE / ALTER TABLE / index / requête préparée) : créer la table commandes avec sa clé étrangère, ajouter une colonne plus tard sans perdre les données, créer un index sur ville, puis chercher un client par email sans risquer l'injection ?

Chaque « oui, je saurais » est une brique solide. Pour chaque « euh… », revenez à la leçon correspondante : c'est exactement là qu'un quart d'heure de relecture vaut le plus. Et la règle qui les surplombe toutes : dès qu'une donnée vient de l'extérieur, requête préparée, jamais de concaténation.