Requêtes préparées

• Verrous
• Sommaire
• Procédures stockées

Après les verrous et les transactions, voici une troisième notion importante pour la sécurisation des requêtes : les requêtes préparées.

Une requête préparée, c'est en quelque sorte une requête stockée en mémoire (pour la session courante), et que l'on peut exécuter à loisir. Mais avant d'entrer dans le vif du sujet, nous allons faire un détour par les variables utilisateur, qui sont indispensables aux requêtes préparées.

Dans ce chapitre, nous apprendrons donc :

• ce que sont les variables utilisateur et comment les définir ;
• ce qu'est une requête préparée ;
• comment créer, exécuter et supprimer une requête préparée ;
• la manière d'utiliser les requêtes préparées ;
• en quoi les requêtes préparées sont utiles pour la sécurisation d'une application ;
• comment et dans quel cas on peut gagner en performance en utilisant les requêtes préparées.

• Variables utilisateur
• Principe et syntaxe des requêtes préparées
• Usage et utilité

Variables utilisateur

Définitions

Variable

Une variable est une information stockée, constituée d'un nom et d'une valeur. On peut par exemple avoir la variable age (son nom), ayant pour valeur 24.

Variable utilisateur

Une variable utilisateur est une variable, définie par l'utilisateur. Les variables utilisateur MySQL doivent toujours être précédées du signe @.

Une variable utilisateur peut contenir quatre types de valeur :

• un entier (ex. : 24) ;
• un réel (ex. : 7,8) ;
• une chaîne de caractères (ex. : 'Hello World !') ;
• une chaîne binaire, auquel cas il faut faire précéder la chaîne du caractère b (ex. : b'011001'). Nous n'en parlerons pas dans ce cours.

Il existe également ce qu'on appelle des variables système, qui sont des variables prédéfinies par MySQL, et des variables locales, que nous verrons avec les procédures stockées.

Créer et modifier une variable utilisateur

SET

La manière la plus classique de créer ou modifier une variable utilisateur est d'utiliser la commande SET.

1
2

SET @age = 24;                                  -- Ne pas oublier le @
SET @salut = 'Hello World !', @poids = 7.8;     -- On peut créer plusieurs variables en même temps

Si la variable utilisateur existe déjà, sa valeur sera modifiée, sinon, elle sera créée.

1

SELECT @age, @poids, @salut;

Opérateur d'assignation

Il est également possible d'assigner une valeur à une variable utilisateur directement dans une requête, en utilisant l'opérateur d'assignation := (n'oubliez pas les :, sinon il s'agit de l'opérateur de comparaison de valeurs).

1

SELECT @age := 32, @poids := 48.15, @perroquet := 4;

Utilisation d'une variable utilisateur

Ce qu'on peut faire

Une fois votre variable utilisateur créée, vous pouvez bien sûr l'afficher avec SELECT. Mais vous pouvez également l'utiliser dans des requêtes ou faire des calculs avec.

1
2
3
4
5
6
7
8

SELECT id, sexe, nom, commentaires, espece_id 
FROM Animal 
WHERE espece_id = @perroquet; -- On sélectionne les perroquets

SET @conversionDollar = 1.31564;       -- On crée une variable contenant le taux de conversion des euros en dollars
SELECT prix AS prix_en_euros,         -- On sélectionne le prix des races, en euros et en dollars.
        ROUND(prix * @conversionDollar, 2) AS prix_en_dollars,   -- En arrondissant à deux décimales
        nom FROM Race;

Ce qu'on ne peut pas faire

Avec les variables utilisateur, on peut donc dynamiser un peu nos requêtes. Cependant, il n'est pas possible d'utiliser les variables utilisateur pour stocker un nom de table ou de colonne qu'on introduirait ensuite directement dans la requête. Ni pour stocker une partie de commande SQL. Les variables utilisateur stockent des données.

Exemples

1
2
3

SET @table_clients = 'Client';

SELECT * FROM @table_clients;

