Aller au menu - Aller au contenu

Icône Requête Préparée

Par Avatar Draeli
Mise à jour : 05/07/2009
Difficulté : Intermédiaire Intermédiaire Creative Commons BY-NC-SA
348 visites depuis 7 jours, classé 303/786
Les requêtes préparées (Anglais : prepared statements) sont des requêtes qui, comme le nom l'indique sont préparées et donc qui permettent d'automatiser une requête qui apparaît souvent et ainsi alléger le temps de traitement (ceci n'a d'intérêt que pour les requêtes auxquelles fait souvent appel une session, nous verrons plus bas pourquoi).

Requête préparée : MySQL 4.1 et supérieur.
Retour en haut
Sommaire du tutoriel :
Icône du chapitre
Retour en haut

Explication

Avant de continuer dans les explications théoriques, précisons ce que ne sont pas les requêtes préparées. Les requêtes préparées ne sont pas des procédures stockées (Procédures stockées : en terme simple on peut comparer ça à des requêtes préparées mais globales, ainsi il est possible de préparer des procédures et de les appeler quelle que soit la session), en termes simples cela veut dire que la requête qui sera stockée ne le sera que pour la session ('thread'), ainsi lors de l'utilisation ou lors de la mise en place d'un système se servant de ce système, demandez-vous si cela correspond bien à vos besoins.

Schéma d'une requête normale :
1 : envoi de la requête par le client vers le serveur
2 : compilation de la requête
3 : plan d'exécution par le serveur
4 : exécution de la requête
5 : résultat du serveur vers le client

Schéma d'une requête préparée :
Phase 1 :
1 : envoi de la requête à préparer
2 : compilation de la requête
3 : plan d'exécution par le serveur
4 : stockage de la requête compilée en mémoire
5 : retour d'un identifiant de requête au client
Phase 2 :
1 : le client demande l'exécution de la requête avec l'identifiant
2 : exécution
3 : résultat du serveur au client

Comme nous le voyons, la requête préparée se fait avec plus d'étapes, ce qui explique qu'elle ne devient intéressante que dans le cadre d'une utilisation répétée d'une requête.

Outre cet aspect purement technique, il existe deux autres raisons qui peuvent justifier l'utilisation d'une requête préparée :
- limiter la bande passante utilisée entre le client et le serveur : dû au fait que l'échange d'informations est limité au strict minimum.
- éviter les injections Sql : cela concerne la sécurité et évite que les informations rentrées par un client (à travers un formulaire par exemple) soient interprétées.
Retour en haut

Cadre d'exploitation

Comme nous l'avons vu ce type de requête est limité à la session (voir thread) d'un utilisateur.

Afin de comprendre ce qui est mis en jeu, simulons une personne visitant une page écrite en Php que nous appelerons ma_page.php qui contiendra des requêtes à la base de données (les requêtes faites dedans ont peu d'importance, l'intérêt étant d'ouvrir une connexion à la base de données).

Bien sûr afin de pouvoir effectuer les requêtes contenues dans cette page, il sera nécessaire pour la page d'ouvrir une connexion (en Php cela se fait à l'aide de mysql_connect).
Lors de l'appel à cette fonction, plusieurs paramètres vont être fournis qui, s'ils sont corrects permettront à la page d'ouvrir une session. Cette session ouverte aura un numéro au niveau de MySQL. Ainsi si plusieurs personnes consultent cette même page en même temps, MySQL sera capable de les distinguer (si vous lancez MySQL en ligne de commande vous pourrez d'ailleurs remarquer qu'un id de session vous est alloué).
Cela implique donc que chaque session peut travailler en parallèle des autres ce qui permet d'éviter le chaos qu'il pourrait régner sans ce système.

Voilà pour la théorie mais pourquoi je vous explique cela me direz-vous ?
Tous simplement car il faut bien comprendre qu'une fois que cette connexion sera lancée, une session sera créée et que dans le cas de Php (avec un comportement par défaut) la connexion se terminera soit avec la rencontre de la fonction mysql_close, soit à la fin de la page.
Ainsi durant cet intervalle, si une requête ayant toujours le même aspect (dont seuls les paramètres changent) est répétée plusieurs fois alors il est intéressant d'utiliser ce système.
Retour en haut

