Ficha de revisão: Sécurité et gestion des requêtes SQL

📋 Plan du Cours

1. Requêtes préparées PDO et anti injection SQL
2. Hachage bcrypt et vérification password_verify
3. Protection XSS et en-têtes de sécurité
4. Sessions sécurisées et déconnexion complète
5. Droits SQL GRANT REVOKE et moindre privilège
6. Triggers MySQL et journalisation des actions
7. Vues SQL avec WITH CHECK OPTION
8. Chiffres RGPD et paramètres sécurité à mémoriser
9. Définitions à restituer mot pour mot
10. OWASP Top 10 et correspondances de failles

📖 1. Requêtes préparées PDO et anti injection SQL

🔑 Notions clés & Définitions

• Requête préparée PDO : Une requête préparée PDO sépare le SQL des valeurs et exécute ensuite le SQL avec des paramètres fournis.
• Paramètres nommés PDO : Des paramètres nommés PDO (ex. :login) servent de placeholders dont les valeurs sont passées à execute() pour éviter la concaténation.
• PDO::ATTR_ERRMODE : L’option PDO::ATTR_ERRMODE configure le mode de gestion des erreurs pour que les problèmes de requête soient signalés via exceptions.
• PDO::ATTR_EMULATE_PREPARES : L’option PDO::ATTR_EMULATE_PREPARES désactive l’émulation des requêtes préparées afin d’utiliser le mécanisme natif du SGBD.

📝 Points essentiels

• Créer le PDO avec charset=utf8mb4 et activer PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION pour obtenir des exceptions en cas d’erreur.
• Désactiver PDO::ATTR_EMULATE_PREPARES => false pour éviter l’émulation et s’appuyer sur les requêtes préparées du SGBD.
• Utiliser $pdo->prepare(...) puis$stmt->execute([...]) avec des paramètres nommés comme :login et :role.
• Passer les valeurs à execute() sous forme de tableau associatif dont les clés correspondent exactement aux placeholders.
• Récupérer un résultat avec $stmt->fetch(PDO::FETCH_ASSOC) après un SELECT avec LIMIT 1.
• Pour INSERT, récupérer l’identifiant créé via $pdo->lastInsertId() après execute().

💡 Astuce mémo

SQL + valeurs séparées : prepare() fixe le SQL, execute() injecte les paramètres (pas de concat).

📖 2. Hachage bcrypt et vérification password_verify

🔑 Notions clés & Définitions

• bcrypt : bcrypt : algorithme de hachage conçu pour stocker des mots de passe de façon résistante aux attaques par force brute.
• password_verify : password_verify : fonction PHP qui compare un mot de passe en clair à un hash et renvoie si la correspondance est correcte.
• hash de mot de passe : hash de mot de passe : valeur dérivée du mot de passe en clair, stockée en base pour éviter de conserver le secret en clair.
• XSS via affichage : XSS via affichage : risque où du contenu non échappé injecte du code côté navigateur lors d’un echo.

📝 Points essentiels

• bcrypt doit être utilisé pour générer le hash à partir du mot de passe en clair avant stockage en base.
• password_verify sert à vérifier la saisie utilisateur contre le hash stocké, sans jamais réutiliser le mot de passe en clair stocké.
• Les erreurs d’authentification doivent rester génériques pour ne pas révéler si l’identifiant ou le mot de passe est incorrect.
• Tout contenu affiché avec echo doit être échappé avec htmlspecialchars avant rendu pour réduire le risque XSS.
• htmlspecialchars doit utiliser ENT_QUOTES et UTF-8 pour gérer correctement guillemets simples/doubles et caractères.
• La vérification doit se faire côté serveur avant toute création de session ou redirection vers une zone protégée.

💡 Astuce mémo

bcrypt = “hachage lent” ; password_verify = “compare clair ↔ hash” ; htmlspecialchars = “avant echo, on neutralise”.

📖 3. Protection XSS et en-têtes de sécurité

🔑 Notions clés & Définitions

