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());
?><?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']));
?><?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));
}
?><?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());
?><?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