Utilisation

Cette partie va vous montrer comment procéder afin de mettre en place ce système, lors de vos tests, je vous conseille fortement de tester la requête directement en ligne de commande.

Préparation d'une procédure :
Code : SQL
1
PREPARE identifiant FROM 'requete' ;

Ceci est la forme de base pour déclarer une requête préparée, ceci dit ce n'est pas tout car bien sûr l'intérêt de ce type de choses est de pouvoir déclarer des paramètres dans la requête mais rien de mieux qu'un exemple afin de vous faire comprendre :
Code : SQL
1
PREPARE test FROM 'SELECT * FROM matable WHERE ID=?';

Comme vous pouvez le remarquer la requête n'a rien d'exceptionnel si ce n'est le '?' qui apparaît à la place du numéro d'id auquel on pourrait s'attendre, et c'est ce '?' qui va vous permettre de rendre vos requêtes paramétrables à souhait.
Le '?' présent dans la requête représente donc une variable.

Déclaration de la variable :
Code : SQL
1
SET @mavariable=X ;

Bien sûr dans le cadre de l'exemple ci-dessus, le X vaudra un chiffre et 'mavariable' sera le nom de cette variable.

Exécution de la requête :
Code : SQL
1
EXECUTE test USING @mavariable ;

On va demander l'exécution de la requête préparée ayant en identifiant du nom de 'test' et on va lui assigner la variable '@mavariable' (rappelons que le @ représente dans MySQL une variable utilisateur de session)

Comme vous le voyez l'utilisation est relativement simple cependant il vous appartient de faire attention dans le cas où vous assigneriez une requête préparée avec un nom qui correspondrait à une requête préparée déjà existante, effectivement, si cette nouvelle requête échoue, alors l'ancienne requête préparée ne sera plus disponible (MySQL effectue un DROP PREPARE identifiant ; avant de procéder à la mise en place de la nouvelle requête).

Suppression d'une requête :
Vous pouvez supprimer une requête préparée à l'aide de :
Code : SQL
1
DROP PREPARE identifiant ;

ou
Code : SQL
1
DEALLOCATE PREPARE identifiant ;


Connaître les utilisateurs connectés au serveur :
Une fois connecté avec une session en ligne de commande, tapez :
Code : SQL
1
SHOW processlist;


Remarquez qu'il est possible de mettre plusieurs '?' afin de mettre plusieurs paramètres. En ce cas, l'appel se fera sous la même forme, la seule différence étant lors de l'appel qui comprendra chaque variable séparée par une virgule, et le nombre de variables devra être égal au nombre de '?'.

Exemple :
Code : SQL
1
PREPARE test FROM 'SELECT * FROM matable WHERE ID = ? AND nombre > ?'

L'appel se fera alors par :
Code : SQL
1
EXECUTE test USING @mavariable , @mavariable2 ;
Retour en haut

Manipulation

Ici nous allons un peu nous amuser en manipulant des sessions afin de voir exactement ce qu'il ce passe, ce qui permettra à ceux n'ayant pas tout compris de lever le voile à travers une manipulation qui se veut simple mais néanmoins complète.

Nécessaire :
- 1 serveur MySQL 4.1 ou supérieur (disponible sur le site officiel www-fr.mysql.com) avec si possible une installation par défaut.
- la base de données WORLD en .sql disponible sur le site officiel MySQL.

