Requêtes action SQL

requêtes sélections appelées avec la méthode query
requêtes action appelées avec la méthode exec

Dans le chapitre précédent j'ai évoqué les requêtes sélection (commencent par le mot clé SELECT) je vais dans ce chapitre évoquer les requêtes action (ajout, modification et suppression).
Comme les requêtes sélection les requêtes action sont appelées au sein de pages PHP qui constituent l'interface d'administration de la base de données.

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, par exemple des liens vers des sites ...

Les requêtes ajout

Une requête ajout permet d'insérer de nouveaux enregistrements dans une table.
Une requête ajout commence par le mot clé INSERT.

Exemples de requêtes ajout

Dans un chapitre précédent je vous ai déjà communiqué deux exemples de requêtes ajout (à la table NEWS) mais je n'avais pas donné d'explication ..
Je rappelle ci-dessous leur code : INSERT INTO news(date_new,titre,message) VALUES (CURDATE(),'$titre','$message'); INSERT INTO news (date_new, titre, message) VALUES ('2017-02-10', 'Convocation AG', 'Convocation AG.\r\nBlabla ...\r\nBlabla ...'), ('2017-02-20', 'Convocation bureau', 'Convocation bureau.\r\nBlabla ...\r\nBlabla ...'), ('2017-03-20', 'Compte rendu AG', 'Compte rendu AG.\r\nBlabla ...\r\nBlabla ...'), ('2017-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 ; les autres champs sont remplis avec des variables (données provenant d'un formulaire).

La deuxième requête ajoute quatre enregistrements dans la table "news".

Vous constatez que dans les deux cas on ne précise par la valeur affectée au champ ID qui pourtant ne peut rester vide puisque il est NOT NULL. Mais le champ ID a aussi l'attribut AUTO_INCREMENT donc le remplissage est automatique.

Requête ajout : autre syntaxe

Il n'est pas obligatoire de préciser après le nom de la table la liste de tous les champs à remplir.

Rappel sur la liste des colonnes dans la table "membres" : id, nom, prenom, sexe, adresse1, adresse2, adresse3, tel1, tel2, bureau, date_naiss, mail. Donc 12 champs !
Pour les champs relatifs au coordonnées (3 champs pour l'adresse, 2 champs pour les n° de téléphone, un champ pour l'adresse mail), NULL est autorisé. Donc pour ajouter une ligne dans la table "membres" j'ai deux solutions : INSERT INTO membres (id, nom, pre.nom, sexe, bureau, date_naiss) VALUES (20, 'Melanchon','jacques','M', 0,'1960-10-10'); OU INSERT INTO membres VALUES (20, 'Melanchon','jacques','M', null, null,null, null, null, 0,'1960-10-10',null)

Attention la deuxième solution paraît plus simple mais il faut prendre certaines précautions.
Après le mot clé VALUES il faut obligatoirement affecter une valeur à chaque champ et surtout il faut respecter l'ordre physique des champs.

Ajout dans la table "news"

Je vous rappelle la structure de la table "news" : 4 champs (id, date_new, titre, message).
Pour tous les champs NULL est interdit.
Donc pour ajouter trois enregistrements dans la table "news" je peux écrire tout simplement :
INSERT INTO news VALUES (21, curdate(),'', ''),(22, curdate(),'', ''),(23, curdate(),'', '');

Je peux remplir un champ à l'aide d'une fonction. Le champ "date_new" est de type date et la fonction SQL curdate() retourne la date courante.
Je remplis certains champs avec une chaîne vide ; une chaîne vide c'est différent de NULL !

Requêtes modification

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

Modification d'une ligne

Elle contient une clause WHERE.

Exemple : modification des champs "titre" et "message" des nouvelles lignes dans la table "news".
Ainsi pour 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" 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 alors pas de clause WHERE. Donc tous les enregistrements sont modifiées pour leur champ "tel2".

Requêtes suppression

Elles commencent par le mot clé DELETE.

Supprimer toutes les lignes

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

Attention cette requête vide la table (supprime tous les enregistrements) mais celle-ci 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 détruire une table dans une base de données (contenu 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;

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)

Page pour ajouter une info

Le code 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>"; 
}
...

Le code du formulaire (HTML)