1

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@table_clients' at line 1

1
2
3

SET @colonnes = 'id, nom, description';

SELECT @colonnes FROM Race WHERE espece_id = 1;

C'est logique, dans une requête, les noms de tables/colonnes et les commandes SQL ne peuvent pas être représentées comme des chaînes de caractères : on ne les entoure pas de guillemets.

Portée des variables utilisateurs

Une variable utilisateur n'existe que pour la session dans laquelle elle a été définie. Lorsque vous vous déconnectez, toutes vos variables utilisateurs sont automatiquement réinitialisées. De plus, deux sessions différentes ne partagent pas leurs variables utilisateur.

Exemple

Session 1:

1

SET @essai = 3;

Session 2:

1

SELECT @essai;

De même, si vous assignez une valeur à @essai dans la session 2, @essai vaudra toujours 3 pour la session 1.

Principe et syntaxe des requêtes préparées

Principe

Une requête préparée, c'est en fait un modèle de requête que l'on enregistre et auquel on donne un nom. On va ensuite pouvoir l'exécuter en l'appelant grâce à son nom, et en lui passant éventuellement un ou plusieurs paramètres.

Par exemple, si vous avez régulièrement besoin d'aller chercher des informations sur vos clients à partir de leur adresse email dans une session, plutôt que de faire :

1
2
3
4

SELECT * FROM Client WHERE email = 'truc@email.com';
SELECT * FROM Client WHERE email = 'machin@email.com';
SELECT * FROM Client WHERE email = 'bazar@email.com';
SELECT * FROM Client WHERE email = 'brol@email.com';

Vous pouvez préparer une requête modèle :

1

SELECT * FROM Client WHERE email = ?

Où le ? représente un paramètre. Ensuite, il suffit de l'appeler par son nom en lui passant 'truc@email.com', ou 'machin@email.com', selon les besoins du moment.

Portée

Tout comme les variables utilisateur, une requête préparée n'existe que pour la session qui la crée.

Syntaxe

Voyons comment faire tout ça !

Préparation d'une requête

Pour préparer une requête, il faut renseigner deux éléments :

• le nom qu'on donne à la requête préparée ;
• la requête elle-même, avec un ou plusieurs paramètres (représentés par un ?).

Voici la syntaxe à utiliser :

1
2

PREPARE nom_requete
FROM 'requete_preparable';

Exemples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- Sans paramètre
PREPARE select_race
FROM 'SELECT * FROM Race';

-- Avec un paramètre
PREPARE select_client
FROM 'SELECT * FROM Client WHERE email = ?';

-- Avec deux paramètres
PREPARE select_adoption
FROM 'SELECT * FROM Adoption WHERE client_id = ? AND animal_id = ?';

Plusieurs choses importantes :

• Le nom de la requête préparée ne doit pas être entre guillemets. Par contre la requête à préparer, si. La requête à préparer doit être passée comme une chaîne de caractères.
• Que le paramètre soit un nombre (client_id = ?) ou une chaîne de caractères (email = ?), cela ne change rien. On ne met pas de guillemets autour du ? à l'intérieur de la requête à préparer.
• La chaîne de caractères contenant la requête à préparer ne peut contenir qu'une seule requête (et non plusieurs séparées par un ;).
• Les paramètres ne peuvent représenter que des données, des valeurs, pas des noms de tables ou de colonnes, ni des morceaux de commandes SQL.

Comme la requête à préparer est donnée sous forme de chaîne de caractères, il est également possible d'utiliser une variable utilisateur, dans laquelle on enregistre tout ou partie de la requête à préparer.

Exemples

1
2
3
4
5
6
7
8

SET @req = 'SELECT * FROM Race';
PREPARE select_race
FROM @req;

SET @colonne = 'nom';
SET @req_animal = CONCAT('SELECT ', @colonne, ' FROM Animal WHERE id = ?');
PREPARE select_col_animal
FROM @req_animal;

