Utilisation avancée de MYSQL

Dans ce chapitre je vais présenter les vues (view) c'est à dire les tables virtuelles et les requêtes préparées qui sont très importantes en matière de sécurité.
Mais auparavant je veux vous montrer une astuce afin que le code SQL des requêtes multitables ne soit pas pas trop lourd.

Utiliser des alias pour les tables

Pour les requêtes multitables et afin de simplifier la syntaxe il est conseillé d'utiliser des alias de champs mais surtout des alias de tables.

La requête avec jointure interne entre "billets" & "commentaires"

Solution sans alias de table

SELECT billets.id_billet as idb, id_commentaire, titre, contenu, 
date_format(date_billet,'%d-%m-%Y')AS date_billet, auteur, commentaire
FROM billets INNER JOIN commentaires ON billets.id_billet = commentaires.id_billet 
ORDER BY billets.id_billet ;

Solution avec alias de table

La table "billets" aura pour alias "b" et la table "commentaires" aura pour alias "c".

On peut alors écrire :

SELECT b.id_billet, c.id_billet, id_commentaire, titre, contenu, 
date_format(date_billet,'%d-%m-%Y')AS date_billet, auteur, commentaire
FROM billets b 
INNER JOIN commentaires c 
ON b.id_billet = c.id_billet 
ORDER BY b.id_billet ;

Volontairement j'affiche les deux champs de jointure : b.id_billet & c.id_billet.

Le rendu

2 2	1 Beaujolais nouveau	Pot de l'amitié le jeudi...  15-10-2025 Charles J'aime pas le vin rouge ...
2 2	2 Beaujolais nouveau	Pot de l'amitié le jeudi ..  15-10-2025 Jacques Je préfère un verre de cidre
3 3	3 Poste vacant Suite à la démission de Pauline ... 14-10-2025	Julie	Je suis candidate ... 
3 3	4 Poste vacant Suite à la démission de Pauline ... 14-10-2025	Martin	Je suis candidat.

Je ne peux que vous conseiller de tester vous même ce code à partir de PHPMYADM (en local ou chez votre hébergeur).

MYSQL : les vues

Définition

Une vue est une requête sélection (en général complexe) qui est mémorisée dans la base.
L'exécution de la vue produit une table virtuelle.

Création d'une vue

Je vais mémoriser la jointure interne entre les tables 'billets' & 'commentaires' sous forme d'une vue.

Le code de la vue "jointure

J'accéde à PHPMYADIM chez mon hébergeur, je sélectionne la base et je colle dans l'onglet "SQL" le code suivant :

CREATE VIEW inner_join AS
SELECT b.id_billet, id_commentaire, titre, contenu, 
date_format(date_billet,'%d-%m-%Y')AS date_billet, auteur, commentaire
FROM billets b 
INNER JOIN commentaires c 
ON b.id_billet = c.id_billet 
ORDER BY b.id_billet ;

Je reprends donc exactement le code de la jointure interne entre "billets" & "commentaires".
Mais afin que cette requête soit mémorisée dans la base sous forme d'une vue, je fais précéder par "CREATE VIEW inner_join AS".
Donc le code SQL de création d'une vue est : CREATE view nomVue As SELECT ...

Il suffit ensuite de cliquer sur le bouton "exécuter".
Observez l'arborescence de la base ; le dossier "vues" comprend désormais une vue nommée "inner_join".

Le rendu

Ci-dessous l'état ayant pour source la vue "inner_join".

Notez que désormais la date du billet, son titre et son contenu ne sont affichés qu'une fois même si il y a N commentaires pour ce billet.

Le code de cet état

// nom de cet état : "inner_join.php"
...
$selection = $bdd->query("select * from inner_join;");
$precedent = 0;  
while($record = $selection->fetch())
{
	$idb = $record['id_billet'];
	$idc = $record['id_commentaire'];
	$date = $record['date_billet'];
	$titre = $record['titre'];
	$contenu = $record['contenu'];
	$auteur = $record['auteur'];
	$commentaire = $record['commentaire'];
	if ($idb ==$precedent) 
		echo "<br>$auteur a dit :  $commentaire"; 
	else
	{
		echo "<hr><p>Billet n° : $idb du : $date  Titre :  $titre Libellé : $contenu"; 
		echo "<br>$auteur a dit : $commentaire"; 
	}
	$precedent = $idb; 
} // fin while
?> 
...

Notez la simplicité de l'instruction PHP qui appelle la requête : SELECT * FROM inner_join
On manipule la vue "inner_join" comme une table.
On n'a plus à se préoccuper de la sélection des champs, du type de jointure, de l'ordre des enregistrements. Tout est géré en amont.

Concernant l'état remarquez l'astuce pour que certains champs de la vue ne soient affichés qu'une fois.
Au premier passage dans la boucle $precedent vaut 0 alors que $idb vaut 2 donc affichage de tous les champs de l'enregistrement.
Par contre au deuxième passage $precedent vaut 2 et $idb vaut toujours 2 donc égalité et donc affichage seulement des champs $auteur & $commentaire.

Travaux pratiques

À vous de 'bosser' !
Je vous propose de tester vos connaissances en réalisant des requêtes des vues et des états sourcés sur ces vues.

Étape 1

Utilisez l'utilitaire PHPMYADIM installé sur votre PC pour créer deux vues supplémentaire :
"left_join"
"right_join"

Partez du code SQL des requêtes multi-tables sur "billets" & "commentaires" dans le chapitre sur les requêtes SELECT.
Bien évidemment pour simplifier le code vous utiliserez des alias de tables.
Les requêtes SELECT

