Preparedstatement : principes et exemples

Bien que les systèmes de gestion de base de données (SGBD) qui fonctionnent avec le langage de base de données SQL soient très populaires, ils ont toujours été susceptibles d'être manipulés lors de l'injection de données. Par exemple, une entrée utilisateur qui n'est pas suffisamment masquée et qui contient des métacaractères tels que les guillemets ou les points-virgules est une cible potentielle pour les attaquants. Une réponse possible à ce problème est l'utilisation des Prepared Statements, c'est-à-dire des requêtes préparées à la base de données qui ne sont pas affectées de valeurs avant l'exécution.

Qu'est-ce qui rend cette technique si particulière et où est-elle utilisée ? En utilisant MySQL comme exemple, nous vous montrons comment fonctionnent les requêtes préparées, et comment les utiliser pour la gestion des bases de données.

Que sont les requêtes préparées ?

Les Prepared Statements (requêtes préparées) sont des modèles prêts à l'emploi pour les requêtes dans les systèmes de base de données SQL qui ne contiennent pas de valeurs pour les paramètres individuels. Ces modèles de déclaration (également appelés templates de déclaration) fonctionnent avec des variables ou des caractères de remplissage qui sont uniquement remplacés par les valeurs réelles dans le système contrairement à la saisie manuelle, où les valeurs sont déjà attribuées au moment où une commande est exécutée.

Tous les principaux systèmes de gestion de bases de données basés sur SQL, tels que MySQL, MariaDB, Oracle, Microsoft SQL Server ou PostgreSQL prennent en charge les requêtes préparées, et la plupart de ces applications utilisent à cette fin un protocole binaire NoSQL. Toutefois, certains systèmes, dont MySQL, utilisent également une syntaxe SQL commune pour la mise en œuvre. En outre, certains langages de programmation tels que Java, Perl, Python et PHP supportent des requêtes par le biais de leurs bibliothèques ou extensions standard. Par exemple, si vous utilisez ce dernier langage de script pour l'accès aux bases de données, vous pouvez utiliser soit l'interface orientée objet PHP Data Objects (PDO), soit l'extension PHP MySQLi pour mettre en œuvre les preparedstatements.

Pourquoi est-il si utile d'utiliser des Prepared Statements dans MySQL et autres ?

La raison décisive incitant à travailler avec des requêtes préparées dans des systèmes de gestion de bases de données comme MySQL est l'aspect de la sécurité. Le plus gros problème de l'accès standard aux bases de données en langage SQL est probablement qu'elles sont facilement manipulables. Dans ce cas, on parle d'un SQL-Injection où du code est ajouté ou modifié pour avoir accès à des données sensibles ou même pour avoir un contrôle total sur le système de base de données. Les Prepared Statements en PHP ou dans d'autres langues ne présentent pas cette faille de sécurité, car seules des valeurs concrètes leur sont attribuées dans le système.

Note

La condition préalable à la norme de sécurité élevée des Prepared Statements est qu'aucun élément de cette requête ne soit générée à partir d'une source externe

Toutefois, la protection contre les injections SQL n'est pas le seul argument en faveur de l'utilisation de modèles d'instructions : une fois analysés et compilés, ils peuvent être utilisés encore et encore par le système de base de données respectif par la suite (avec les valeurs respectives modifiées). Par conséquent, les requêtes préparées consomment beaucoup moins de ressources et sont plus rapides que les interrogations manuelles de la base de données lorsque les tâches SQL doivent être exécutées de manière répétée.

Comment fonctionne exactement l'utilisation de preparedstatement ?

Si l'on ne tient pas compte de la syntaxe du langage de script sous-jacent et des particularités du système de gestion de base de données respectif, l'intégration et l'utilisation des requêtes préparées se déroulent généralement dans les phases suivantes :

Phase 1 : Préparation des Prepared Statements

La première étape consiste à créer un modèle de déclaration - en PHP par exemple avec la fonction prepare(). Au lieu des valeurs concrètes pour les paramètres pertinents, les place holders déjà mentionnés sont insérés, qui sont également appelés variables de liaison. En général, ces derniers sont marqués d'un " ? " comme dans l'exemple suivant :

INSERT INTO Produit (Nom, Prix) VALUES (?, ?);

Les requêtes préparées complètes sont ensuite transmises au système de gestion de la base de données correspondant.

Phase 2 : traitement du modèle de preparedstatement par le SGBD

