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é et protège des injections SQL.

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 ?.

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 exécute l'instruction avec les valeurs liées en utilisant les ressources internes précédemment créées.

Exemple #1 Première étape : la préparation

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");

// Requête non préparée
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");

// Requête préparée, étape 1 : préparation
$stmt = $mysqli->prepare("INSERT INTO test(id, label) VALUES (?, ?)");

// Requête préparée, étape 2 : lie les valeurs et exécute la requête
$id = 1;
$label = 'PHP';
$stmt->bind_param("is", $id, $label); // "is" means that $id is bound as an integer and $label as a string

$stmt->execute();

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 #2 Requête de type INSERT préparée une seule fois, et exécutée plusieurs fois

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");

// Requête non préparée
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");

// Requête préparée, étape 1 : la préparation
if (!($stmt = $mysqli->prepare("INSERT INTO test(id) VALUES (?)"))) {
     echo "Échec 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;
$stmt->bind_param("is", $id, $label); // "is" means that $id is bound as an integer and $label as a string

$data = [
    1 => 'PHP',
    2 => 'Java',
    3 => 'C++'
];

foreach ($data as $id => $label) {
    $stmt->execute();
}

$result = $mysqli->query('SELECT id, label FROM test');
var_dump($result->fetch_all(MYSQLI_ASSOC));

L'exemple ci-dessus va afficher :

array(3) {
  array(2) {
    ["id"]=>
    string(1) "1"
    ["label"]=>
    string(3) "PHP"
  }
  [1]=>
  array(2) {
    ["id"]=>
    string(1) "2"
    ["label"]=>
    string(4) "Java"
  }
  [2]=>
  array(2) {
    ["id"]=>
    string(1) "3"
    ["label"]=>
    string(3) "C++"
  }
}

Chaque requête préparée occupe des ressources sur le serveur. Elles doivent être fermées explicitement immédiatement après utilisation. Si l'on ne le fait 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, il faut prendre en considération l'utilisation des syntaxes multi-INSERT MySQL pour les INSERTs. Par exemple, les multi-INSERTs requièrent moins d'aller-retour client-serveur que la requête préparée vue dans l'exemple ci-dessus.

Exemple #3 Moins d'aller-retour en utilisant les multi-INSERTs SQL

<?php
 
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");
 
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT)");
 
$values = [1, 2, 3, 4];
 
$stmt = $mysqli->prepare("INSERT INTO test(id) VALUES (?), (?), (?), (?)");
$stmt->bind_param('iiii', ...$values);
$stmt->execute();

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 sérialisés en chaînes de caractères avant envoi. La bibliothèque cliente reçoit des données binaires et tente de convertir les valeurs en 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 #4 Types de données natifs

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");

// Requête non préparée
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'PHP')");

$stmt = $mysqli->prepare("SELECT id, label FROM test WHERE id = 1");
$stmt->execute();
$result = $stmt->get_result();
$row = $result->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 = PHP (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és 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 #5 Liage des variables de sortie

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");

// Requête non préparée
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'PHP')");

$stmt = $mysqli->prepare("SELECT id, label FROM test WHERE id = 1");
$stmt->execute();

$stmt->bind_result($out_id, $out_label);

$out_id    = NULL;
$out_label = NULL;
if (!$stmt->bind_result($out_id, $out_label)) {
    echo "Échec 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 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és via l'interface mysqli_result. La fonction mysqli_stmt::get_result() retourne un jeu de résultats mis en mémoire tampon.

Exemple #6 Utilisation de mysqli_result pour récupérer les résultats

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");

// Requête non préparée
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'PHP')");

$stmt = $mysqli->prepare("SELECT id, label FROM test WHERE id = 1");
$stmt->execute();

$result = $stmt->get_result();

var_dump($result->fetch_all(MYSQLI_ASSOC));

L'exemple ci-dessus va afficher :

array(1) {
  [0]=>
  array(2) {
    ["id"]=>
    int(1)
    ["label"]=>
    string(3) "PHP"
  }
}

L'utilisation de l'interface mysqli_result offre d'autres avantages d'un point de vue flexibilité dans la navigation dans le jeu de résultats côté client.

Exemple #7 Jeu de résultats mis en mémoire tampon pour plus de flexibilité dans la lecture

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");

// Requête non préparée
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'PHP'), (2, 'Java'), (3, 'C++')");

$stmt = $mysqli->prepare("SELECT id, label FROM test");
$stmt->execute();

$result = $stmt->get_result();

for ($row_no = $result->num_rows - 1; $row_no >= 0; $row_no--) {
    $result->data_seek($row_no);
    var_dump($result->fetch_assoc());
}

L'exemple ci-dessus va afficher :

array(2) {
  ["id"]=>
  int(3)
  ["label"]=>
  string(1) "C++"
}
array(2) {
  ["id"]=>
  int(2)
  ["label"]=>
  string(1) "Java"
}
array(2) {
  ["id"]=>
  int(1)
  ["label"]=>
  string(1) "PHP"
}

Échappement 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 fonctionnalité 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. À noter : qu'un formatage 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.

Voir aussi