• XSS : Vulnérabilité web où du code malveillant est injecté et exécuté dans le navigateur d’un utilisateur via une page ou un paramètre non correctement filtré.
• En-têtes de sécurité : Ensemble de paramètres HTTP envoyés par le serveur pour réduire certaines surfaces d’attaque côté navigateur, notamment en limitant l’exécution de scripts et le chargement de ressources.
• Content-Security-Policy : En-tête HTTP qui définit des règles de chargement (origines autorisées) pour limiter l’exécution de scripts et réduire l’impact d’une injection.
• X-Content-Type-Options : En-tête HTTP qui force le navigateur à respecter le type MIME déclaré, ce qui limite les interprétations dangereuses de contenus.
• X-Frame-Options : En-tête HTTP qui contrôle si une page peut être intégrée dans un iframe, afin de réduire les attaques par clickjacking.

📝 Points essentiels

• La protection XSS repose sur la validation/échappement des entrées et sur des en-têtes qui empêchent l’exécution de scripts non autorisés côté navigateur.
• Content-Security-Policy réduit l’impact d’une injection en n’autorisant que des sources explicitement permises pour les scripts et autres ressources.
• X-Content-Type-Options limite les risques liés au MIME sniffing en empêchant le navigateur de deviner un type différent de celui annoncé.
• X-Frame-Options diminue le clickjacking en empêchant l’affichage de la page dans un cadre (iframe) non souhaité.
• Les en-têtes de sécurité complètent le filtrage applicatif : ils ne remplacent pas l’assainissement des données côté serveur.
• Comparaison : CSP vs X-Frame-Options — CSP cible l’exécution/chargement de scripts, tandis que X-Frame-Options cible l’intégration en iframe.

💡 Astuce mémo

CSP = Scripts contrôlés ; X-Frame-Options = Frames bloqués ; X-Content-Type-Options = MIME respecté.

📖 4. Sessions sécurisées et déconnexion complète

🔑 Notions clés & Définitions

• Déconnexion complète : La déconnexion complète est la suppression effective de la session côté serveur et l’invalidation des jetons/cookies pour empêcher toute réutilisation.
• CSRF : La CSRF est une attaque qui force le navigateur d’une victime à exécuter une action non voulue sur un site où elle est déjà authentifiée.
• Token CSRF : Un token CSRF est une valeur aléatoire associée à une requête, vérifiée côté serveur pour bloquer les actions forgées.
• random_bytes : random_bytes est une fonction qui produit des octets cryptographiquement aléatoires pour générer des secrets comme des tokens.

📝 Points essentiels

• Une session sécurisée repose sur l’invalidation côté serveur lors de la déconnexion, pas seulement sur la suppression côté navigateur.
• Un token CSRF peut être généré avec random_bytes(32) puis encodé en hex via bin2hex, ce qui donne 64 caractères hexadécimaux.
• Le contrôle du token CSRF doit être fait à chaque requête sensible pour empêcher l’exécution d’actions forgées.
• La déconnexion complète doit empêcher la réutilisation d’un ancien identifiant de session ou d’un ancien jeton.
• Comparaison : CSRF vs XSS — CSRF vise une action non voulue via le navigateur déjà authentifié, tandis que XSS exécute du code malveillant dans le navigateur via une page ou une URL piégée.

💡 Astuce mémo

CSRF = “contre les requêtes forgées” : token aléatoire (32 octets → 64 hex) + invalidation à la déconnexion.

📖 5. Droits SQL GRANT REVOKE et moindre privilège

🔑 Notions clés & Définitions

• Moindre privilège : Principe de sécurité où chaque entité ne reçoit que les droits strictement nécessaires à sa tâche, afin de limiter l’impact d’un abus.
• GRANT : Commande SQL qui accorde des droits (permissions) à un utilisateur ou à un rôle sur une ressource donnée.
• REVOKE : Commande SQL qui retire des droits précédemment accordés à un utilisateur ou à un rôle sur une ressource donnée.
• Droits sur ressources : En SQL, les permissions sont attachées à une ressource précise (par exemple une base, une table ou une vue) et à un type d’action autorisée.

📝 Points essentiels

• Le moindre privilège vise à réduire la surface d’attaque en limitant ce qu’un compte peut faire en cas de compromission.
• GRANT sert à donner explicitement des permissions, tandis que REVOKE sert à les retirer explicitement.
• Les droits doivent être accordés au niveau le plus fin possible (ressource et action) pour éviter des accès trop larges.
• Retirer des droits avec REVOKE est une mesure de durcissement quand un besoin d’accès disparaît ou change.
• Le principe de moindre privilège s’applique aussi aux rôles et processus, pas seulement aux utilisateurs humains.