Bien, maintenant que vous avez votre serveur MySQL installé, il est temps de passer à la manipulation proprement dite, pour cela nous allons ouvrir une première fenêtre MySQL en ligne de commande (Démarrer->Programmes->MySQL->MySQL Server 4.1->MySQL Command Line Client.

Là, si vous avez effectué correctement la manipulation, vous devriez avoir une petite fenêtre qui est ouverte vous demandant un mot de passe (celui que vous avez mis à l'installation), une fois le mot de passe accepté, vous arrivez sur le prompt de MySQL qui se présente sous la forme : mysql>, ce prompt est juste là comme point de repère et n'implique rien, vous pouvez donc le changer à votre convenance, dans notre cas, nous allons le renommer afin de nous y retrouver parmi les différentes fenêtres qui seront ouvertes en fin de manipulation.
Tapez => prompt fenetre1> et validez.
Comme vous pouvez le remarquer, le prompt a changé :)
Maintenant que ceci est fait, ouvrez une deuxième fenêtre de la même manière et changez-en le prompt en fenetre2>.

A ce point de la manipulation, nous avons deux fenêtres MySQL qui sont ouvertes si tout est correct.

Rendez-vous sur la première fenêtre et tapez => SHOW processlist; et validez.
Un tableau s'affiche vous récapitulant des informations, en l'occurence, cela vous indique que vous avez deux sessions de lancées avec respectivement le numéro de session, l'utilisateur connecté, l'hôte et son port de connexion, la base de données où se trouve l'utilisateur (égale à NULL pour le moment ce qui est normal), et diverses autres choses dont le info qui vous permet de voir la session qui a effectué le SHOW processlist;.
A ce stade, nous voyons que chaque session est normalement indépendante de l'autre mais afin de nous en assurer, passons à la suite de la manipulation.

Bien maintenant dans une des deux fenêtres, entrez ceci :
Code : SQL
1
2
CREATE DATABASE world;
USE world;

et validez dans les deux fenêtres. Ceci nous a permis de créer une base de données appelée world et de se rendre dessus.
Il est maintenant nécessaire de la remplir, pour cela nous allons avoir besoin du world.sql que vous avez normalement récupéré (voir Nécessaire), ouvrez le répertoire où il est stocké.
Maintenant, tapez => source mais sans valider puis faites glisser le fichier world.sql dans la fenêtre ou vous avez tapé ceci. Vous devriez donc maintenant être en présence d'une chose dans ce genre :
source "lecteur:\repertoire\world.sql"
Bon ce n'est pas fini, virez les guillemets puis ajoutez ; à la fin. Vous êtes désormais en présence de quelque chose sous la forme :
source lecteur:\repertoire\world.sql;
Validez et patientez un petit peu, les tables et les enregistrements sont en cours de création.

Nous allons vérifier que tous s'est bien passé en tapant ceci :
SHOW TABLES;
là vous aurez 3 tables si tout s'est déroulé correctement (de toute façon dans le cas contraire vous aurez 1-N bips vous signalant des erreurs, ce en quoi vous avez fait une mauvaise manipulation).

Bien passons maintenant à la partie finale qui est notre but. Tester les requêtes préparées (oui on y arrive enfin o_O).

Première étape, une requête préparée qui nous permettra de récupérer des pays.
Mettez-vous sur l'une des deux fenêtres sur laquelle vous resterez pour le moment et tapez ceci :
Code : SQL
1
2
3
PREPARE pays FROM 'SELECT Name FROM Country WHERE Name LIKE ?';
SET @p='%fr%';
EXECUTE pays USING @p;
puis validez.
Comme vous le voyez ceci vous sort la liste des pays contenant fr dans leur nom.
Vous pouvez bien sùr remplacer la valeur de la variable afin de trouver autre chose et faire des tests par vous-même. Ainsi, une fois que vous aurez modifié la variable à l'aide de SET, vous n'aurez plus qu'à rappeler la requête avec EXECUTE.
Remarquez qu'un oubli du @ dans la requête EXECUTE ne provoquera qu'une erreur vous signalant une mauvaise syntaxe.

Bien maintenant passez à l'autre fenêtre et essayez de lancer une requête à l'aide :
Code : SQL
1
EXECUTE pays USING @p;

Vous aurez alors l'erreur suivante :
ERROR 1243 (HY000): Unknown prepared statement handler (pays) given to EXECUTE
Ceci est dû au fait que la requête préparée demandée n'est pas trouvée.

Conclusion, comme nous l'avons vu tout au long de ce tutorial, ceci est dû au fait que si effectivement la requête existe dans la session où elle a été déclarée, dans une autre session elle ne sera pas accessible.
Retour en haut

Définitions

Cette partie est là afin de développer tous les termes qui peuvent paraître obscurs de prime abord.

Thread : on parle de thread (flux en Français) dans ce cas afin de désigner une session ouverte par un utilisateur. Cette session est régie par des règles qui dépendent des paramètres du serveur mais aussi des moteurs de stockages utilisés (sachant que ceci peut aussi être réglé au niveau du serveur).

Moteur de Stockage : il existe 5 types disponibles avec MySQL qui sont MyISAM, InnoDB, BDB, MERGE, HEAP. Ces moteurs ont des caractéristiques qui leur sont propres et qui devront être choisies en fonction des besoins de la personne qui met en place la base de données.
Les spécificités de chaque moteur seront traitées dans un futur tutoriel pour des raisons pratiques :)

