Requêtes action SQL

Dans le chapitre précédent j'ai évoqué les requêtes sélection (qui commencent par le mot clé SELECT). je vais dans ce chapitre aborder les requêtes action (création, ajout, modification et suppression).
Les requêtes action modifient le contenu de la base de données. Elles doivent être mises à la disposition du visiteur avec un maximum de précaution.
Si, par exemple dans le cadre d'un forum, vous autorisez les internautes à ajouter des lignes dans une table, il faut empêcher l'injection d'instructions HTML ou JS malveillantes ...

Les requêtes CREATE TABLE

Ce type de requête crée la structure d'une nouvelle table.
Il faudra ensuite insérer des enregistrements dans la nouvelle table.
Une requête CREATE TABLE peut être générée via l'utilitaire PHPMyadmin.

Rappel : la requête qui crée la table "billets"

CREATE TABLE `billets` 
(
  `id_billet` int(3) AUTO_INCREMENT PRIMARY KEY,
  `titre` varchar(30) NOT NULL,
  `date_billet` date NOT NULL,
  `contenu` text NOT NULL
);

Notez bien les attributs du champ 'id_billet' : AUTO_INCREMENT, PRIMARY KEY
Les autres champs sont NOT NULL ce qui signifie qu'ils doivent impérativement avoir un contenu.

Les requêtes INSERT

Une requête INSERT permet d'ajouter un ou plusieurs enregistrements dans une table.
Elle aussi peut être générée par PHPMyadmin.

Exemples de requêtes INSERT

Dans un chapitre précédent je vous ai déjà communiqué deux exemples de requêtes INSERT

INSERT INTO news(date_new,titre,message) VALUES (CURDATE(),'$titre','$message');

INSERT INTO news (date_new, titre, message) VALUES
	('2025-02-10', 'Convocation AG', 'Convocation AG.\r\nBlabla ...\r\nBlabla ...'),
	('2025-02-20', 'Convocation bureau', 'Convocation bureau.\r\nBlabla ...\r\nBlabla ...'),
	('2025-03-20', 'Compte rendu AG', 'Compte rendu AG.\r\nBlabla ...\r\nBlabla ...'),
	('2025-03-21', 'Compte rendu réunion bureau', 'Compte rendu réunion bureau.\r\nBlabla ...\r\nBlabla ....');

La première requête ajoute une ligne dans la table "news" ; le champ date_new est rempli avec la date courante grâce à la fonction SQL CURDATE() ; les autres champs sont remplis avec des données provenenant du formulaire de soumission.

La deuxième requête ajoute quatre enregistrements dans la table "news" ; elle a été générée par PHPMYAdmin lorsque je demande l'exportation de la table.

Dans les deux requêtes ajout il n'est pas mentionné le champ id car il est AUTO_INCREMENT donc le remplissage est assuré par le moteur SQL.

Requêtes modification

Une requête de mise à jour commence par le mot clé UPDATE.

Modification d'un enregistrement

Elle contient une clause WHERE.

Exemple : modification des champs "titre" et "message" des nouvelles lignes dans la table "news".
Exemple : modifier l'enregistrement identifié 21 :

UPDATE news
	SET titre = 'info bidon',message = 'ceci est une "fake new" nauséabonde et diffamatoire ... '
	WHERE id = 21;

Je peux insérer des guillemets doubles dans une chaîne délimitée par des guillemets simples !

Modifier toute une colonne

On veut que tous les champs "tel2" de la table "membres" soient remplis par la chaîne : "00 00 00 00 00"
UPDATE membres SET tel2 ="00 00 00 00 00"

La requête UPDATE n'a pas de clause WHERE donc toute la colonne "tel2" est concernée.

Requêtes suppression

Elles commencent par le mot clé DELETE.

Supprimer tous les enregistrements d'une table

Pour vider la table "membres" il suffit d'écrire :
DELETE FROM membres ;

Attention cette requête vide la table (supprime tous les enregistrements) mais la structure subsiste ; elle est prête à recevoir de nouveaux enregistrements.

Pour vider une table on peut aussi utiliser TRUNCATE TABLE nomTable
La différence majeure étant que la commande TRUNCATE ré-initialise l’auto-incrémentation tandis que la commande DELETE ne ré-initialise pas l’auto-incrémentation.
Pour supprimer une table (données et structure) il faut utiliser la commande DROP TABLE nomTable.

Suppression sélective de lignes