💡 Astuce mémo

GRANT = donner, REVOKE = retirer ; Moindre privilège = “juste assez de droits”.

📖 6. Triggers MySQL et journalisation des actions

🔑 Notions clés & Définitions

• Journalisation des actions : La journalisation des actions est l’enregistrement systématique des opérations sensibles pour détecter, enquêter et prouver une activité.
• Trigger MySQL : Un trigger MySQL est un mécanisme qui exécute automatiquement du code lors d’un événement sur une table (INSERT, UPDATE, DELETE).
• Table journal_actions : La table journal_actions est un stockage dédié qui conserve les traces d’accès et d’actions afin de soutenir la surveillance et l’audit.
• Accountability RGPD : L’accountability RGPD impose au responsable de traitement de pouvoir démontrer sa conformité au RGPD.

📝 Points essentiels

• OWASP A09:2021 vise les échecs de logging et monitoring, donc une absence de traces empêche de détecter une intrusion.
• Une journalisation utile doit couvrir les accès et actions, sinon l’investigation après incident devient impossible.
• L’usage d’une table dédiée comme journal_actions facilite la traçabilité et la corrélation des événements.
• Les triggers MySQL peuvent alimenter journal_actions automatiquement à chaque modification pertinente des données.
• Le principe de responsabilité RGPD (Art. 5(2)) exige de pouvoir prouver la conformité, ce qui rend la journalisation indispensable en pratique.

💡 Astuce mémo

A09 = « Absence de traces = Intrusion invisible » : sans logs, pas de détection ni de preuve.

📖 7. Vues SQL avec WITH CHECK OPTION

🔑 Notions clés & Définitions

• WITH CHECK OPTION : Option de vue qui impose que toute ligne modifiée via la vue respecte le prédicat de la vue.
• Vue SQL : Objet de base de données qui présente un résultat de requête comme une table virtuelle.
• Prédicat de la vue : Condition $WHERE$ qui définit quelles lignes sont visibles et donc autorisées par la vue.
• Contrôle d’accès par vue : Principe consistant à limiter ce que l’utilisateur peut voir ou modifier en s’appuyant sur les vues et leurs contraintes.

📝 Points essentiels

• WITH CHECK OPTION empêche qu’une mise à jour ou suppression via la vue ne fasse sortir la ligne du périmètre défini par le prédicat.
• Sans WITH CHECK OPTION, une modification peut rendre une ligne non conforme au filtre de la vue tout en restant en base.
• WITH CHECK OPTION s’applique aux opérations de modification effectuées à travers la vue (par exemple UPDATE/DELETE) et vérifie la condition de la vue.
• Le prédicat de la vue doit être conçu pour refléter exactement le sous-ensemble autorisé, sinon la contrainte peut être trop permissive ou trop restrictive.
• Pour un contrôle robuste, combine la contrainte de la vue avec le principe du moindre privilège sur les droits SQL de l’application.

💡 Astuce mémo

Pense à « CHECK » comme un garde-fou : la ligne doit rester « dans le cadre » du filtre de la vue.

📖 8. Chiffres RGPD et paramètres sécurité à mémoriser

🔑 Notions clés & Définitions

• SQL Injection : Vulnérabilité où un attaquant injecte du code dans une requête SQL pour contourner la logique applicative.
• XSS : Vulnérabilité où du contenu malveillant est exécuté dans le navigateur d’un utilisateur via une sortie non maîtrisée.
• password_hash : Fonction PHP de hachage de mots de passe qui applique un algorithme adapté et un coût paramétrable.
• Consentement passif RGPD : Modèle de consentement où une case pré-cochée est interprétée comme un accord, ce qui est interdit par l’opt-in.
• Principe du moindre privilège : Principe de sécurité qui limite les droits d’un compte aux seules actions nécessaires à l’application.

📝 Points essentiels

