Aller au contenu
ELECTRONICS ENGINEERINGelectronics-engineering.com
Systèmes d'information

SQL : requêtes, regroupements, valeurs nulles et injection

Création de tables et contraintes, select, jointures internes et externes, group by et having, sous-requêtes, logique à trois valeurs et NOT IN avec des nuls démontrés, vues, requêtes préparées contre l'injection SQL.

https://www.electronics-engineering.com/fr/sql-requetes-jointures-null-logique-a-trois-valeurs/

Pas encore de vote : soyez le premier à noter cette page.

SQL est le langage pour dialoguer avec toute base de données relationnelle, de celle d’un téléphone à celles des banques. Il est né chez IBM dans les années 1970 et c’est une norme internationale. Dans cette leçon, nous regardons les requêtes avec sélection, jointure et tri, les regroupements, les sous-requêtes, la valeur nulle et la logique à trois valeurs avec la démonstration d’une erreur classique, les vues, et la façon de se défendre contre l’injection SQL. Les exemples utilisent le schéma Étudiants, Cours et Examens de la leçon sur la normalisation.

Définir et modifier les données

On crée les tables en déclarant colonnes, types et contraintes :

CREATE TABLE Exams (
  number INT REFERENCES Students(number),
  code   CHAR(6) REFERENCES Courses(code),
  mark   INT CHECK (mark BETWEEN 18 AND 30),
  date   DATE NOT NULL,
  PRIMARY KEY (number, code)
);

On modifie les données avec INSERT, UPDATE et DELETE, et le SGBD rejette les opérations qui violent les contraintes (leçon sur le modèle relationnel).

Les requêtes

La forme de base est SELECT (quoi afficher) FROM (depuis quelles tables) WHERE (sous quelles conditions), qui correspondent à la projection, à la jointure et à la sélection de l’algèbre relationnelle :

SELECT s.surname, c.title, e.mark
FROM Exams e
JOIN Students s ON s.number = e.number
JOIN Courses c  ON c.code = e.code
WHERE e.mark >= 28
ORDER BY e.mark DESC, s.surname;

La jointure interne (JOIN) ne garde que les lignes qui trouvent une correspondance. La jointure externe (LEFT JOIN) garde toutes les lignes de la table de gauche, en remplissant de nuls les colonnes de l’autre table quand il n’y a pas de correspondance : on l’utilise, par exemple, pour lister aussi les étudiants sans examens.

Les regroupements

Les fonctions d’agrégation (COUNT, SUM, AVG, MIN, MAX) résument des groupes de lignes. GROUP BY forme les groupes, HAVING les filtre après l’agrégation :

SELECT s.number, s.surname, AVG(e.mark) AS average, COUNT(*) AS exams
FROM Students s JOIN Exams e ON e.number = s.number
GROUP BY s.number, s.surname
HAVING COUNT(*) >= 5
ORDER BY average DESC;

WHERE filtre les lignes avant de les regrouper, HAVING filtre les groupes après : la condition sur la moyenne va dans HAVING, parce que la moyenne n’existe qu’après le regroupement.

Les sous-requêtes

Une requête peut en contenir une autre : « les étudiants qui ont une note supérieure à la moyenne de toutes les notes ».

SELECT DISTINCT s.surname
FROM Students s JOIN Exams e ON e.number = s.number
WHERE e.mark > (SELECT AVG(mark) FROM Exams);

Avec IN, EXISTS et NOT EXISTS, on exprime l’appartenance et l’existence, par exemple les étudiants qui n’ont jamais passé un certain examen.

La valeur nulle et la logique à trois valeurs

NULL représente une valeur inconnue ou inexistante : une note pas encore enregistrée, un deuxième prénom qui n’existe pas. Une comparaison avec une valeur nulle n’est ni vraie ni fausse, mais inconnue (U) : SQL utilise une logique à trois valeurs.

Tables de vérité de ET et OU en logique à trois valeurs
T vrai, U inconnu, F faux. Un ET avec un terme faux est faux quel que soit l’autre, un OU avec un terme vrai est vrai ; dans les autres cas avec un terme inconnu, le résultat est inconnu. La négation de l’inconnu reste inconnue.

La clause WHERE ne garde que les lignes pour lesquelles la condition est vraie : celles dont le résultat est faux ou inconnu sont écartées.

Théorème. Si la sous-requête contient au moins une valeur nulle, la condition x NOT IN (subquery) n’est jamais vraie, et la requête ne renvoie aucune ligne.

Démonstration. x NOT IN (v1, ..., vn) est équivalent à x <> v1 AND ... AND x <> vn. Si l’une des valeurs est nulle, la comparaison correspondante x <> NULL est U. Un ET qui contient un terme U peut être F (si un autre terme est faux, c’est-à-dire si x est dans la liste) ou U (si tous les autres sont vrais), mais jamais T, comme le montre la table. Aucune ligne ne satisfait donc la condition. ∎

C’est l’une des erreurs les plus perfides de SQL : une requête qui « ne trouve rien » sans lever aucune erreur. On l’évite en utilisant NOT EXISTS, qui n’a pas ce problème, ou en excluant explicitement les nuls. Pour la même raison, le test de nullité s’écrit IS NULL, non = NULL, qui est toujours U. Les fonctions d’agrégation ignorent elles aussi les nuls : COUNT(*) compte les lignes, COUNT(mark) seulement celles dont la note n’est pas nulle, et AVG ne fait la moyenne que des valeurs présentes.

Les vues

Une vue est une requête à laquelle on donne un nom et qu’on utilise comme une table :

CREATE VIEW Averages AS
SELECT number, AVG(mark) AS average FROM Exams GROUP BY number;

Les vues simplifient les requêtes répétées, cachent la complexité du schéma et servent à accorder des permissions limitées : un utilisateur peut avoir accès à la vue des moyennes sans voir les notes individuelles.

L’injection SQL

Construire une requête en collant du texte écrit par l’utilisateur est l’erreur de sécurité la plus classique sur le web. Si un programme exécute "SELECT * FROM Users WHERE name = '" + name + "'" et que quelqu’un tape comme nom ' OR '1'='1, la condition devient toujours vraie et renvoie tous les utilisateurs ; avec des variantes, on peut lire ou effacer des tables entières. La défense est d’utiliser toujours des requêtes préparées avec des paramètres :

SELECT * FROM Users WHERE name = ?

la valeur est passée séparément et le SGBD la traite comme une donnée, jamais comme du code. S’y ajoutent le principe du moindre privilège (l’application se connecte avec un utilisateur qui ne peut faire que ce dont elle a besoin) et la validation des données saisies.

S’abonner
Notifier de
guest
1 commentaire
Le plus ancien
Plus récent Les plus votés
1
0
Vous avez apprécié cet article ? Merci de laisser un commentaire.x