# Preparedstatement : principes et exemples

Bien que les [systèmes de gestion de base de données (SGBD)](https://www.ionos.fr/digitalguide/hebergement/aspects-techniques/systeme-de-gestion-de-base-de-donnees-sgbd/ "Système 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](https://www.ionos.fr/digitalguide/serveur/know-how/apprendre-mysql-en-toute-simplicite/ "Apprendre MySQL en toute simplicité"), [MariaDB](https://www.ionos.fr/digitalguide/hebergement/aspects-techniques/mariadb-vs-mysql/ "MariaDB vs MySQL"), Oracle, Microsoft SQL Server ou [PostgreSQL](https://www.ionos.fr/digitalguide/serveur/know-how/postgresql/ "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](https://www.ionos.fr/digitalguide/hebergement/aspects-techniques/une-introduction-a-mysqli/ "Une introduction à 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](https://www.ionos.fr/digitalguide/serveur/securite/injection-sql-bases-et-mesures-pour-se-proteger/ "Injection SQL : bases et mesures pour se protéger") 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 :

```none
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 (**P**HP **D**ata **O**bjects)](https://www.php.net/manual/fr/book.pdo.php "PHP Manuals : PDO"). 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“](https://dev.mysql.com/doc/refman/8.0/en/prepare.html "MySQL Documentation : Information Page PREPARE-Statement") 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.

```none
PREPARE stmt_name FROM preparable_stmt
```

Pour **exécuter des requêtes SQL préparées**, vous avez besoin de la commande [“EXECUTE“](https://dev.mysql.com/doc/refman/8.0/en/execute.html "Documentation MySQL : Page d'information EXECUTE-Statement"). 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.

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

Pour **libérer** une Prepared Statement PHP, utilisez la requête [“DEALLOCATE PREPARE“](https://dev.mysql.com/doc/refman/8.0/en/deallocate-prepare.html "Documentation MySQL : page d'information EXECUTE-Statement"). 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.

```none
{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 :

```none
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** :

```none
<?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](https://www.ionos.fr/digitalguide/sites-internet/developpement-web/binary-large-object/) (grand objet de données binaires)

[YouTube video](https://www.youtube.com/watch?v=aN5KqxK1slc)


This is a markdown version of: [https://www.ionos.fr/digitalguide/sites-internet/developpement-web/prepared-statements-phpmysql/](https://www.ionos.fr/digitalguide/sites-internet/developpement-web/prepared-statements-phpmysql/) for AI/LLM consumption.