Requêtes préparées et procédures stockées

La plupart des bases de données supportent le concept des requêtes préparées. Qu'est-ce donc ? Il est possible de les voir comme une sorte de modèle compilé pour le SQL que l'on veut exécuter, qui peut être personnalisé en utilisant des variables en guise de paramètres. Les requêtes préparées offrent deux fonctionnalités essentielles :

La requête ne doit être analysée (ou préparée) qu'une seule fois, mais peut être exécutée plusieurs fois avec des paramètres identiques ou différents. Lorsque la requête est préparée, la base de données va analyser, compiler et optimiser son plan pour exécuter la requête. Pour les requêtes complexes, ce processus peut prendre assez de temps, ce qui peut ralentir les applications si l'on doit répéter la même requête plusieurs fois avec différents paramètres. En utilisant les requêtes préparées, l'on évite ainsi de répéter le cycle analyse/compilation/optimisation. Pour résumer, les requêtes préparées utilisent moins de ressources et s'exécutent plus rapidement.
Les paramètres pour préparer les requêtes n'ont pas besoin d'être entre guillemets ; le pilote gère cela. Si l'application utilise exclusivement les requêtes préparées, il est possible d'être sûr qu'aucune injection SQL n'est possible (Cependant, si l'on construit d'autres parties de la requête en se basant sur des entrées utilisateurs, l'on continue à prendre un risque).

Les requêtes préparées sont tellement pratiques que c'est l'unique fonctionnalité que PDO émule pour les pilotes qui ne les prennent pas en charge. Ceci assure de pouvoir utiliser la même technique pour accéder aux données, sans se soucier des capacités de la base de données.

Exemple #1 Insertions répétitives en utilisant les requêtes préparées

Cet exemple effectue une requête INSERT en y substituant un nom et une valeur pour les marqueurs nommés.

<?php
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);

// insertion d'une ligne
$name = 'one';
$value = 1;
$stmt->execute();

// insertion d'une autre ligne avec des valeurs différentes
$name = 'two';
$value = 2;
$stmt->execute();
?>

Exemple #2 Insertions répétées en utilisant des requêtes préparées

Cet exemple effectue une requête INSERT en y substituant un nom et une valeur pour les marqueurs ?.

<?php
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)");
$stmt->bindParam(1, $name);
$stmt->bindParam(2, $value);

// insertion d'une ligne
$name = 'one';
$value = 1;
$stmt->execute();

// insertion d'une autre ligne avec différentes valeurs
$name = 'two';
$value = 2;
$stmt->execute();
?>

Exemple #3 Récupération des données en utilisant des requêtes préparées

Cet exemple récupère des données basées sur la valeur d'une clé fournie par un formulaire. L'entrée utilisateur est automatiquement échappée, il n'y a donc aucun risque d'attaque par injection SQL.

<?php
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name = ?");
$stmt->execute([$_GET['name']]);
foreach ($stmt as $row) {
  print_r($row);
}
?>

Exemple #4 Appel d'une procédure stockée avec un paramètre de sortie

Si le pilote de la base de données le prend en charge, il est également possible de lier des paramètres aussi bien pour l'entrée que pour la sortie. Les paramètres de sortie sont utilisés typiquement pour récupérer les valeurs d'une procédure stockée. Les paramètres de sortie sont un peu plus complexes à utiliser que les paramètres d'entrée car on doit savoir la longueur qu'un paramètre donné pourra atteindre lorsqu'on le lie. Si la valeur retournée est plus longue que la taille que l'on aura suggérée, une erreur sera émise.

<?php
$stmt = $dbh->prepare("CALL sp_returns_string(?)");
$stmt->bindParam(1, $return_value, PDO::PARAM_STR, 4000); 

// Appel de la procédure stockée
$stmt->execute();

print "La procédure a retourné : $return_value\n";
?>

Exemple #5 Appel d'une procédure stockée avec un paramètre d'entrée/sortie

Il faut également spécifier les paramètres qui gèrent les valeurs aussi bien pour l'entrée que pour la sortie ; la syntaxe est similaire aux paramètres de sortie. Dans le prochain exemple, la chaîne 'Bonjour' est passée à la procédure stockée et lorsqu'elle retourne la valeur, 'Bonjour' est remplacée par la valeur retournée par la procédure.

<?php
$stmt = $dbh->prepare("CALL sp_takes_string_returns_string(?)");
$value = 'hello';
$stmt->bindParam(1, $value, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000); 

// appel de la procédure stockée
$stmt->execute();

print "La procédure a retourné : $value\n";
?>

Exemple #6 Utilisation invalide de marqueur

<?php
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name LIKE '%?%'");
$stmt->execute([$_GET['name']]);

// un marqueur doit être utilisé à la place d'une valeur complète
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name LIKE ?");
$stmt->execute(["%$_GET[name]%"]);
?>