• Toujours utiliser des requêtes préparées (prepare) et ne jamais concaténer $_GET['id'] ou$_POST['x'] directement dans le SQL pour éviter l’injection.
• Pour éviter l’XSS, afficher les variables avec htmlspecialchars($var, ENT_QUOTES, 'UTF-8') avant de les rendre dans la page.
• Ne jamais stocker un mot de passe en clair en base et ne pas utiliser md5($password) ou sha1($password) pour les mots de passe.
• Utiliser password_hash($mdp, PASSWORD_BCRYPT, ['cost'=>12]) pour produire un hachage robuste.
• Le consentement RGPD doit être un opt-in : supprimer l’attribut checked d’une case pour éviter un consentement passif (art. 7).
• En production, ne pas laisser display_errors=On : préférer display_errors=Off et log_errors=On pour ne pas exposer d’informations sensibles.

💡 Astuce mémo

Injection→Préparé, XSS→Échappé, Mot de passe→password_hash cost 12, RGPD→opt-in (pas checked).

📖 9. Définitions à restituer mot pour mot

🔑 Notions clés & Définitions

• Vue filtrante : Une vue filtrante est une vue dont le WHERE limite les lignes concernées, et qui sert de filtre pour les opérations INSERT/UPDATE autorisées via cette vue.
• CREATE PROCEDURE : CREATE PROCEDURE est la commande SQL qui définit une procédure stockée avec paramètres d’entrée et de sortie.
• Transaction : Une transaction est un bloc d’exécution SQL regroupant plusieurs opérations, validé par COMMIT ou annulé par ROLLBACK.
• EXIT HANDLER FOR SQLEXCEPTION : Un EXIT HANDLER FOR SQLEXCEPTION est un gestionnaire d’exception qui s’exécute en cas de SQLEXCEPTION et peut annuler la transaction.
• ON DELETE : ON DELETE est la clause de contrainte de clé étrangère qui définit le comportement lors de la suppression d’une ligne référencée.

📝 Points essentiels

• Bloquer INSERT/UPDATE via une vue : les opérations ne doivent pas contourner le filtre WHERE de la vue.
• CREATE PROCEDURE avec paramètres : IN p INT et OUT r VARCHAR(50) permettent de passer une valeur en entrée et récupérer un résultat en sortie.
• Appel de procédure : CALL nom(5, @result); puis lecture du résultat avec SELECT @result;.
• Transaction : START TRANSACTION; puis COMMIT; ou ROLLBACK; pour valider ou annuler.
• EXIT HANDLER : DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; END; permet d’annuler en cas d’erreur SQL.
• ON DELETE RESTRICT bloque la suppression si une ligne est référencée, CASCADE supprime les lignes dépendantes, SET NULL met la clé à NULL, NO ACTION ne modifie rien.

💡 Astuce mémo

Vue = filtre : si la ligne ne passe pas le WHERE, l’INSERT/UPDATE doit être bloqué.

📖 10. OWASP Top 10 et correspondances de failles

🔑 Notions clés & Définitions

• OWASP Top 10 : Référentiel de risques web classant les failles les plus fréquentes et leurs impacts pour guider la priorisation des corrections.
• A01 Accès et moindre privilège : Catégorie OWASP visant les contrôles d’accès insuffisants et l’absence de principe du moindre privilège.
• A02 Crypto et hachage : Catégorie OWASP centrée sur la protection cryptographique, notamment le choix de fonctions de hachage adaptées.
• A03 Injection SQL et XSS : Catégorie OWASP regroupant les injections et les exécutions de code dues à un traitement non sécurisé des entrées.
• A07 Authentification et sessions : Catégorie OWASP couvrant les faiblesses d’authentification, de gestion de session et les attaques par force brute.

📝 Points essentiels

• MD5 est cassable (rainbow tables + brute force GPU) alors que bcrypt est conçu pour être lent et résistant aux attaques par force brute.
• bcrypt se vérifie avec password_verify(saisi, hash_bdd) et se crée avec password_hash(mdp, PASSWORD_BCRYPT, ['cost'=>12]).
• hash_equals() compare en temps constant pour réduire les timing attacks, contrairement à un === direct sur des valeurs sensibles.
• XSS se corrige en échappant avec htmlspecialchars($v, ENT_QUOTES, 'UTF-8') avant affichage.
• CSRF se corrige avec un token aléatoire (bin2hex(random_bytes(32))) et une vérification via hash_equals($_SESSION['t'],$_POST['t']).
• Injection SQL : utiliser PDO avec prepare puis execute([':p'=>$v]) au lieu d’assembler des requêtes avec$_POST directement dans le SQL.

