Télécharger le PDF du cours
Préparer ses requêtes comporte des avantages notables notamment dans le cas où l’on doit exécuter un grand nombre de fois une même requête ou si l’on doit insérer des données envoyées par les utilisateurs.
Préparer ses requêtes : comment ça marche ?
Si vous devez exécuter des requêtes similaires plusieurs fois d’affilée, il va alors être très intéressant d’utiliser ce qu’on appelle des requêtes préparées.
Les requêtes préparées sont des requêtes qui vont être créées en trois temps : la préparation, la compilation et l’exécution.
Tout d’abord, une première phase de préparation dans laquelle nous allons créer un template ou schéma de requête, en ne précisant pas les valeurs réelles dans notre requête mais en utilisant plutôt des marqueurs nommés (sous le forme :nom) ou des marqueurs interrogatifs (sous la forme ?).
Ces marqueurs nommés ou interrogatifs (qu’on peut plus globalement nommer marqueurs de paramètres) vont ensuite être remplacés par les vraies valeurs lors de l’exécution de la requête. Notez que vous ne pouvez pas utiliser les marqueurs nommés et les marqueurs interrogatifs dans une même requête SQL, il faudra choisir l’un ou l’autre.
Une fois le template créé, la base de données va analyser, compiler, faire des optimisations sur notre template de requête SQL et va stocker le résultat sans l’exécuter.
Finalement, nous allons lier des valeurs à nos marqueurs et la base de données va exécuter la requête. Nous allons pouvoir réutiliser notre template autant de fois que l’on souhaite en liant de nouvelles valeurs à chaque fois.
Utiliser des requêtes préparées va nous offrir deux principaux avantages par rapport à l’exécution directe de requêtes SQL :
- Nous allons gagner en performance puisque la préparation de nos requêtes ne va être faite qu’une fois quel que soit le nombre d’exécutions de notre requête ;
- Le risque d’injection SQL est minimisé puisque notre requête est pré-formatée et nous n’avons donc pas besoin de protéger nos paramètres ou valeurs manuellement.
Un premier point sur l’injection SQL
Jusqu’à présent, nous avons fourni nous-mêmes les valeurs à insérer en base de données. Cependant, en pratique, nous allons très souvent stocker et manipuler des données envoyées directement par les utilisateurs.
Le gros souci par rapport aux données envoyées par les utilisateurs est que vous devez toujours vous en méfier : vous n’êtes jamais à l’abri d’une étourderie ou d’un comportement volontairement malfaisant.
Jusqu’ici, nos requêtes n’étaient pas du tout protégées contre ce type de comportements. Pour bien comprendre cela, imaginez que vous récupériez les valeurs à insérer dans notre table Clients créée précédemment à partir d’un formulaire.
Vous demandez donc aux utilisateurs de rentrer leur nom, prénom, adresse, etc. Sans plus de vérification, rien n’empêche un utilisateur d’envoyer une valeur qui va soit faire planter notre script soit éventuellement altérer notre base de données.
<!DOCTYPE html>
<html>
<head>
<title>Cours PHP / MySQL</title>
<meta charset="utf-8">
</head>
<body>
<h1>Bases de données MySQL</h1>
<?php
$servname = 'localhost';
$dbname = 'pdodb';
$user = 'root';
$pass = 'root';
/*On imagine qu'on récupère les valeurs suivantes à partir d'un formulaire envoyé
*par les utilisateurs*/
$nom = "Richard";
$prenom = "Pierre";
$adresse = "Rue de la Chèvre";
$ville = "Toulon";
$cp = 83000;
$pays = "France";
$mail = "'gg@gmail.com'),('a','b','c','d',1,'e','f'";
try{
$dbco = new PDO("mysql:host=$servname;dbname=$dbname", $user, $pass);
$dbco->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbco->beginTransaction();
$sql = "INSERT INTO Clients(Nom,Prenom,Adresse,Ville,Codepostal,Pays,Mail)
VALUES($nom,$prenom,$adresse,$ville,$cp,$pays,$mail)";
$dbco->exec($sql);
echo 'Entrée ajoutée dans la table';
}
catch(PDOException $e){
$dbco->rollBack();
echo "Erreur : " . $e->getMessage();
}
?>
</body>
</html>
Ici, nous avons précisé une valeur habile dans notre variable $mail qui nous a permis d’insérer deux entrées d’un coup en utilisant une syntaxe non recommandée mais qui fonctionne toujours qui précise les différentes valeurs des entrées à insérer en séparant les groupes par des virgules.
En effet, regardons plus attentivement ce que ça donne lorsqu’on remplace notre variable par son contenu dans le code ci-dessus.
$sql = "INSERT INTO Clients(Nom,Prenom,Adresse,Ville,Codepostal,Pays,Mail)
VALUES($nom,$prenom,$adresse,$ville,$cp,$pays,'gg@gmail.com'),
('a','b','c','d',1,'e','f')";
Dans ce cas-là, deux nouvelles entrées vont être insérées dans notre table. Ici, nous restons dans la catégorie des cas « gênants » mais non dangereux.
Cependant, rien n’aurait empêché cet utilisateur d’insérer une autre commande SQL pour lire le contenu de notre base de données, la modifier ou encore la supprimer !
Pour cette raison, vous devez une nouvelle fois faire toujours très attention dès que vous recevez des données utilisateur et ajouter différents niveaux de sécurité (sécuriser ses formulaires en utilisant les regex, neutraliser les injections en PHP, préparer ses requêtes, etc.).
Les méthodes execute(), bindParam() et bindValue()
Pour exécuter une requête préparée, nous allons cette fois-ci devoir utiliser la méthode execute() et non plus exec() comme on utilisait depuis le début de cette partie.
En utilisant des marqueurs dans nos requêtes préparées, nous allons avoir deux grandes options pour exécuter la méthode execute() :
- On va pouvoir lui passer un tableau de valeurs de paramètres (uniquement en entrée) ;
- On va pouvoir d’abord appeler les méthodes
bindParam()oubindValue()pour respectivement lier des variables ou des valeurs à nos marqueurs puis ensuite exécuterexecute().
Pas d’inquiétude, je vous explique immédiatement les différences concrètes entre ces méthodes et les cas d’utilisation !
Commencez déjà par noter que passer un tableau directement en valeur de execute() devrait être considéré comme la méthode par défaut puisque c’est finalement la plus simple et que tout va fonctionner normalement dans l’immense majorité des cas.
En fait, execute(array) est une méthode d’écriture raccourcie ; l’idée derrière cela est qu’une boucle va être exécutée en tâche de fond dont l’objet va être d’appeler bindValue() sur chacun des éléments du tableau.
En utilisant execute(array), les valeurs vont être liées en tant que type String excepté pour le type NULL qui restera inchangé. Cela va fonctionner une nouvelle fois dans l’immense majorité des cas.
Cependant, dans de rares cas, il sera utile de définir explicitement le type de données. Les cas les plus fréquents sont les suivants :
- Notre requête contient une clause LIMIT ou toute autre clause qui ne peut pas accepter une valeur de type String et le mode emulation est activé (ON) ;
- Notre table contient des colonnes avec un type particulier qui n’accepte que des valeurs d’un certain type (comme les colonnes de type
BOOLEANouBIGINTpar exemple).
Dans ces cas-là, il sera alors préférable de lier les variables avant d’utiliser execute() si vous voulez avoir le résultat attendu.
Quelle différence maintenant entre bindParam() et bindValue() ?
La méthode bindParam() va lier un paramètre à un nom de variable spécifique et la variable va être liée en tant que référence et ne sera évaluée qu’au moment de l’appel à la méthode execute().
Si la variable change de valeur entre l’appel à la méthode bindParam() et l’appel à la méthode execute(), c’est donc la dernière valeur qui sera utilisée.
La méthode bindValue() va elle associer directement une valeur à un paramètre.
La méthode bindParam() fonctionne avec deux paramètres obligatoires et trois facultatifs dont un qui va particulièrement nous intéresser :
- Un identifiant (obligatoire) qui sera de la forme
:nomsi on utilise des marqueurs nommés ou qui sera l’index de base 1 du paramètre si on utilise un marqueur interrogatif ; - Le nom de la variable PHP (obligatoire) à lier au paramètre de la requête SQL ;
- Le type de données explicite pour le paramètre (facultatif) spécifié en utilisant les constantes
PDO::PARAM_*constants.
Les constantes prédéfinies les plus utilisées sont les suivantes :
PDO ::PARAM_STR, qui représente le type de données CHAR, VARCHAR et les autres types de données « chaine de caractères » SQL ;PDO ::PARAM_INT, qui représente le type de données SQL INTEGER (nombre entier) ;PDO ::PARAM_NULL, qui représente le type de données SQL NULL ;PDO ::PARAM_BOOL, qui représente le type de données booléen.
Pour la liste complète des constantes, vous pouvez consulter la documentation officielle ici.
La méthode bindValue() va fonctionner également avec deux paramètres obligatoires et un facultatif :
- Un identifiant (obligatoire) qui sera de la forme
:nomsi on utilise des marqueurs nommés ou qui sera l’index de base 1 du paramètre si on utilise un marqueur interrogatif ; - La valeur à associer au paramètre (obligatoire) ;
- Le type de données explicite pour le paramètre (facultatif) spécifié en utilisant les constantes
PDO::PARAM_*constants.
execute() bindParam() et bindValue() appartiennent toutes les trois à la classe PDOStatement et non pas à la classe PDO. La classe PDOStatement représente une requête préparée et, une fois exécutée, l’ensemble des résultats associés.
Exemples pratiques de requêtes préparées
Reprenons notre table Clients et tentons d’insérer de nouvelles entrées en utilisant les requêtes préparées. Nous allons passer en revue les différentes façons de faire expliquées précédemment.
Avec execute(array) et des marqueurs nommés
Commençons déjà en préparant une requête avec des marqueurs nommés puis en l’exécutant avec execute(array) :
<!DOCTYPE html>
<html>
<head>
<title>Cours PHP / MySQL</title>
<meta charset='utf-8'>
</head>
<body>
<h1>Bases de données MySQL</h1>
<?php
$servname = "localhost"; $dbname = "pdodb"; $user = "root"; $pass = "root";
try{
$dbco = new PDO("mysql:host=$servname;dbname=$dbname", $user, $pass);
$dbco->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$nom = "Flo";
$prenom = "Dechand";
$adresse = "Rue des Moulins";
$ville = "Marseille";
$cp = 13001;
$pays = "France";
$mail = "flodc@gmail.com";
//$sth appartient à la classe PDOStatement
$sth = $dbco->prepare("
INSERT INTO Clients(Nom,Prenom,Adresse,Ville,Codepostal,Pays,Mail)
VALUES (:nom, :prenom, :adresse, :ville, :cp, :pays, :mail)
");
$sth->execute(array(
':nom' => $nom,
':prenom' => $prenom,
':adresse' => $adresse,
':ville' => $ville,
':cp' => $cp,
':pays' => $pays,
':mail' => $mail));
echo "Entrée ajoutée dans la table";
}
catch(PDOException $e){
echo "Erreur : " . $e->getMessage();
}
?>
</body>
</html>
Ici, on commence par préparer notre requête SQL grâce à la méthode prepare() qui appartient à la classe PDOStatement. On place le résultat dans un objet $sth.
Notez qu’on remplace bien les valeurs dans notre requête SQL par nos marqueurs nommés (sans les entourer d’apostrophes).
On appelle ensuite execute() en lui passant un tableau composé de nos marqueurs et des variables associées.
Avec execute(array) et des marqueurs interrogatifs
Le principe va être relativement similaire à ce que l’on vient de faire, à part que nous allons cette fois-ci utiliser des marqueurs interrogatifs :
<!DOCTYPE html>
<html>
<head>
<title>Cours PHP / MySQL</title>
<meta charset='utf-8'>
</head>
<body>
<h1>Bases de données MySQL</h1>
<?php
$servname = "localhost"; $dbname = "pdodb"; $user = "root"; $pass = "root";
try{
$dbco = new PDO("mysql:host=$servname;dbname=$dbname", $user, $pass);
$dbco->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$nom = "Tom";
$prenom = "Dubois";
$adresse = "Rue du Chene";
$ville = "Nice";
$cp = 06000;
$pays = "France";
$mail = "duboistom@gmail.com";
//$sth appartient à la classe PDOStatement
$sth = $dbco->prepare("
INSERT INTO Clients(Nom,Prenom,Adresse,Ville,Codepostal,Pays,Mail)
VALUES (?, ?, ?, ?, ?, ?, ?)
");
$sth->execute(array($nom, $prenom, $adresse, $ville, $cp, $pays, $mail));
echo "Entrée ajoutée dans la table";
}
catch(PDOException $e){
echo "Erreur : " . $e->getMessage();
}
?>
</body>
</html>
En utilisant bindValue et des marqueurs nommés
Nous allons cette fois-ci associer des valeurs à des paramètres en utilisant bindValue() et des marqueurs nommés :
<!DOCTYPE html>
<html>
<head>
<title>Cours PHP / MySQL</title>
<meta charset='utf-8'>
</head>
<body>
<h1>Bases de données MySQL</h1>
<?php
$servname = "localhost"; $dbname = "pdodb"; $user = "root"; $pass = "root";
try{
$dbco = new PDO("mysql:host=$servname;dbname=$dbname", $user, $pass);
$dbco->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$nom = "Laura";
$prenom = "Dubois";
$adresse = "Rue du Chene";
$ville = "Nice";
$cp = 06000;
$pays = "France";
$mail = "lauradb@gmail.com";
//$sth appartient à la classe PDOStatement
$sth = $dbco->prepare("
INSERT INTO Clients(Nom,Prenom,Adresse,Ville,Codepostal,Pays,Mail)
VALUES (:nom, :prenom, :adresse, :ville, :cp, :pays, :mail)
");
//La constante de type par défaut est STR
$sth->bindValue(':nom', $nom);
$sth->bindValue(':prenom', $prenom);
$sth->bindValue(':adresse', $adresse);
$sth->bindValue(':ville', $ville);
$sth->bindValue(':cp', $cp, PDO::PARAM_INT);
$sth->bindValue(':pays', $pays);
$sth->bindValue(':mail', $mail);
$sth->execute();
echo "Entrée ajoutée dans la table";
}
catch(PDOException $e){
echo "Erreur : " . $e->getMessage();
}
?>
</body>
</html>
On ajoute une étape ici en utilisant bindValue() pour lier des valeurs à des paramètres. Une fois toutes les valeurs liées, nous n’avons plus qu’à appeler execute() pour exécuter notre requête.
En utilisant bindValue et des marqueurs interrogatifs
Même principe que précédemment mais cette fois-ci avec des marqueurs interrogatifs :
<!DOCTYPE html>
<html>
<head>
<title>Cours PHP / MySQL</title>
<meta charset='utf-8'>
</head>
<body>
<h1>Bases de données MySQL</h1>
<?php
$servname = "localhost"; $dbname = "pdodb"; $user = "root"; $pass = "root";
try{
$dbco = new PDO("mysql:host=$servname;dbname=$dbname", $user, $pass);
$dbco->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$nom = "Mathilde";
$prenom = "Palaz";
$adresse = "Rue des Cerisiers";
$ville = "Rouen";
$cp = 76000;
$pays = "France";
$mail = "mathplz@gmail.com";
//$sth appartient à la classe PDOStatement
$sth = $dbco->prepare("
INSERT INTO Clients(Nom,Prenom,Adresse,Ville,Codepostal,Pays,Mail)
VALUES (?, ?, ?, ?, ?, ?, ?)
");
//La constante de type par défaut est STR
$sth->bindValue(1, $nom);
$sth->bindValue(2, $prenom);
$sth->bindValue(3, $adresse);
$sth->bindValue(4, $ville);
$sth->bindValue(5, $cp, PDO::PARAM_INT);
$sth->bindValue(6, $pays);
$sth->bindValue(7, $mail);
$sth->execute();
echo "Entrée ajoutée dans la table";
}
catch(PDOException $e){
echo "Erreur : " . $e->getMessage();
}
?>
</body>
</html>
Notez qu’on précise cette fois ci l’index de base 1 du paramètre dans notre méthode bindValue().
En utilisant bindParam et des marqueurs nommés ou interrogatifs
On peut finalement lier nos variables à des marqueurs avec bindParam() dans nos requêtes préparées.
<!DOCTYPE html>
<html>
<head>
<title>Cours PHP / MySQL</title>
<meta charset='utf-8'>
</head>
<body>
<h1>Bases de données MySQL</h1>
<?php
$servname = "localhost"; $dbname = "pdodb"; $user = "root"; $pass = "root";
try{
$dbco = new PDO("mysql:host=$servname;dbname=$dbname", $user, $pass);
$dbco->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$nom = "Jean";
$prenom = "Bombeur";
$adresse = "Rue des Bouchers";
$ville = "Toulouse";
$cp = 31000;
$pays = "France";
$mail = "jbb@gmail.com";
$sth = $dbco->prepare("
INSERT INTO Clients(Nom,Prenom,Adresse,Ville,Codepostal,Pays,Mail)
VALUES (:nom, :prenom, :adresse, :ville, :cp, :pays, :mail)
");
//La constante de type par défaut est STR
$sth->bindParam(':nom', $nom);
$sth->bindParam(':prenom', $prenom);
$sth->bindParam(':adresse', $adresse);
$sth->bindParam(':ville', $ville);
$sth->bindParam(':cp', $cp, PDO::PARAM_INT);
$sth->bindParam(':pays', $pays);
$sth->bindParam(':mail', $mail);
$cp = 31001;
$sth->execute();
echo "Entrée ajoutée dans la table";
}
catch(PDOException $e){
echo "Erreur : " . $e->getMessage();
}
?>
</body>
</html>
Ici, on utilise des marqueurs nommés. Notez bien une nouvelle fois que les variables sont ici liées en tant que références et ne sont évaluées qu’au moment où on appelle execute().
Ainsi, si on modifie la valeur stockée dans une variable entre le moment où on appelle bindParam() et celui où on appelle execute(), c’est bien la dernière valeur qui va être retenue au contraire de si on utilisait bindValue().
On peut également utiliser bindParam avec des marqueurs interrogatifs :
<!DOCTYPE html>
<html>
<head>
<title>Cours PHP / MySQL</title>
<meta charset='utf-8'>
</head>
<body>
<h1>Bases de données MySQL</h1>
<?php
$servname = "localhost"; $dbname = "pdodb"; $user = "root"; $pass = "root";
try{
$dbco = new PDO("mysql:host=$servname;dbname=$dbname", $user, $pass);
$dbco->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$nom = "Gérard";
$prenom = "Philippe";
$adresse = "Impasse des sans Noms";
$ville = "Nantes";
$cp = 44000;
$pays = "France";
$mail = "philou@gmail.com";
$sth = $dbco->prepare("
INSERT INTO Clients(Nom,Prenom,Adresse,Ville,Codepostal,Pays,Mail)
VALUES (?, ?, ?, ?, ?, ?, ?)
");
$sth->bindParam(1, $nom);
$sth->bindParam(2, $prenom);
$sth->bindParam(3, $adresse);
$sth->bindParam(4, $ville);
$sth->bindParam(5, $cp, PDO::PARAM_INT);
$sth->bindParam(6, $pays);
$sth->bindParam(7, $mail);
$sth->execute();
echo "Entrée ajoutée dans la table";
}
catch(PDOException $e){
echo "Erreur : " . $e->getMessage();
}
?>
</body>
</html>
Voilà tout pour les requêtes préparées ! Si ces nouvelles choses vous semblent floues, prenez le temps de relire ce chapitre et de refaire les exemples.
Dans tous les cas, nous allons beaucoup nous resservir des requêtes préparées par la suite donc vous devriez les assimiler et les maitriser rapidement.