...	
<form action='#' method='post'>
<p class ="remarque">* : saisie obligatoire ! </p> 
<label>Titre de la new  *</label>	<input type='text' name='titre' required />
<label>Entrer le message de la new  *</label>	<textarea name='message' required></textarea>
<label></label><button type='submit' name='submit'>Validez</button>
</form>
...

$bdd est la variable objet qui référence la connexion à la base de données.

Imaginons que l'utilisateur saisissent l'info du jour en guise de titre de la nouvelle new.
Le texte saisi comprend une apostrophe(donc un guillemet simple) non échappée donc il y aura erreur et la saisie ne sera pas ajoutée à la table.
Il aurait fallu que l'utilisateur saisisse la chaine : l\'info du jour ! Mais peut-on exiger cela de l'utilisateur ?

Mais il y a beaucoup plus grave.
Imaginons qu'un administrateur (voulant montrer sa science en informatique (ou ennuyer) le webmaster) saisisse dans le champ titre un script JavaScript tel : <script> location.href =https://www.xvideos.com; </script >.
Le visiteur suivant, voulant consulter les dernières infos, va en fait se retrouver sur le pornographique !!!
L'internaute mal intentionné aurait pu aussi saisir une instruction HTML du genre
<a href ="https://www.xvideos.com"></a>
Donc le code PHP proposé a une grosse, très grosse faille de sécurité.

La solution

Le code HTML du formulaire ne change pas. Par contre dans le code PHP il faut rajouter certaines fonctions. ... // code PHP 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.

La fonction addslashes permet d'échapper les guillemets simples et doubles c'est à dire de remplacer ' par \' et " par \".
Ainsi l'utilisateur pourra saisir des guillemets dans le titre et le message sans qu'il y ait plantage.

La fonction htmlentities évite la faille de sécurité évoquée plus haut.
En effet grâce à cette fonction certains caractères et en particulier les chevrons sont remplacés par leur entité de caractère et donc l'instruction saisie par l'internaute malveillant, n'est plus exécutable.

Page pour supprimer une info

Le code correspondant

// code PHP
$selection = $bdd->query("SELECT * FROM news ORDER BY id DESC limit 5") ;
while($record = $selection->fetch())
{
	echo "<div>"; 
	$id = $record['id'];
	$date_new = $record['date_new'];
	$titre = $record['titre'];
	$message = $record['message'];
	echo "<h2>ID : $id</h2>";
	echo "<p>titre :  $titre</p>"; 
	echo "<p> info du :  $date_new</p>"; 
	echo "<p>Contenu : $message</p>" ; 
	echo "</div>"; 
} // fin tant que
// fin code PHP
<form action='#' method='post'>
<label>ID de l'info à supprimer</label>	<input type='text' name='id' required />
<label></label><button type='submit' name='submit'>Validez</button>
</form>
...
// code PHP
if(isset($_POST['id']))
{	
// requête qui supprime un enregistrement dans la table news
	$id = $_POST['id'];
	$bdd->exec("DELETE FROM news WHERE id ='$id' ") ;
}
...

On affiche 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 ==) !

Page pour mettre à jour une info

Le code de la page qui permet de modifier le titre et le message d'une new (extrait) suite à une erreur de saisie. ... <p>Suite à une modification actualisez la page pour constater la modification ! <h2>Liste actuelle des news</h2> // code PHP $selection = $bdd->query("SELECT * FROM news ORDER BY id DESC limit 5") ; while($record = $selection->fetch()) { echo "<div>"; $id = $record['id']; $date_new = $record['date_new']; $titre = $record['titre']; $message = $record['message']; echo "<h2>ID : $id</h2>"; echo "<p>titre : $titre</p>"; echo "<p> info du : $date_new</p>"; echo "<p>Contenu : $message</p>" ; echo "</div>"; } // fin tant que // fin PHP <form action='#' method='post'> <label>ID de l'info à modifier</label> <input type='text' name='id' required /> <label>Nouveau titre de la new</label> <input type='text' name='titre' required /> <label>Nouveau texte</label> <textarea name='message' required></textarea> <label></label><button type='submit' name='submit'>Validez</button> </form> // code 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' ") ; } ...

Pour ne pas trop complexifier le code, l'administrateur doit ici, resaisir le titre et le message.
Dans la réalité il faudrait imaginer une solution plus ergonomique ...

Traduction

Débuter en programmation web - sommaire

Initiation au HTML & CSS

Découverte de PHP & SQL

Débuter en programmation web - recherche

L'auteur : Patrick Darcheville