Les requêtes action : insert, update, delete

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 public 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 Javascript ou PHP !!!

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 SQL :

	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 de plus il fait fonction de PRIMARY KEY (clé primaire).
Mais ce champ 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 NULL est à OUI pour ces 6 champs.
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. Toutes les lignes 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 mais celle-ci subsiste ; elle est prête à recevoir de nouveaux enregistrements.

Pour vider une table on peut aussi utiliser TRUNCATE TABLE
La différence majeure étant que la commande TRUNCATE va ré-initialiser l’auto-incrémentation tandis que la commande DELETE ne ré-initialise pas l’auto-incrémentation.

Pour supprimer une table dans une base de données il faut utiliser la commande DROP TABLE .

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"

Page pour ajouter une info

Le code (extraits):

... <?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>"; } ?> <h1>Ajouter une info du club </h1> <form action='#' method='post'> <p class ="remarque">* : saisie obligatoire ! <br> <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 contenant entre autes la table news.
L'instruction de connexion n'apparaît pas dans l'extrait. Cette instruction a déjà été abordée dans un chapitre précédent ("Site avec 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 : 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.

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

Emploi 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 de la new 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 le script saisi n'est plus exécutable car le texte saisi devient :

&lt;script&gt; location.href =https://www.xvideos.com; &lt;/script &gt;

Les chevrons ont été remplacés par leur entité de caractère. Un script JS ou un lien HTML sont simplement affichés mais pas exécutés.

Page pour supprimer une info

Le code de la page (extrait) :

... <section> <h1>Supprimer une info</h1> <p class ="remarque">Suite à une suppression actualisez la page pour constater la suppression ! <h2>Liste des news</h2> <?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 ?> <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> <?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.

... <h1>Modifier une info</h1> <p class ="remarque">Suite à une modification actualisez la page pour constater la modification ! <h2>Liste actuelle des news</h2> <?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 ?> <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> <?php if(isset($_POST['id'])) { // requête qui modifie un modifie 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.
Mais on pourrait imaginer un choix ...
Retour menu