Ligne de commande : on parle d'interface en ligne de commande afin de désigner les processus sans interface et qui ne proposent comme toute interactivité qu'un 'prompt'.
Par exemple une fois connecté en ligne de commande à MySQL, vous aurez le prompt : mysql>
Il est à remarquer que sous WindowsXP, vous pouvez lancer l'interface en ligne de commande à partir de : Démarrer->Exécuter->cmd et OK.

Variables spécifiques à MySQL : MySQL possède deux types de variables qui peuvent intervenir.
Les variables à portée globale, qui sont désignées par @@ et les variables à portée de sessions, qui comme le nom l'indique ne sont valables que dans l'espace de la session et qui peuvent donc varier d'une connexion à l'autre.
Exemples de variables pour changer l'heure selon le fuseau horaire :
- Changer le fuseau horaire pour toutes les sessions
Code : SQL
1
SET @@time_zone='+01:00';

- Changer le fuseau horaire pour la session courante
Code : SQL
1
SET @time_zone='+03:00';

Dans le cas ci-dessus, si l'utilisateur de la session qui a changé l'heure pour son fuseau souhaite savoir quel est son décalage avec le serveur, alors il saura qu'il a 2H d'avance (+03:00 - +01:00).
Retour en haut
Finalement on s'aperçoit que ce type de requêtes peut s'avérer très utile quand il s'agit d'optimiser des requêtes qui seront appelées plusieurs fois durant la session d'un utilisateur mais dont l'usage reste restreint.
Retour en haut

Partager

Retour en haut
36 commentaires pour "Requête Préparée"
Note moyenne : 3.15 / 4 (13 votes)
Pseudo Commentaire
Hors ligne Edmeral # Posté le 02/02/2010 à 09:38:48
Ulquiorra ♥ Ubuntu !
Avatar
close

Ville : Meknès
Pays : Maroc

 Bon tuto ;)

Sinon pour ceux qui utilisent Linux (comme moi), vous pouvez accéder à MySql en ligne de commande avec :
Code : Console
mysql -u root -p


Après vous entrez le mot de passe que vous avez choisi pendant l'installation de MySQL.
                    
                        






                        
 

                    
                		
                

              
                

                  
                    
                      Hors ligne 
                    
                      Caduchon
                    
                  
                  
                    #
                    
                         Posté le 12/02/2010 à 11:20:46
                    
                    
                


                
                    

                
                
                    

Cicéron c'est Pointcarré
Avatar
Validateurs


     Ville : Louvain-la-neuve

	Pays : Belgique






                		
                
                    
                    Sympa le tuto. Merci.

Pour ceux qui s'interrogent sur la nécessité d'utiliser plusieurs fois une même requête sql sur une même page, ça peut effectivement être le cas avec des paramètres différents.