Par contre, il n'est pas possible de mettre directement la fonction CONCAT() dans la clause FROM.

Exécution d'une requête préparée

Pour exécuter une requête préparée, on utilise la commande suivante :

1

EXECUTE nom_requete [USING @parametre1, @parametre2, ...];

Si la requête préparée contient un ou plusieurs paramètres, on doit leur donner une valeur avec la clause USING, en utilisant une variable utilisateur. Il n'est pas possible de donner directement une valeur. Par ailleurs, il faut donner exactement autant de variables utilisateur qu'il y a de paramètres dans la requête.

Exemples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

EXECUTE select_race;

SET @id = 3;
EXECUTE select_col_animal USING @id;

SET @client = 2;
EXECUTE select_adoption USING @client, @id;

SET @email = 'jean.dupont@email.com';
EXECUTE select_client USING @email;

SET @email = 'marie.boudur@email.com';
EXECUTE select_client USING @email;

SET @email = 'fleurtrachon@email.com';
EXECUTE select_client USING @email;

SET @email = 'jeanvp@email.com';
EXECUTE select_client USING @email;

SET @email = 'johanetpirlouit@email.com';
EXECUTE select_client USING @email;

Suppression d'une requête préparée

Pour supprimer une requête préparée, on utilise DEALLOCATE PREPARE, suivi du nom de la requête préparée.

Exemple

1

DEALLOCATE PREPARE select_race;

Usage et utilité

Usage

La syntaxe que nous venons de voir, avec PREPARE, EXECUTE et DEALLOCATE est en fait très rarement utilisée. En effet, vous le savez, MySQL est rarement utilisé seul. La plupart du temps, il est utilisé en conjonction avec un langage de programmation, comme Java, PHP, python, etc. Celui-ci permet de gérer un programme, un site web,… , et crée des requêtes SQL permettant de gérer la base de données de l'application. Or, il existe des API (interfaces de programmation) pour plusieurs langages, qui permettent de faire des requêtes préparées sans exécuter vous-mêmes les commandes SQL PREPARE, EXECUTE et DEALLOCATE. Exemples : l'API C MySQL pour le langage C, MySQL Connector/J pour le Java, ou MySQL Connector/Net pour le .Net.

Voici un exemple de code C utilisant l'API MySQL pour préparer et exécuter une requête d'insertion :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

MYSQL_STMT  *req_prep;
MYSQL_BIND  param[3];

int     client = 2;
int     animal = 56;
MYSQL_TIME  date_reserv;

char        *req_texte = "INSERT INTO Adoption (client_id, animal_id, date_reservation) VALUES (?, ?, ?)";

// On prépare la requête
if (mysql_stmt_prepare(req_prep, req_texte, strlen(req_texte)) != 0)
{
    printf("Impossible de préparer la requête");
    exit(0);
}   

// On initialise un tableau (param, qui contiendra les paramètres) à 0
memset((void*) param, 0, sizeof(param));

// On définit le premier paramètre (pour client_id)
param[0].buffer_type = MYSQL_TYPE_INT;
param[0].buffer = (void*) &client;
param[0].is_unsigned = 0;
param[0].is_null = 0;

// On définit le deuxième paramètre (pour animal_id)
param[1].buffer_type = MYSQL_TYPE_INT;
param[1].buffer = (void*) &animal;
param[1].is_unsigned = 0;
param[1].is_null = 0;

// On définit le troisième paramètre (pour date_reservation)
param[2].buffer_type = MYSQL_TYPE_DATE;
param[2].buffer = (void*) &date_reserv;
param[2].is_null = 0;

// On lie les paramètres
if (mysql_stmt_bind_param(req_prep, param) != 0)
{
    printf("Impossible de lier les paramètres à la requête");
    exit(0);
}

// On définit la date
date_reserv.year = 2012;
date_reserv.month = 3;
date_reserv.day = 20;