Le système de gestion de la base de données commence par analyser le modèle de déclaration afin qu'il puisse être compilé à l'étape suivante, c'est-à-dire converti en une déclaration exécutable. Au cours de ce processus, la requête préparée est également optimisée.

Phase 3 : exécution de la déclaration préparée

Par la suite, le modèle traité peut être exécuté dans le système de base de données aussi souvent que nécessaire. La seule condition préalable à cela est une entrée appropriée de l'application connectée ou une source de données qui doit fournir les valeurs correspondantes pour les champs de caractère de remplissage. Les valeurs "Livre" (nom) et "10" (prix), par exemple, ou "Ordinateur" et "1000" pourraient correspondre à l'exemple de code ci-dessus (phase 1).

Tutoriel : comment utiliser les Prepared Statements dans MySQL avec MySQLi

Après avoir décrit la fonctionnalité des requêtes préparées en général, le tutoriel suivant expliquera l'utilisation des instructions pratiques à l'aide d'exemples concrets. Le tutoriel est basé sur les exemples suivants :

  • MySQL comme système de gestion de base de données et
  • PHP comme langage de requête préparée.

Les versions actuelles de MySQL prennent en charge l'utilisation côté serveur de requêtes préparées basées sur un protocole binaire qui contient toutes les commandes SQL, mettant à jour les données, et enregistre également toutes les mises à jour depuis la dernière sauvegarde. Dans ce tutoriel, l'extension PHP MySQLi est utilisée comme interface d'accès, qui prend également en charge les Prepared Statements basées sur le protocole binaire.

Note

Une bonne alternative à MySQLi, fréquemment utilisée comme API de requête préparée, est l‘interface orientée objet PDO (PHP Data Objects). Cette variante est généralement considérée comme une solution plus facile d‘utilisation pour les débutants

PREPARE, EXECUTE et DEALLOCATE PREPARE : les trois commandes SQL élémentaires pour utiliser les Prepared Statements

Trois commandes SQL jouent un rôle essentiel dans l'application des requêtes préparées dans les bases de données MySQL :

La commande "PREPARE" est nécessaire pour préparer une requête préparée à l'usage et, entre autres, pour lui attribuer un nom unique sous lequel la requête peut être consultée ultérieurement.

PREPARE stmt_name FROM preparable_stmt

Pour exécuter des requêtes SQL préparées, vous avez besoin de la commande "EXECUTE". Vous vous référez à la requête préparée respective en spécifiant le nom généré avec "PREPARE". La fréquence d'exécution d'une déclaration dépend de vous : vous pouvez définir un nombre quelconque de variables différentes ou transmettre un nombre illimité de nouvelles valeurs pour les variables définies.

EXECUTE stmt_name
	[USING @var_name [, @var_name] ...]

Pour libérer une Prepared Statement PHP, utilisez la requête "DEALLOCATE PREPARE". Par ailleurs, les requêtes sont automatiquement publiées à la fin d'une session. La libération est pertinente dans la mesure où, sinon, la limite supérieure spécifiée par la variable système max_prepared_stmt_count est rapidement atteinte. Vous ne pourriez alors pas générer de nouvelles requêtes préparées.

{DEALLOCATE | DROP} PREPARE stmt_name

Vous pouvez utiliser ces requêtes SQL comme des Prepared Statements MySQL

Vous pouvez préparer et exécuter presque toutes les requêtes SQL prises en charge par MySQL en tant qu'instructions préparées. La seule exception à cette règle concerne les requêtes de diagnostic, qui sont exclues en tant que requêtes préparées pour se conformer à la norme SQL. Il s'agit donc des requêtes suivantes :

  • SHOW WARNINGS
  • SHOW COUNT(*) WARNINGS
  • SHOW ERRORS
  • SHOW COUNT(*) ERRORS

En outre, vous ne pouvez pas générer de modèles pour les requêtes SQL en référence aux variables système warning_count et error_count.

Vous pouvez toutefois utiliser les preparedstatements suivants :

ALTER TABLE
ALTER USER
ANALYZE TABLE
CACHE INDEX
CALL
CHANGE MASTER
CHECKSUM {TABLE | TABLES}
COMMIT
{CREATE | DROP} INDEX
{CREATE | RENAME | DROP} DATABASE
{CREATE | DROP} TABLE
{CREATE | RENAME | DROP} USER
{CREATE | DROP} VIEW
DELETE
DO
FLUSH {TABLE | TABLES | TABLES WITH READ LOCK | HOSTS | PRIVILEGES
	| LOGS | STATUS | MASTER | SLAVE | USER_RESOURCES}
