Accueil/Informatique & Développement /Chapitre 09 /Exercices corrigés

Bases de données & SQL — exercices

Le projet E6 « Gestion des présences » déroulé de bout en bout — conception, requêtes, puis mise en sécurité.

🎯

Le réflexe avant toute requête destructive

Écrivez-la d'abord en SELECT pour voir combien de lignes elle touche. Une fois le compte vérifié, remplacez par UPDATE ou DELETE. Cette habitude évite l'accident dont personne ne se remet.

01Concevoir le MLD (projet E6 · étape 1)

1Trois tables et une relation Moyen

Un établissement veut gérer ses étudiants et leurs présences. L'administration se connecte à l'application, consulte les étudiants et enregistre les présences/absences.

Proposez le MLD : les tables, leurs colonnes, les clés primaires et étrangères.

✅ Correction

MLD

administrateurs (id, login, mot_de_passe)
etudiants       (id, nom, prenom, classe)
presences       (id, #id_etudiant, date_presence, statut)

Le soulignement marque la clé primaire, le # la clé étrangère.

🧰 Pourquoi la clé étrangère est dans presences

La relation est 1–N : un étudiant a plusieurs présences, une présence appartient à un seul étudiant. La clé va donc du côté N — dans presences.

L'inverse serait absurde : il faudrait une ligne étudiant par jour de présence, et le nom serait recopié des centaines de fois.

⚠️ Pourquoi administrateurs n'est reliée à rien

C'est normal et voulu : les administrateurs servent uniquement à l'authentification. Aucune donnée métier ne dépend de « quel admin a saisi quoi ».

Si l'énoncé demandait de tracer qui a enregistré chaque présence, il faudrait alors ajouter #id_admin dans presences — une seconde relation 1–N.

02Créer les tables

2Le script complet Moyen

Écrivez le script SQL créant la base gestion_presence et ses trois tables, avec la contrainte de clé étrangère.

✅ Correction

sql — bdd.sql

CREATE DATABASE IF NOT EXISTS gestion_presence;
USE gestion_presence;

CREATE TABLE administrateurs (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    login        VARCHAR(50)  NOT NULL UNIQUE,
    mot_de_passe VARCHAR(255) NOT NULL
);

CREATE TABLE etudiants (
    id     INT AUTO_INCREMENT PRIMARY KEY,
    nom    VARCHAR(50) NOT NULL,
    prenom VARCHAR(50) NOT NULL,
    classe VARCHAR(50) NOT NULL
);

CREATE TABLE presences (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    id_etudiant   INT NOT NULL,
    date_presence DATE NOT NULL,
    statut        VARCHAR(20) NOT NULL,
    FOREIGN KEY (id_etudiant) REFERENCES etudiants(id)
);

🧰 Deux détails qui font la différence

`UNIQUE` sur `login`	empêche deux administrateurs de porter le même identifiant — sinon la connexion deviendrait ambiguë
`VARCHAR(255)` pour le mot de passe	parce qu'on y stockera une empreinte (`password_hash`) de 60 caractères, pas le mot de passe

⚠️ L'ordre de création n'est pas libre

presences référence etudiants : la table référencée doit exister d'abord. Inverser les deux blocs provoque :

erreur

Can't create table 'presences' (errno: 150 "Foreign key constraint is incorrectly formed")

Même règle pour la suppression, en sens inverse : on supprime presences avant etudiants.

03Choisir les bons types (TP7)

3Repérer l'erreur de type Moyen

Voici une table du TP7. Quelque chose y cloche — quoi, et quelles conséquences ?

sql

CREATE TABLE employes (
    id      INT PRIMARY KEY AUTO_INCREMENT,
    nom     VARCHAR(50),
    poste   VARCHAR(50),
    salaire VARCHAR(50)
);

✅ Correction

salaire est déclaré VARCHAR : un nombre stocké comme du texte. Trois conséquences, toutes silencieuses.

Problème	Ce qui se passe
Le tri est faux	`ORDER BY salaire` trie alphabétiquement : `"10000"` arrive avant `"9000"`, car « 1 » précède « 9 »
Les calculs échouent	`SUM()` et `AVG()` donnent des résultats aberrants ou nuls
Les comparaisons mentent	`WHERE salaire > 2000` compare du texte, pas des nombres

sql — corrigé

CREATE TABLE employes (
    id      INT PRIMARY KEY AUTO_INCREMENT,
    nom     VARCHAR(50) NOT NULL,
    poste   VARCHAR(50) NOT NULL,
    salaire DECIMAL(10,2) NOT NULL
);

-- ou, si la table existe déjà :
ALTER TABLE employes MODIFY salaire DECIMAL(10,2);

🧰 DECIMAL et non FLOAT pour l'argent

DECIMAL(10,2) = 10 chiffres au total, dont 2 après la virgule. Il stocke la valeur exacte.

FLOAT stocke une approximation : on peut y obtenir 0.1 + 0.2 = 0.30000000000000004. Acceptable pour une température, jamais pour un salaire ou un prix.

⚠️ Le même piège qu'en PHP et en JavaScript

C'est exactement le problème de .value en JavaScript et d'input() en Python : un nombre traité comme du texte. Il ne plante pas — il donne des résultats faux, ce qui est bien pire.

04Interroger la base

4Huit requêtes Moyen

Sur la base gestion_presence, écrivez les requêtes :

tous les étudiants, triés par nom ;
les étudiants de la classe « BTS CIEL 1 » ;
la barre de recherche : les étudiants dont le nom, le prénom ou la classe contient « dup » ;
ajouter l'étudiante Alice Dupont en BTS CIEL 1 ;
changer la classe de l'étudiant n° 3 ;
supprimer l'étudiant n° 5 ;
les présences du 13 septembre 2026 ;
les 10 dernières présences enregistrées.

✅ Correction

sql

-- 1
SELECT * FROM etudiants ORDER BY nom ASC;

-- 2
SELECT * FROM etudiants WHERE classe = 'BTS CIEL 1';

-- 3  (la barre de recherche du projet)
SELECT * FROM etudiants
WHERE nom    LIKE '%dup%'
   OR prenom LIKE '%dup%'
   OR classe LIKE '%dup%';

-- 4
INSERT INTO etudiants (nom, prenom, classe)
VALUES ('Dupont', 'Alice', 'BTS CIEL 1');

-- 5
UPDATE etudiants SET classe = 'BTS CIEL 2' WHERE id = 3;

-- 6
DELETE FROM etudiants WHERE id = 5;

-- 7
SELECT * FROM presences WHERE date_presence = '2026-09-13';

-- 8
SELECT * FROM presences ORDER BY date_presence DESC LIMIT 10;

⚠️ La requête 6 peut être refusée — et c'est une bonne nouvelle

erreur

Cannot delete or update a parent row:
a foreign key constraint fails (`presences`, CONSTRAINT ... FOREIGN KEY (`id_etudiant`))

Si l'étudiant n° 5 a des présences enregistrées, la clé étrangère empêche sa suppression. Le SGBD vous protège de laisser des présences orphelines.

Deux solutions selon ce que vous voulez vraiment :

sql

-- a. supprimer d'abord les présences, puis l'étudiant
DELETE FROM presences WHERE id_etudiant = 5;
DELETE FROM etudiants WHERE id = 5;

-- b. ou décider une fois pour toutes, à la création de la table :
FOREIGN KEY (id_etudiant) REFERENCES etudiants(id) ON DELETE CASCADE

ON DELETE CASCADE supprime automatiquement les présences avec l'étudiant. Pratique — mais à n'activer qu'en connaissance de cause : la suppression devient silencieuse et irréversible.

🧰 La date s'écrit toujours AAAA-MM-JJ

'2026-09-13', jamais '13/09/2026'. C'est le format SQL standard, et c'est aussi ce qui permet de trier correctement : l'ordre alphabétique de ce format est l'ordre chronologique.

05L'historique des présences (étape 7)

5Relier deux tables Exigeant

La page d'historique doit afficher, pour chaque présence : nom, prénom, classe, date, statut. Or la table presences ne contient qu'un id_etudiant.

Écrivez la requête de l'historique complet, du plus récent au plus ancien.
Écrivez la requête donnant tous les étudiants avec leur nombre d'absences — y compris ceux qui n'en ont aucune.

✅ Correction

sql — 1. l'historique

SELECT e.nom, e.prenom, e.classe, p.date_presence, p.statut
FROM presences p
INNER JOIN etudiants e ON p.id_etudiant = e.id
ORDER BY p.date_presence DESC;

sql — 2. les absences par étudiant

SELECT e.nom, e.prenom, COUNT(p.id) AS nb_absences
FROM etudiants e
LEFT JOIN presences p
       ON p.id_etudiant = e.id AND p.statut = 'absent'
GROUP BY e.id, e.nom, e.prenom
ORDER BY nb_absences DESC;

🧰 Pourquoi LEFT et non INNER à la question 2

Avec INNER JOIN, les étudiants sans aucune absence n'apparaîtraient pas du tout. Le tableau afficherait uniquement les absentéistes — et passerait pour complet.

LEFT JOIN garde tous les étudiants ; ceux sans absence obtiennent 0. C'est bien ce que demande la question.

⚠️ La condition statut = 'absent' va dans le ON, pas dans le WHERE

sql — faux

LEFT JOIN presences p ON p.id_etudiant = e.id
WHERE p.statut = 'absent'        -- ✘ annule l'effet du LEFT JOIN

Un LEFT JOIN produit des lignes où les colonnes de droite valent NULL (les étudiants sans absence). Le WHERE s'applique après la jointure et élimine ces NULL — on retombe exactement sur le comportement d'un INNER JOIN.

Placée dans le ON, la condition filtre pendant la jointure : les étudiants sans absence sont conservés, avec un compte de 0.

🧰 COUNT(p.id) et non COUNT(*)

Pour un étudiant sans absence, le LEFT JOIN crée une ligne remplie de NULL. COUNT(*) compterait cette ligne et renverrait 1 au lieu de 0.

COUNT(p.id) ne compte que les valeurs non nulles : il renvoie bien 0. Un détail d'une seule expression, et tout le tableau change de sens.

06Le tableau de bord (étape 4)

6Trois compteurs Moyen

Le tableau de bord doit afficher le nombre total d'étudiants, de présences et d'absences. Écrivez les requêtes SQL, puis le code PHP qui les affiche.

✅ Correction

sql

SELECT COUNT(*) FROM etudiants;
SELECT COUNT(*) FROM presences WHERE statut = 'present';
SELECT COUNT(*) FROM presences WHERE statut = 'absent';

-- ou les deux derniers en UNE seule requête :
SELECT statut, COUNT(*) AS nombre
FROM presences
GROUP BY statut;

php — dashboard.php

<?php
session_start();
if (!isset($_SESSION['admin_connecte'])) {
    header("Location: login.php");
    exit();
}
require 'db.php';

$nbEtudiants = $pdo->query("SELECT COUNT(*) FROM etudiants")->fetchColumn();
$nbPresents  = $pdo->query("SELECT COUNT(*) FROM presences
                            WHERE statut = 'present'")->fetchColumn();
$nbAbsents   = $pdo->query("SELECT COUNT(*) FROM presences
                            WHERE statut = 'absent'")->fetchColumn();
?>

<div class="row">
  <div class="col-12 col-md-4">
    <div class="card text-center"><div class="card-body">
      <h5 class="card-title">Étudiants</h5>
      <p class="display-6"><?php echo $nbEtudiants; ?></p>
    </div></div>
  </div>
  <!-- … présences, absences … -->
</div>

🧰 fetchColumn() pour une valeur unique

`fetchColumn()`	renvoie une seule valeur — parfait pour un `COUNT()`
`fetch()`	une ligne complète
`fetchAll()`	toutes les lignes d'un coup

Sans fetchColumn(), il faudrait écrire $res->fetch()[0] — plus verbeux et moins clair.

Remarquez aussi que ces trois requêtes ne contiennent aucune variable : query() est donc légitime ici. Dès qu'une variable entre en jeu, il faudra prepare().

07Sécuriser le projet

7Réparer les trois failles Exigeant

Voici trois extraits réels du projet « Gestion des présences ». Identifiez la faille de chacun et corrigez-la.

php — A. login.php

$login = $_POST['login'];
$mdp   = $_POST['mot_de_passe'];
$sql = "SELECT * FROM administrateurs
        WHERE login = '$login' AND mot_de_passe = '$mdp'";
$resultat = $connexion->query($sql);

php — B. ajouter_etudiant.php

$nom = htmlspecialchars($_POST['nom']);   // « on sécurise comme vu en cours »
$sql = "INSERT INTO etudiants (nom, prenom, classe)
        VALUES ('$nom', '$prenom', '$classe')";
$connexion->query($sql);

sql — C. bdd.sql

INSERT INTO administrateurs (login, mot_de_passe)
VALUES ('admin', 'admin');

✅ Correction

A — Injection SQL : contournement total de l'authentification

En saisissant ' OR '1'='1 comme identifiant, la requête devient :

sql

SELECT * FROM administrateurs WHERE login = '' OR '1'='1' AND mot_de_passe = ''

La condition est vraie, une ligne revient, l'attaquant est administrateur — sans connaître le moindre mot de passe.

php — corrigé

$req = $pdo->prepare("SELECT * FROM administrateurs WHERE login = :login");
$req->execute([':login' => $_POST['login']]);
$admin = $req->fetch();

if ($admin && password_verify($_POST['mot_de_passe'], $admin['mot_de_passe'])) {
    $_SESSION['admin_connecte'] = true;
    $_SESSION['login'] = $admin['login'];
    header("Location: dashboard.php");
    exit();
}
$erreur = "Identifiants incorrects.";

Notez qu'on ne cherche plus le mot de passe dans la requête : on récupère l'administrateur par son login, puis on vérifie l'empreinte en PHP.

B — `htmlspecialchars()` n'est pas une protection SQL

Fonction	Protège de	À quel moment
`htmlspecialchars()`	XSS	à l'affichage
`prepare()`	injection SQL	à l'envoi en base

Pire : appliquée avant l'insertion, elle corrompt les données. « O'Brien » est stocké O'Brien et ressortira ainsi partout.

php — corrigé

// à l'ÉCRITURE : on prépare, on ne transforme pas
$req = $pdo->prepare("INSERT INTO etudiants (nom, prenom, classe)
                      VALUES (:nom, :prenom, :classe)");
$req->execute([
    ':nom'    => trim($_POST['nom']),
    ':prenom' => trim($_POST['prenom']),
    ':classe' => trim($_POST['classe'])
]);

// à la LECTURE, dans etudiants.php : c'est là qu'on échappe
echo htmlspecialchars($ligne['nom']);

C — Mot de passe stocké en clair

Quiconque ouvre phpMyAdmin ou récupère une sauvegarde lit tous les mots de passe.

php — générer l'empreinte une fois

echo password_hash('admin', PASSWORD_DEFAULT);
// $2y$10$e0MYzXyjpJS7Pd0RVvHwHe1HlcS7.dV8rXk...

sql — et l'insérer

INSERT INTO administrateurs (login, mot_de_passe)
VALUES ('admin', '$2y$10$e0MYzXyjpJS7Pd0RVvHwHe1HlcS7.dV8rXk...');

La colonne était déjà déclarée VARCHAR(255) : la structure prévoyait le hachage, il ne manquait que de s'en servir.

🌍 Ce que ces trois corrections valent à l'examen E6

Un projet fonctionnel qui laisse passer ' OR '1'='1 perd beaucoup plus de points qu'un projet un peu moins complet mais sécurisé. La sécurité fait partie des critères d'évaluation — et l'injection SQL est le premier test que fait un correcteur.

La bonne nouvelle : les trois corrections tiennent en une vingtaine de lignes. Votre conception de base (tables, clés, PDO, sessions) était déjà juste.

🧰 La checklist de sécurité d'un projet PHP/MySQL

Toute requête contenant une variable → prepare() + execute().
Mots de passe → password_hash() / password_verify().
Chaque page privée → session_start() + garde + exit().
Tout affichage de donnée utilisateur → htmlspecialchars().
PDO → utf8mb4 et ERRMODE_EXCEPTION.

01Concevoir le MLD (projet E6 · étape 1)

1Trois tables et une relation Moyen

02Créer les tables

2Le script complet Moyen

03Choisir les bons types (TP7)

3Repérer l'erreur de type Moyen

04Interroger la base

4Huit requêtes Moyen

05L'historique des présences (étape 7)

5Relier deux tables Exigeant

06Le tableau de bord (étape 4)

6Trois compteurs Moyen

07Sécuriser le projet

7Réparer les trois failles Exigeant

A — Injection SQL : contournement total de l'authentification

B — htmlspecialchars() n'est pas une protection SQL

C — Mot de passe stocké en clair

B — `htmlspecialchars()` n'est pas une protection SQL