Pour supprimer seulement les lignes vérifiant une condition il suffit d'introduire dans la requête une clause WHERE.
Exemple : supprimer dans "news" les lignes dont l'ID est supérieur à 20.
DELETE FROM news WHERE id >20;

Les requêtes ALTER

Une requête ALTER TABLE permet de modifier la structure d'une table existante.

Ajout de colonnes

Syntaxe : ALTER TABLE nomTable ADD nomColonne type;

Exemple : ALTER TABLE commentaires ADD date_commentaire date;
Sens : j'ajoute la colonne "date_commentaire" de type date dans la table "commentaires".

Renommage de colonnes

Syntaxe : ALTER TABLE nomTable CHANGE ancienNom nouveauNom type;

Suppression de colonnes

Syntaxe : ALTER TABLE nomTable DROP COLUMN nomColonne;

Ajout d'une clé primaire

Syntaxe : ALTER TABLE nomTable ADD PRIMARY KEY (nomColonne);

Exemple : Imaginons que lors de la création de la table "commentaires", j'ai oublié de demander que la colonne "id_commentaire' soit clé primaire.
Il suffit d'exécuter (dans l'onglet "SQL" de PHPMyadmin) le code suivant : ALTER TABLE `commentaires` ADD PRIMARY KEY (`id_commentaire`);

Interface de mise à jour de la table "news"

une page pour insérer une nouvelle info (donc appelant une requête SQL INSERT)
une page pour supprimer une info (donc appelant une requête SQL DELETE)
une page pour modifier une info (donc appelant une requête UPDATE)

Bien évidemment le nombre de personnes ayant accès à ces pages doit être restreint.
Par ailleurs et dans le cadre de l'extension PDO, l'appel d'une requête action se réalise avec la méthode exec().

La page pour ajouter une new

// inclusion de deux fichiers 
...	
<h3>Les dix dernières news</h4>
<table>
// en-têtes du tableau
<tr><th>N°</th><th>Titre de la new </th><th>Date de la new</th><th>message</th></tr>
<php
$selection = $bdd->query("SELECT * FROM news ORDER BY id DESC LIMIT 10") ;
while($record = $selection->fetch())
{
	// affichage des news (pense-bête)
	$id = $record['id'];
	$date_new = $record['date_new'];
	$titre = $record['titre'];
	$message = $record['message'];
	echo "<tr><td>$id</td>  <td>$titre</td> <td>$date_new</td> <td>$message</td></tr> "; 
	
} // fin tant que 
?>
</table>
<h3>Saisie de news</h3>
<form action ="#" method ="post">
<label>Titre de l'info </label><input type ="text" name ="titre" required>
<p><label>Titre de l'info </label><textarea name ="message"></textarea>
<p><button type ="submit">Soumission</button>
</form>
<h4>
<?php		
if(isset($_POST['titre']))
{	
	// requête qui ajoute un enregistrement dans la table news
	$titre = $_POST['titre'];
	$message = $_POST['message']; 
	$bdd->exec("INSERT INTO news(date_new,titre,message) VALUES (CURDATE(),'$titre','$message')") ;
	echo "<p class ='remarque' >info enregistrée !</p>"; 
}
else echo "Renseignez le formuaire"; 
?>
</h4>
...

Il y a beaucoup à dire sur ce code qui est assez long.
les données des dix dernières news sont affichées sous forme d'un beau tableau HTML.
Notez le code de la requête SELECT qui limite aux dix dernières infos avec la clause LIMIT.
Cet affichage tient lieu de pense-bête à l'administrateur afin qu'il ne resaisisse pas la même info.

Puis il y a un formulaire pour saisir une nouvelle new.

Un nouveau script PHP insère un nouvel enregistrement dans la table 'news'.
CURDATE() est une fonction MYSQL qui retourne la date courante ainsi l'utilisateur n'a pas à saisir la date. Le champ ID est aussi rempli automatiquement puisqu'il est AUTO_INCREMENT.

Les problèmes liés au code ci-dessus

Imaginons qu'un utilisateur saisisse l'info du jour en guise de titre de la nouvelle info.
Le texte saisi comprend une apostrophe (donc un guillemet simple) non échappée. Donc il va a y avoir tentative d'insertion de ... 'l'info du jour' ce qui est une chaine incorrecte.
Il aurait fallu que l'utilisateur saisisse l\'info du jour pour que insérer la chaine correcte cette fois) : 'l\'info du jour'.
Mais peut-on sérieusement demander à l'utilisateur d'échapper systématiquement les guillemets ?