Étape 2

"left_join.php" ayant pour source "select * from left_join"
""right_join.php" ayant pour source : "select * from right_join"

Les requêtes préparées

Injection SQL

L'injection SQL est une vulnérabilité de sécurité qui permet à des attaquants d'exécuter des requêtes SQL malveillantes sur une base de données MySQL.

Une injection SQL consiste à insérer du code SQL malveillant dans des champs de saisie. Ce code SQL sera ensuite exécuté par le moteur de base de données.

voler des données sensibles (mots de passe,informations personnelles, etc.).
modifier ou supprimer des données.
prendre le contrôle du serveur de base de données.

Les injections SQL c'est un sujet complexe.
Pour en savoir davantage, je vous invite à lire l'excellent article sur WIKIPEDIA : Les injections SQL

Cet article est excellent car il est illustré d'exemples.

Pour se protéger contre les injections SQL avec MySQL, il est recommandé d'utiliser des requêtes préparées ('Prepared Statements') afin de bien séparer le code SQL des données utilisateur.

Requête préparée (Prepared Statements)

La page "recherche_inscrits.php" est mise à la disposition de l'administration du site afin de retrouver rapidement un inscrit en saisissant son identifiant (adresse mail).
Cette page a pour source une requête paramétrée préparée.

Le code du document PHP

// inclusion des fichiers commun.php & connexion.php
...
<h3>Recherche d'un inscrit dans la table 'inscrits'</h3>
<form action ="#" method ="get">
	<p><label>Saisir l'identifiant de l'inscrit recherché</label><input type ='email' required  name ='identifiant'>
	<p><button type ="submit">Connexion</button>
</form>
<h4>
<?php
if(isset($_GET['identifiant']))
{
	$requete = $bdd->prepare('SELECT * FROM inscrits WHERE identifiant = ? ');
	$requete->execute(array($_GET['identifiant'])); 
	$record = $requete->fetch(); 
	if (!$record)  //si aucun enregistrement trouvé
	{
		echo "Cet identifiant n'existe pas !"; 
	}
	else 
	{
		$nom = $record['nom']; 
		$prenom = $record['prenom']; 
		echo "<br>L'inscrit a pour nom  : $nom"; 
		echo "<br>L'inscrit a pour prénom  : $prenom";
	}
} 
else echo "Renseignez le formulaire !"; 
?>
</h4>
...

Dans la realité la table "inscrits" comprendra des milliers voire des dizaines de milliers d'enregistrements.
Comme la recherche se fait sur l'identifiant et que cette colonne est clé primaire, la recherche sera implicitement indexée.
Une clé primaire est un type d'index qui garantit l'unicité et la non-nullité des valeurs.

Étude de la requête préparée

Dans une requête paramétrée ordinaire on aurait écrit : $bdd->query("SELECT * FROM inscrits WHERE identifiant = '$_GET['identifiant]' ");

Dans une requête préparée il y a une phase de préparation avec la méthode prepare()
$requete = $bdd->prepare('SELECT * FROM inscrits WHERE identifiant = ? ');
La partie variable de la requête est représentée par le symbole "?" (un marqueur).
Phase 2 : exécution de la requête préparée : $requete->execute(array($_GET['identifiant']));
La requête est alors exécutée grâce aux paramètres passés sous forme d'un 'array'.

Le rendu

Rappel du contenu de la table 'inscrits' :

INSERT INTO `inscrits` (`identifiant`, `motpasse`, `nom`, `prenom`, `date_naiss`) VALUES
('dupont@free.fr', 'zoro5362', 'Dupont', 'Julien', '1955-10-17'),
('dupuis@aol.com', 'sesame59', 'Dupuis', 'Léon', '1965-10-17'),
('lecornu@matignon.fr', 'censure26', 'Lecornu', 'Sébastien', '1975-10-17'),
('macron@elysee.com', 'toxique75', 'Macron', 'Emmanuel', '1985-10-17');

Saisissez l'un des identifiants et observez.

Requête préparée avec plusieurs paramètres

Dans le chapitre 27 (les requêtes sélection) je l'avoue, je vous ai menti.
Pour la connexion d'un inscrit à son espace perso je vous ai proposé une code ouvert aux injections SQL ; une requête sélection paramétrée ordinaire :
SELECT * FROM inscrits WHERE identifiant = '$identifiant_saisi' AND motpasse ='$motpasse_saisi'

En fait le véritable code qui prévient des injections SQL est : ... $requete = $bdd->prepare('SELECT * FROM inscrits WHERE identifiant = ? AND motpasse = ? '); $requete->execute(array($_POST['identifiant'],$_POST['motpasse'] )); $record = $requete->fetch(); if (!$record) //si aucun enregistrement trouvé { ... } ...

Notez qu'il y a deux marqueurs dans la requête préparée donc pour l'exécuter il faut deux élements à la fonction array()

Traduction

Débuter en programmation web - sommaire

Initiation au HTML & CSS

Initiation à PHP & MYSQL

Débuter en programmation web - recherche

L'auteur : Patrick Darcheville

Utilisation avancée de MYSQL

Utiliser des alias pour les tables

La requête avec jointure interne entre "billets" & "commentaires"

Solution sans alias de table

Solution avec alias de table

Le rendu

MYSQL : les vues

Définition

Création d'une vue

Le code de la vue "jointure

Le rendu

Le code de cet état

Travaux pratiques

Étape 1

Étape 2

Les requêtes préparées

Injection SQL

Requête préparée (Prepared Statements)

Le code du document PHP

Étude de la requête préparée

Le rendu

Requête préparée avec plusieurs paramètres