💡 Astuce mémo

A0X:2021 = Accès (A01) / Crypto (A02) / Injection (A03) / Auth (A07) / Logs (A09).

📊 Tableaux de synthèse

CSP vs X-Frame-Options

CSRF vs XSS

⚠️ Pièges & confusions fréquents

1. Confondre prepare/execute : concaténer $_GET/$_POST dans le SQL au lieu d’utiliser des placeholders nommés et un tableau passé à execute().
2. Oublier ENT_QUOTES et UTF-8 dans htmlspecialchars : htmlspecialchars($var,'UTF-8') sans ENT_QUOTES peut laisser passer des guillemets et casser l’échappement attendu.
3. Vérifier un bcrypt avec === ou md5 : comparer directement des hashes avec === ou utiliser md5($password) au lieu de password_verify($saisi,$hash_bdd).
4. Faire une déconnexion incomplète : seulement unset côté PHP sans invalider la session/détruire et sans supprimer le cookie, laissant une réutilisation possible.
5. Se tromper sur NEW/OLD dans un trigger : utiliser NEW.colonne dans AFTER DELETE alors que NEW n’existe pas après DELETE (il faut OLD).
6. Croire que WITH CHECK OPTION est un “contrôle d’accès” général : c’est un garde-fou sur INSERT/UPDATE pour respecter le WHERE de la vue, pas un chiffrement ni un contrôle d’autorisations global.
7. Mauvaise config RGPD consentement : laisser une case pré-cochée (checked) au lieu d’un opt-in actif, non conforme à l’Art. 7 RGPD.

✅ Checklist Examen

1. Écrire de mémoire la connexion PDO avec charset=utf8mb4, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION et PDO::ATTR_EMULATE_PREPARES => false.
2. Écrire de mémoire un SELECT préparé : $stmt=$pdo->prepare('... login=:login AND role=:role LIMIT 1'); puis $stmt->execute([':login'=>$login,':role'=>$role]); puis fetch(PDO::FETCH_ASSOC).
3. Écrire de mémoire un INSERT préparé avec placeholders nommés, puis récupérer l’identifiant via $pdo->lastInsertId() après execute().
4. Écrire de mémoire un UPDATE/DELETE préparé avec placeholders nommés et exécution via $stmt->execute([':s'=>$statut,':id'=>$id]) (même logique que SELECT).
5. Écrire de mémoire le hachage bcrypt : password_hash($password,PASSWORD_BCRYPT,['cost'=>12]) et la vérification : password_verify($password_saisi,$user['password_hash']).
6. Écrire de mémoire l’affichage XSS-safe : echo htmlspecialchars($valeur, ENT_QUOTES, 'UTF-8'); et citer l’idée “échapper avant echo”.
7. Écrire de mémoire les en-têtes de sécurité attendus : Content-Security-Policy, X-Content-Type-Options, Strict-Transport-Security (et savoir leur rôle).
8. Écrire de mémoire la session sécurisée : session_regenerate_id(true) après connexion réussie, et la déconnexion complète : $_SESSION=[]; session_destroy(); setcookie(session_name(),'',['expires'=>time()-3600,'path'=>'/']
9. Écrire de mémoire la logique CSRF : token généré avec bin2hex(random_bytes(32)) et vérification côté serveur (token aléatoire + contrôle à chaque requête sensible).
10. Écrire de mémoire les commandes GRANT/REVOKE et l’idée “moindre privilège” : GRANT pour accorder, REVOKE pour retirer, et FLUSH PRIVILEGES après modifications manuelles.
11. Écrire de mémoire la structure obligatoire d’un trigger MySQL : DELIMITER //, CREATE TRIGGER ... AFTER INSERT/UPDATE/DELETE ... FOR EACH ROW BEGIN ... END // DELIMITER ; et l’usage NEW/OLD (NEW après INSERT/UPDATE, OLD à
12. Écrire de mémoire une vue avec WITH CHECK OPTION : CREATE VIEW ... AS SELECT ... WHERE ... WITH CHECK OPTION; et expliquer qu’elle bloque INSERT/UPDATE qui sortiraient du WHERE.
13. Écrire de mémoire les formules RGPD à placer : “Art. 33 ... 72 heures” en cas de fuite, et “opt-in (case décochée) Art. 7” pour consentement passif.