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 seront échappées automatiquement par le serveur. Le serveur insère les valeurs échappées à la position appropriée dans le template de la requête avant son exécution. Une astuce doit être fournie au serveur pour le type de variable liée, afin de créer une conversion appropriée. Voir la documentation sur la fonction mysqli_stmt_bind_param() pour plus de détails.
L'échappement automatique des valeurs par le serveur est quelque fois considéré comme une fonctionnalité relative à la sécurité afin de prévenir les injections SQL. Le même degré de sécurité peut être atteint avec les requêtes non préparées, si les valeurs d'entrée sont échappées correctement.
É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