// On exécute la requête
if (mysql_stmt_execute(req_prep) != 0)
{
    printf("Impossible d'exécuter la requête");
    exit(0);
}

Pour d'autres langages, des extensions ont été créées, qui sont en général elles-mêmes basées sur l'API C MySQL, comme par exemple PDO pour le PHP.

Exemple de code PHP utilisant l'extension PDO pour préparer et exécuter une requête de sélection :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

<?php
try
{
    $email = 'jean.dupont@email.com';

    // On se connecte
    $bdd = new PDO('mysql:host=localhost;dbname=elevage', 'sdz', '', array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION ));

    // On prépare la requête
    $requete = $bdd->prepare("SELECT * FROM Client WHERE email = :email");

    // On lie la variable $email définie au-dessus au paramètre :email de la requête préparée
    $requete->bindValue(':email', $email, PDO::PARAM_STR);

    //On exécute la requête
    $requete->execute();

    // On récupère le résultat
    if ($requete->fetch())
    {
        echo 'Le client existe !';
    }
} catch (Exception $e)
{
    die('Erreur : ' . $e->getMessage());
}

D'abord parce qu'il est toujours intéressant de savoir comment fonctionnent les requêtes préparées. Ensuite, parce qu'il pourrait arriver qu'il n'existe aucune API ni extension permettant de faire des requêtes préparées pour le langage dans lequel vous programmez, auquel cas, bien entendu, il faudrait construire vos requêtes préparées vous-mêmes. Ou vous pourriez tomber sur l'un des rares cas où il vous serait nécessaire de préparer une requête directement en SQL. Enfin, vous aurez peut-être besoin de faire quelques tests impliquant des requêtes préparées directement dans MySQL.

Cependant, si une API ou une extension existe et répond à vos besoins, utilisez-la ! Elle sera généralement plus performante et plus sécurisée que ce que vous pourriez faire vous-mêmes.

Utilité

Les requêtes préparées sont principalement utilisées pour deux raisons :

• protéger son application des injections SQL ;
• gagner en performance dans le cas d'une requête exécutée plusieurs fois par la même session.

Empêcher les injections SQL

En général, quand on crée une application, l'utilisateur peut interagir avec celle-ci. L'utilisateur peut créer un membre sur un site web communautaire, un personnage sur un jeu vidéo, etc. Les actions de l'utilisateur vont donc avoir une incidence sur la base de données de l'application. Il va envoyer certaines informations, qui vont être traitées, puis une partie va être envoyée sous forme de requêtes à la base de données. Il existe un adage bien connu en programmation : "Never trust user input" traduit en français par "Ne jamais faire confiance aux données fournies par l'utilisateur".

Lorsque l'on traite des données qui viennent de l'extérieur, il est absolument impératif de toujours vérifier celles-ci, et de protéger les requêtes construites à partir de ces données. Ceci évite que l'utilisateur, volontairement ou non, fasse ce qu'on appelle une injection SQL et provoque un comportement inattendu et souvent indésirable, voire dangereux, pour les données.

Passons maintenant à la question qui doit vous brûler les lèvres.

On appelle injection SQL le fait qu'un utilisateur fournisse des données contenant des mots-clés SQL ou des caractères particuliers qui vont détourner ou modifier le comportement des requêtes construites sur la base de ces données.

Imaginons que vous créiez un site web avec un espace membre. Vos membres ont accès à une page "profil" grâce à laquelle ils peuvent gérer leurs informations, ou supprimer leur compte. Pour supprimer leur compte, ils ont simplement à appuyer sur un bouton qui envoie leur numéro d'id.

D'un côté on a donc une requête incomplète :

1

DELETE FROM Membre WHERE id =

De l'autre, l'id du client, par exemple 4.

Avec votre langage de programmation web préféré, vous mettez les deux ensemble pour créer la requête complète :

1

DELETE FROM Membre WHERE id = 4;