Exemple typique (si j'ai bien compris le tuto): envoyer un mp à tous les utilisateurs. Avec les commandes classique on aurait quelque chose comme:

Code : PHP
1
2
3
4
5
<?php
$result_user = mysql('SELECT id FROM Users');
while($data_user = mysql_fetch_assoc($result_user))
  mysql('INSERT INTO Messages(expediteur,destinataire,texte) VALUES('.$id_session.','.$data_user['id'].',"'.$mon_message.'")');
?>







La requête est chaque fois différente, mais pas en terme de compilation, elle ne diffère qu'à l'exécution pour un de ses paramètres. Elle pourrait donc être préparée.
                    
                        




Image utilisateur
[Tuto][Olympiades] Inégalités





                        
 

                    
                		
                

              
                

                  
                    
                      Hors ligne 
                    
                      hornetbzz
                    
                  
                  
                    #
                    
                         Posté le 12/02/2010 à 14:45:57
                    
                    
                


                
                    

                
                
                    

que la force soit avec toi
Avatar



     Études : ESTACA






                		
                
                    
                    Simple, clair, efficace. Bravo et merci.
                    
                
                

              
                

                  
                    
                      Hors ligne 
                    
                      Juns
                    
                  
                  
                    #
                    
                         Posté le 02/05/2010 à 23:50:44
                    
                    
                


                
                    

                
                
                    

Homo-Noeticus
Avatar



    





                		
                
                    
                    Tuto clair et bien pensé mais dans lequel il manque pour ma part quelques point d'interrogation vital.



1) Je trouve qu'il y à un manque clair sur les injection mysql, il es dit que les requêtes préparer sont sécurisé, mais en quoi elle le sont ???

Qu'est ce qui fait qu'on sais qu'il ne peut y avoir d'injection ???



2) les exemples sur les requêtes préparer ne porte uniquement que sur la méthode "query" , à aucun moment on nous donne un exemple pour une requête préparer de type "exec".

Dans l'exemple données il y a une utilisation d'un array,(si on utilise des marqueurs ) mais pour un "exec" en à ton besoin ???



3) Le même problème ce pose pour sécurise une requête non préparer toujours du "query" en exemple.



Ce qui me préoccupe le plus dans PDO c'est de savoir si ce que l'on fait et il ou non sécurisé et si oui pourquoi.

Étant donnée que PDO et différent de MYSQL sur le façon de procéder il en vas aussi de même sur la façon de sécurisé des données.

Et c'est bien la base de mon problème c'est que je ne voit pas comment utiliser pdo de façon clair pour sécurisé des données.



Code : PHP
1
2
$RessourceDeConnection = new PDO(...); // ouverture d'une connection
$RessourceDeConnection->query("SELECT id_membre FROM membres WHERE nom = ".$RessourceDeConnection->quote($nom, PDO::PARAM_STR));







Ici on montre un exemple de "quote", mais dans cette exemple il n'y à qu'un seul élément ( nom ).

Comment ce transforme le code avec plusieurs éléments ???



Code : PHP
1
2
3
$RessourceDeConnection->query("UPDATE membre
SET pseudo =".$RessourceDeConnection->quote($id, PDO::PARAM_STR) nom =".$RessourceDeConnection->quote($nom, PDO::PARAM_STR)
WHERE id = ".$RessourceDeConnection->quote($id, PDO::PARAM_INT));







Mon exemple et il correct ?



4) dernière question, doit ton de façon systématique sécurité tout code PDO que l'on réaliser ?

Je m'explique, pour un code PDO qui vas dans la base de données uniquement récupérer des information sans que l'utilisateur n'est à donner à aucun moment que ce soit une information sur ce que l'on veut récupérer ( par exemple un système d'affichage de news ).

Sécurisé le code PDO devient il utilise ???



Voilà merci pour vos futur réponses.

Ps: certain de vous pourrais me retourner un lien sur l'utilisation de PDO en anglais, mais je préférer une explication textuelle car je ne lit pas l'anglais.
                    
                        
Qui a trait à la conscience et à l'étude de celle-ci

                        
 

                    
                		
                

              
                

                  
                    
                      Connecté 
                    
                      Corlorenzo
                    
                  
                  
                    #
                    
                         Posté le 30/12/2010 à 12:06:37
                    
                    
                


                
                    

                
                
                    

Avatar

    

    Avis : Bon
        
    



    





                		
                
                    
                    Merci pour ce tuto clair et intéressant.



J'ai cependant eu un soucis lors de l'importation de la base de donnée dans la partie manipulation (qui doit être dû à une évolution de mySQL).

En effet j'ai dû entrer:



source lecteur:\\repertoire\\world.sql;



au lieu de:



source lecteur:\repertoire\world.sql;



de plus je n'avais pas de guillemets à supprimer.

(j'utilise mySQL 5.1.53).
                    
                		
                

              
            
        

        
            
Voir tous les commentaires