Les requêtes préparées
La base de données MySQL supporte les requêtes préparées. Une requête préparée ou requête paramétrable est utilisée pour exécuter la même requête plusieurs fois, avec une grande efficacité.
Flux de travail de base
L'exécution d'une requête préparée se déroule en deux étapes : la préparation et l'exécution. Lors de la préparation, un template de requête est envoyé au serveur de base de données. Le serveur effectue une vérification de la syntaxe, et initialise les ressources internes du serveur pour une utilisation ultérieure.
Le serveur MySQL supporte le mode anonyme, avec des marqueurs de position utilisant le caractère ?.
Exemple #1 Première étape : la préparation
<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
echo "Echec lors de la connexion à MySQL : (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
/* Non-prepared statement */
if (!$mysqli->query("DROP TABLE IF EXISTS test") || !$mysqli->query("CREATE TABLE test(id INT)")) {
echo "Echec lors de la création de la table : (" . $mysqli->errno . ") " . $mysqli->error;
}
/* Prepared statement, stage 1: prepare */
if (!($stmt = $mysqli->prepare("INSERT INTO test(id) VALUES (?)"))) {
echo "Echec de la préparation : (" . $mysqli->errno . ") " . $mysqli->error;
}
?>
La préparation est suivie de l'exécution. Pendant l'exécution, le client lie les valeurs des paramètres et les envoie au serveur. Le serveur crée une requête depuis le template et y lie les valeurs pour l'exécution, en utilisant les ressources internes créées précédemment.
Exemple #2 Seconde étape : lie et exécute
<?php
/* Requête préparée, seconde étape : lie les valeurs et exécute la requête */
$id = 1;
if (!$stmt->bind_param("i", $id)) {
echo "Echec lors du liage des paramètres : (" . $stmt->errno . ") " . $stmt->error;
}
if (!$stmt->execute()) {
echo "Echec lors de l'exécution : (" . $stmt->errno . ") " . $stmt->error;
}
?>
Exécution répétée
Une requête préparée peut être exécutée à plusieurs reprises. A chaque exécution, la valeur courante de la variable liée est évaluée, et envoyée au serveur. La requête n'est pas analysée de nouveau. Le template de requête n'est pas une nouvelle fois envoyée au serveur non plus.
Exemple #3 Requête de type INSERT préparée une seule fois, et exécutée plusieurs fois
<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
echo "Echec de la connexion à MySQL : (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
/* Requête non préparée */
if (!$mysqli->query("DROP TABLE IF EXISTS test") || !$mysqli->query("CREATE TABLE test(id INT)")) {
echo "Echec lors de la création de la table : (" . $mysqli->errno . ") " . $mysqli->error;
}
/* Requête préparée, étape 1 : la préparation */
if (!($stmt = $mysqli->prepare("INSERT INTO test(id) VALUES (?)"))) {
echo "Echec lors de la préparation : (" . $mysqli->errno . ") " . $mysqli->error;
}
/* Requête préparée, étape 2 : lie les valeurs et exécute la requête */
$id = 1;
if (!$stmt->bind_param("i", $id)) {
echo "Echec lors du liage des paramètres : (" . $stmt->errno . ") " . $stmt->error;
}
if (!$stmt->execute()) {
echo "Echec lors de l'exécution de la requête : (" . $stmt->errno . ") " . $stmt->error;
}
/* Requête préparée : on exécute la requête de nouveau, seules les données sont transférées depuis le client vers le serveur */
for ($id = 2; $id < 5; $id++) {
if (!$stmt->execute()) {
echo "Echec lors de l'exécution de la requête : (" . $stmt->errno . ") " . $stmt->error;
}
}
/* Il est recommandé de fermer explicitement */
$stmt->close();
/* Requête non préparée */
$res = $mysqli->query("SELECT id FROM test");
var_dump($res->fetch_all());
?>
L'exemple ci-dessus va afficher :
array(4) {
[0]=>
array(1) {
[0]=>
string(1) "1"
}
[1]=>
array(1) {
[0]=>
string(1) "2"
}
[2]=>
array(1) {
[0]=>
string(1) "3"
}
[3]=>
array(1) {
[0]=>
string(1) "4"
}
}
Chaque requête préparée occupe des ressources sur le serveur. Elles doivent être fermées explicitement immédiatement après utilisation. Si vous ne le faîtes pas, la requête sera fermée lorsque le gestionnaire de requête sera libéré par PHP.
L'utilisation de requête préparée n'est pas toujours la façon la plus efficace d'exécuter une requête. Une requête préparée exécutée une seule fois provoque plus d'aller-retour client-serveur qu'une requête non préparée. C'est pour cela que la requête de type SELECT n'est pas exécutée comme requête préparée dans l'exemple ci-dessus.
De plus, vous devez prendre en considération l'utilisation des syntaxes multi-INSERT MySQL pour les INSERTs. Par exemple, les multi-INSERs requièrent moins d'aller-retour client-serveur que la requête préparée vue dans l'exemple ci-dessus.
Exemple #4 Moins d'aller-retour en utilisant les multi-INSERTs SQL
<?php
if (!$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3), (4)")) {
echo "Echec lors du Multi-INSERT : (" . $mysqli->errno . ") " . $mysqli->error;
}
?>
Types de données des valeurs du jeu de résultats
Le protocole serveur client MySQL définit un protocole de transfert des données différent pour les requêtes préparées et pour les requêtes non préparées. Les requêtes préparées utilisent un protocole appelé binaire. Le serveur MySQL envoie les données du jeu de résultats "tel que", au format binaire. Les résultats ne sont pas linéarisés en chaînes de caractères avant envoi. La bibliothèque cliente ne reçoit donc pas que des chaînes de caractères. Au lieu de cela, elle va recevoir des données binaires, et tentera de les convertir en valeurs dans un type de données PHP approprié. Par exemple, les résultats depuis une colonne INT SQL seront fournis comme variables de type entier PHP.
Exemple #5 Types de données natifs
<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
echo "Echec lors de la connexion à MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
!$mysqli->query("CREATE TABLE test(id INT, label CHAR(1))") ||
!$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'a')")) {
echo "Echec lors de la création de la table : (" . $mysqli->errno . ") " . $mysqli->error;
}
$stmt = $mysqli->prepare("SELECT id, label FROM test WHERE id = 1");
$stmt->execute();
$res = $stmt->get_result();
$row = $res->fetch_assoc();
printf("id = %s (%s)\n", $row['id'], gettype($row['id']));
printf("label = %s (%s)\n", $row['label'], gettype($row['label']));
?>
L'exemple ci-dessus va afficher :
id = 1 (integer) label = a (string)
Ce comportement diffère pour les requêtes non préparées. Par défaut, les requêtes non préparées retournent tous les résultats sous forme de chaînes de caractères. Ce comportement par défaut peut être modifié en utilisant une option lors de la connexion. Si cette option est utilisée, alors il n'y aura plus de différence entre une requête préparée et une requête non préparée.
Récupération des résultats en utilisant des variables liées
Les résultats depuis les requêtes préparées peuvent être récupérées en liant les variables de sortie, ou en interrogeant l'objet mysqli_result.
Les variables de sortie doivent être liées après l'exécution de la requête. Une variable doit être liée pour chaque colonne du jeu de résultats de la requête.
Exemple #6 Liage des variables de sortie
<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
echo "Echec lors de la connexion à MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
!$mysqli->query("CREATE TABLE test(id INT, label CHAR(1))") ||
!$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'a')")) {
echo "Echec lors de la création de la table : (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!($stmt = $mysqli->prepare("SELECT id, label FROM test"))) {
echo "Echec lors de la préparation de la requête : (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!$stmt->execute()) {
echo "Echec lors de l'exécution de la requête : (" . $mysqli->errno . ") " . $mysqli->error;
}
$out_id = NULL;
$out_label = NULL;
if (!$stmt->bind_result($out_id, $out_label)) {
echo "Echec lors du liage des paramètres de sortie : (" . $stmt->errno . ") " . $stmt->error;
}
while ($stmt->fetch()) {
printf("id = %s (%s), label = %s (%s)\n", $out_id, gettype($out_id), $out_label, gettype($out_label));
}
?>
L'exemple ci-dessus va afficher :
id = 1 (integer), label = a (string)
Les requêtes préparées retournent des jeux de résultats non mis en mémoire tampon par défaut. Les résultats de la requête ne sont pas implicitement récupérés et transférés depuis le serveur vers le client pour une mise en mémoire tampon côté client. Le jeu de résultats prend des ressources serveur tant que tous les résultats n'ont pas été récupérés par le client. Aussi, il est recommandé de les récupérer rapidement. Si un client échoue dans la récupération de tous les résultats, ou si le client ferme la requête avant d'avoir récupéré toutes les données, les données doivent être récupérées implicitement par mysqli.
Il est également possible de mettre en mémoire tampon les résultats d'une requête préparée en utilisant la fonction mysqli_stmt_store_result().
Récupération des résultats en utilisant l'interface mysqli_result
Au lieu d'utiliser des résultats liés, les résultats peuvent aussi être récupérées via l'interface mysqli_result. La fonction mysqli_stmt_get_result() retourne un jeu de résultats mis en mémoire tampon.
Exemple #7 Utilisation de mysqli_result pour récupérer les résultats
<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
echo "Echec lors de la connexion à MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
!$mysqli->query("CREATE TABLE test(id INT, label CHAR(1))") ||
!$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'a')")) {
echo "Echec lors de la création de la table : (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!($stmt = $mysqli->prepare("SELECT id, label FROM test ORDER BY id ASC"))) {
echo "Echec lors de la préparation de la requête : (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!$stmt->execute()) {
echo "Echec lors de l'exécution de la requête : (" . $stmt->errno . ") " . $stmt->error;
}
if (!($res = $stmt->get_result())) {
echo "Echec lors de la récupération du jeu de résultats : (" . $stmt->errno . ") " . $stmt->error;
}
var_dump($res->fetch_all());
?>
L'exemple ci-dessus va afficher :
array(1) {
[0]=>
array(2) {
[0]=>
int(1)
[1]=>
string(1) "a"
}
}
L'utilisation de mysqli_result interface offre d'autres avantages d'un point de vue flexibilité dans la navigation dans le jeu de résultats côté client.
Exemple #8 Jeu de résultats mis en mémoire tampon pour plus de flexibilité dans la lecture
<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
echo "Echec lors de la connexion à MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
!$mysqli->query("CREATE TABLE test(id INT, label CHAR(1))") ||
!$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'a'), (2, 'b'), (3, 'c')")) {
echo "Echec lors de la création de la table : (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!($stmt = $mysqli->prepare("SELECT id, label FROM test"))) {
echo "Echec lors de la préparation de la requête : (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!$stmt->execute()) {
echo "Echec lors de l'exécution de la requête : (" . $stmt->errno . ") " . $stmt->error;
}
if (!($res = $stmt->get_result())) {
echo "Echec lors de la récupération du jeu de résultats : (" . $stmt->errno . ") " . $stmt->error;
}
for ($row_no = ($res->num_rows - 1); $row_no >= 0; $row_no--) {
$res->data_seek($row_no);
var_dump($res->fetch_assoc());
}
$res->close();
?>
L'exemple ci-dessus va afficher :
array(2) {
["id"]=>
int(3)
["label"]=>
string(1) "c"
}
array(2) {
["id"]=>
int(2)
["label"]=>
string(1) "b"
}
array(2) {
["id"]=>
int(1)
["label"]=>
string(1) "a"
}
Echappement et injection SQL
Les variables liées sont envoyées au serveur séparément de la requête, ne pouvant ainsi pas interférer avec celle-ci. Le serveur utilise ces valeurs directement au moment de l'exécution, après que le template ne soit analysé. Les paramètres liés n'ont pas besoin d'être échappés sachant qu'ils ne sont jamais placés dans la chaîne de requête directement. Une astuce doit être fournie au serveur pour spécifier le type de variable liée, afin d'effectuer la conversion appropriée. Voir la fonction mysqli_stmt_bind_param() pour plus d'informations.
Une telle séparation est souvent considérée comme la seule fonctionalité pour se protéger des injections SQL, mais le même degré de sécurité peut être atteint avec les requêtes non-préparées, si toutes les valeurs sont correctement formatées. Notez qu'un formattage correct n'est pas la même chose qu'un échappement et nécessite plus de logique qu'un simple échappement. Aussi, les requêtes préparées sont simplement une méthode plus simple et moins prompte aux erreurs concernant cette approche sécuritaire.
Émulation côté client de la préparation d'une requête
L'API n'inclut pas d'émulation côté client de la préparation d'une requête.
Préparation rapide - comparaison de requêtes non préparées
Le table ci-dessous compare les requêtes préparées côté serveur et les requêtes non préparées.
| Requête préparée | Requête non préparée | |
|---|---|---|
| Aller-retour client serveur, SELECT, une seule exécution | 2 | 1 |
| Chaîne de la requête transférée depuis le client vers le serveur | 1 | 1 |
| Aller-retour client serveur, SELECT, (n) exécution | 1 + n | n |
| Chaîne de la requête transférée depuis le client vers le serveur | 1 template, liage des paramètres n fois, s'il y en a | n fois avec les paramètres, s'il y en a |
| API de liage des paramètres d'entrée | Oui, échappement automatique des entrées | Non, échappement manuelle des entrées |
| API de liage des paramètres de sortie | Oui | Non |
| Support de l'utilisation de l'API mysqli_result | Oui, utilisation de mysqli_stmt_get_result() | Oui |
| Jeu de résultats mis en mémoire tampon | Oui, utilisation de mysqli_stmt_get_result() ou liage avec mysqli_stmt_store_result() | Oui, par défaut avec la fonction mysqli_query() |
| Jeu de résultats non mis en mémoire tampon | Oui, utiilsation de l'API liage de la sortie | Oui, utilisation de mysqli_real_query() avec mysqli_use_result() |
| Saveur du protocole de transfert des données client serveur MySQL | Protocole binaire | Protocole texte |
| Types de données des valeurs du jeu de résultats SQL | Préservé lors de la récupération | Converti en chaînes ou préservé lors de la récupération |
| Supporte toutes les requêtes SQL | Les versions récentes de MySQL supportent la plupart, mais pas toute | Oui |
Voir aussi
Version en cache
05/11/2024 09:41:16 Cette version de la page est en cache (à la date du 05/11/2024 09:41:16) afin d'accélérer le traitement. Vous pouvez activer le mode utilisateur dans le menu en haut pour afficher la dernère version de la page.Document créé le 30/01/2003, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/php-rf-mysqli.quickstart.prepared-statements.html
L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.
Références
Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.