Cependant il y a beaucoup plus grave !
Imaginons qu'un internaute malveillant (et oui ça existe) saisisse dans le champ 'titre' : <a href ="https://www.xvideos.com"></a> donc une instruction HTML parfaitement valide.

Tout internaute visitant la page "afficher_news.php" (qui visualise tous les enregistrements de la table 'news') pourra voir ce lien vers un site pornographique mais surtout cliquer desssus pour y accéder.
Votre site que se voulait sérieux est disqualifé !

La solution

Il suffit d'utiliser certaines fonctions prédéfinies de PHP.

Seul le dernier script PHP doit être modifié

...
if(isset($_POST['titre']))
{	
	// requête qui ajoute un enregistrement dans la table news
	$titre = htmlentities(addslashes($_POST['titre']));
	$message = htmlentities(addslashes($_POST['message'])); 
	$bdd->exec("INSERT INTO news(date_new,titre,message) VALUES (CURDATE(),'$titre','$message')") ;
	echo "<p class ='remarque' >info enregistrée !</p>"; 
}
...

J'emploie des fonctions PHP htmlentities et addslashes qui respectivement convertissent en entités de caractère les parenthèses angulaires (les chevrons sont à la base de la syntaxe HTML) et échappent les guillemets simples, doubles et apostrophes.
Donc les guillemets et apostrophes seront échappés et une instruction HTML sera convertie en chaine de caractères.
Les deux fonctions sont évoquées dans le chapitre sur les les fonctions PHP

Ci-dessous capture d'écran de l'état "ajouter_new.php"

Page pour supprimer une new : "supp_new.php"

Comme pour la page "ajouter_new.php" il faut afficher d'abord les 10 dernières news sous forme d'un tableau.

Le code correspondant

...
// affichage des dix dernières news 
// code identique à la page précédente

<form action='#' method='post'>
<label>ID de l'info à supprimer</label><input type='number' name='id' required>
<button type='submit' name='submit'>Validez</button>
</form>
<?php
if(isset($_POST['submit']))
{	
// requête qui supprime un enregistrement dans la table news
	$id = $_POST['id'];
	$bdd->exec("DELETE FROM news WHERE id ='$id' ") ;
}
?>
...

On affiche d'abord toutes les news. Puis l'utilisateur doit saisir l'ID de la new qu'il veut supprimer.
Attention en SQL l'égalité c'est = (et non pas ==) !
Notez que l'on a successivement appliqué à l'objet $bdd la méthode query("requête sélection") puis la méthode exec("requête action").
Quant à cette requête action elle débute par DELETE.

Page pour mettre à jour une info : "modif_new.php"

Comme pour la page "ajouter_new.php" il faut afficher d'abord les 10 dernières news sous forme d'un tableau. ... // affichage des dix dernières news // code identique à la page précédente <form action='#' method='post'> <label>ID de l'info à modifier </label> <input type='number' name='id' required> <br><label>Nouveau titre de la new</label> <input type='text' name='titre' required > <label>Nouveau texte</label> <br><,;textarea name='message' required ></textarea> <br><button type='submit' name='submit'>Validez</button> </form> <?php if(isset($_POST['id'])) { // requête qui modifie une info dans la table news $id = $_POST['id']; $titre = htmlentities(addslashes($_POST['titre'])); $message = htmlentities(addslashes($_POST['message'])); $bdd->exec("UPDATE news SET titre = '$titre', message = '$message' WHERE id ='$id' ") ; } ?>

On retrouve les fameuses fonctions qui neutralisent les injections malveillantes de code HTML (ou JS) et qui échappent les guillemets.

Essayez-vous même

Vous comprenez que vous ne pouvez pas tester ces trois documents PHP à partir de mon site.
Je vous invite donc à tester en local avec Wampserver OU si vous disposez d'un site hébergé à partir de votre site et de la base hébergée.
J'ai testé tous les documents PHP ; il n'y a pas de bugs.

Attention ces trois pages ont une faille ; elles ne ne sont pas à l'abri d'une injection SQL par un utilisateur malveillant.
Je n'en dit pas plus ; visitez la page : Utilisation avancée de MYSQL

Traduction

Débuter en programmation web - sommaire

Initiation au HTML & CSS

Initiation à PHP & MYSQL

Débuter en programmation web - recherche

L'auteur : Patrick Darcheville