GRANT
INSERT
INSTALL PLUGIN
KILL
LOAD INDEX INTO CACHE
OPTIMIZE TABLE
RENAME TABLE
REPAIR TABLE
REPLACE
RESET {MASTER | SLAVE}
REVOKE
SELECT
SET
SHOW {WARNINGS | ERRORS}
SHOW BINLOG EVENTS
SHOW CREATE {PROCEDURE | FUNCTION | EVENT | TABLE | VIEW}
SHOW {MASTER | BINARY} LOGS
SHOW {MASTER | SLAVE} STATUS
SLAVE {START | STOP}
TRUNCATE TABLE
UNINSTALL PLUGIN
UPDATE

Particularités syntaxiques de la syntaxe SQL des Prepared Statements en PHP

Par rapport à la syntaxe SQL standard, la syntaxe des requêtes préparées présente quelques particularités qui doivent être respectées. Tout d'abord, l'utilisation de place holders pour les valeurs des paramètres rend les requêtes préparées intéressantes pour l'accès aux systèmes de gestion de bases de données. À partir de MySQL 8.0, par exemple, de tels place holders sont également possibles pour les paramètres "OUT" et "INOUT" dans les requêtes "PREPARE" et "EXECUTE" (pour les paramètres "IN", ils sont même disponibles quelle que soit la version du système de base de données). Les autres propriétés spécifiques de la syntaxe de la requête préparée sont les suivantes :

  • La syntaxe SQL des requêtes préparées en PHP ne peut pas être imbriquée. Une requête transmise à une requête "PREPARE" ne peut pas être elle-même une requête "PREPARE", "EXECUTE" ou "DEALLOCATE PREPARE".
  • Les Prepared Statements peuvent être utilisées dans des procédures stockées (fonction d'appel de séquences complètes de requêtes).
  • Les requêtes multiples, également appelées multi-statements, ne sont pas possibles à l'intérieur d'une requête préparée ou à l'intérieur d'une chaîne en les séparant par un point-virgule.

Requêtes préparées en MySQLi : exemple

Cet exemple montre exactement à quoi ressemble une entrée avec des Prepared Statements PHP dans MySQLi :

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// créer une connexion
$conn = new mysqli($servername, $username, $password, $dbname);
// vérifier la connexion
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
// Préparation des Prepared Statements
$stmt = $conn->prepare("INSERT INTO Mesclients (Prénom, Nom, Mail) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $Prénom, $Nom, $mail);
// Paramétrage et exécution
$Prénom = "Max";
$Nom = "Dupont";
$Mail = "max@exemple.fr";
$stmt->execute();
$Prénom = "Sophie";
$Nom = "Dupuis";
$Mail = "sophie@exemple.fr";
$stmt->execute();
$Prénom = "Eric";
$Nom = "Delcroix";
$Mail = "eric@exemple.fr";
$stmt->execute();
echo "Nouvelles entrées créées avec succès";
$stmt->close();
$conn->close();
?>

Ce script PHP établit d'abord la connexion à la base de données MySQL ($conn), où les données individuelles du serveur telles que le nom d'hôte, le nom d'utilisateur, le mot de passe et le nom de la base de données doivent être spécifiées.

Avec la ligne "INSERT INTO Mesclients (Prénom, Nom, Mail) VALUES ( ?, ?, ?)", la partie décisive de la requête préparée commence : la base de données clients "Mesclients" doit recevoir la saisie (INSERT INTO) pour les colonnes "Prénom", "Nom" et "Mail" respectivement. Pour les valeurs (VALUES), on utilise d'abord des caractères de remplissage, qui sont identifiés par le symbole du point d'interrogation ( ?).

Ensuite, les paramètres doivent être liés (bind_param). La base de données a également besoin d'informations sur le type de données concernées. Par exemple, l'argument "sss" utilisé ici indique que les trois paramètres sont des chaînes de caractères. Les alternatives possibles se situent à ce stade :

  • i: INTEGER (valeur entière)
  • d: DOUBLE (valeur numérique approximative de la donnée)
  • b: BLOB (grand objet de données binaires)
aN5KqxK1slc.jpg Pour afficher cette vidéo, des cookies de tiers sont nécessaires. Vous pouvez consulter et modifier vos paramètres de cookies ici.
Cet article vous a-t-il été utile ?