Et maintenant, un méchant pirate s'amuse à modifier la donnée envoyée par le bouton "Supprimer compte" (oui oui, c'est faisable, et même plutôt facile). À la suite du numéro d'id, il ajoute OR 1 = 1. Après construction de la requête, on obtient donc ceci :

1

DELETE FROM Membre WHERE id = 4 OR 1 = 1;

Et la seconde condition étant toujours remplie, c'est l'horreur : toute votre table Membre est effacée ! Et voilà ! Vous avez été victimes d'une injection SQL.

En utilisant les requêtes préparées, lorsque vous liez une valeur à un paramètre de la requête préparée grâce à la fonction correspondante dans le langage que vous utilisez, le type du paramètre attendu est également donné, explicitement ou implicitement. La plupart du temps, soit une erreur sera générée si la donnée de l'utilisateur ne correspond pas au type attendu, soit la donnée de l'utilisateur sera rendue inoffensive (par l'ajout de guillemets qui en feront une simple chaîne de caractères par exemple). Par ailleurs, lorsque MySQL injecte les valeurs dans la requête, les mots-clés SQL qui s'y trouveraient pour une raison où une autre ne seront pas interprétés (puisque les paramètres n'acceptent que des valeurs, et pas des morceaux de requêtes).

Gagner en performance

Lorsque l'on exécute une simple requête (sans la préparer), voici les étapes qui sont effectuées :

1. La requête est envoyée vers le serveur.
2. La requête est compilée par le serveur (traduite du langage SQL compréhensible pour nous, pauvres humains limités, vers un "langage machine" dont se sert le serveur).
3. Le serveur crée un plan d'exécution de la requête (quelles tables utiliser ? quels index ? dans quel ordre ?…).
4. Le serveur exécute la requête.
5. Le résultat de la requête est renvoyé au client.

Voici maintenant les étapes lorsqu'il s'agit d'une requête préparée :

Préparation de la requête :

1. La requête est envoyée vers le serveur, avec un identifiant.
2. La requête est compilée par le serveur.
3. Le serveur crée un plan d'exécution de la requête.
4. La requête compilée et son plan d'exécution sont stockés en mémoire par le serveur.
5. Le serveur envoie vers le client une confirmation que la requête est prête (en se servant de l’identifiant de la requête).

Exécution de la requête :

1. L'identifiant de la requête à exécuter, ainsi que les paramètres à utiliser sont envoyés vers le serveur.
2. Le serveur exécute la requête demandée.
3. Le résultat de la requête est renvoyé au client.

On a donc plus d'étapes pour une requête préparée, que pour une requête non préparée (8 contre 5). Du moins, lorsque l'on exécute une seule fois la requête. Car si l'on exécute plusieurs fois une requête, la tendance s'inverse rapidement : dans le cas d'une requête non préparée, toutes les étapes doivent être répétées à chaque fois (sachant que la création du plan d'exécution est l'étape la plus longue), alors que pour une requête préparée, seule les étapes d'exécution seront répétées.

Il est donc intéressant, d'un point de vue des performances, de préparer une requête lorsqu'elle va être exécutée plusieurs fois pendant la même session (je vous rappelle que les requêtes préparées sont supprimées à la fin de la session).

En résumé

• Les variables utilisateur sont, comme leur nom l'indique, des variables définies par l'utilisateur.
• Les variables utilisateur sont précédées du caractère @ et peuvent être définies par la commande SET ou l'opérateur d'assignation :=.
• Une requête préparée est un modèle de requête auquel on donne un nom, pour pouvoir l'appeler à loisir, en lui passant éventuellement des paramètres, représentés dans la requête préparée par le caractère ?.
• Lorsque l'on prépare une requête, celle-ci doit être représentée par une chaîne de caractères, qui peut être préalablement stockée dans une variable utilisateur.
• Les requêtes préparées permettent de se protéger des injections SQL.
• Lorsqu'une requête doit être exécutée plusieurs fois, la préparer peut permettre de gagner en performance.

• Verrous
• Sommaire
• Procédures stockées