MySQL 4.1 - Prepared Statements

Logo

Introduction

Les commandes préparées (ou Prepared Statements) sont une nouvelle fonctionnalité de la version 4.1 de MySQL. L’objectif de cet article est de montrer l’intérêt des commandes préparées pour une future mise en œuvre avec PHP 5 ( à travers l’extension MySQLi).

Généralités sur les commandes préparées

Définition des commandes préparées

Les commandes préparées (Prepared Statements) permettent de mettre en œuvre une commande une seule fois et d’exécuter cette dernière commande plusieurs fois avec différents paramètres. Les commandes préparées ont le gros avantage de ne pas avoir à regénérer à chaque fois une requête avec des paramètres différents.

Exemple de commande préparée :

select a.libelle, b.libelle
          from t_si_doc_sections a, t_si_doc_folders b
          where a.id_fld = b.id
          and a.libelle like ?
          and b.libelle like ?;

? dans la commande préparée ci-dessus est le paramètre substituable et une valeur devra être fournie pour ce paramètre lors de l’exécution de la commande préparée.

Avantages de commandes préparées

Il y a de nombreux avantages à utiliser les commandes préparées dans les applications, et parmi ces raisons : performances et sécurité.

  • Les commandes préparées aident à l’amélioration de la sécurité en séparant la logique SQL des données fournies. Plus particulièrement, dans un contexte de requêtes simples, une attention toute particulière est portée sur les données fournies par l’utilisateur (utilisation de caractères spéciaux comme les simples quotes, les doubles quotes, les backslashs, etc.). Avec les commandes préparées, cet aspect devient inutile et la mise en place de fonctions spéciales traitant les caractères spéciaux n’est plus nécessaire, MySQL prend en charge ces derniers avec les prepared statements.
  • Les commandes préparées améliorent les performances car la requête est parsée une seule fois. Aussi quand la requête est exécutée plusieurs fois, le parsing n’est pas réévalué à chaque fois. (Dans les futures versions de MySQL, il sera également possible de mettre en cache les plans d’exécution des commandes préparées).
  • Les performances sont également accrues car les commandes préparées utilisent le nouveau protocole binaire. Dans le protocole traditionnel de MySQL, toutes les données sont converties par le client MySQL en chaîne de caractères, lesquelles sont généralement plus grandes que les données originales, et envoyées à travers le réseau vers le serveur. Le protocole binaire supprime cette conversion, tous les types sont envoyés au format binaire natif ce qui décroît l’utilisation de la CPU et l’utilisation du réseau.

Limitations

Les commandes préparées ne peuvent être utilisées que sur certaines commandes DML et DDL (INSERT, REPLACE, UPDATE, DELETE, CREATE TABLE et SELECT). Le support pour d’autres types de requêtes sera ajouté dans les versions ultérieures.

Parfois, les commandes préparées en fonction de leur complexité peuvent être plus lentes que les requêtes régulières.

API utilisables pour les commandes préparées

La plupart des langages de programmation utilisables avec MySQL disposent déjà d’un support pour les commandes préparées.

Il n’existe pas pour l’heure actuelle de support pour les commandes préparées avec le driver ODBC MySQL.

Syntaxes SQL des commandes préparées

Il existe bien entendu une interface SQL pour les commandes préparées, lesquelles se resument à trois nouvelles commandes SQL :

  • PREPARE
  • EXECUTE
  • DEALLOCATE PREPARE

Ces commandes SQL ne bénéficient pas du nouveau protocole binaire ! Aussi elles ne sont généralement utilisées qu’à des fins de tests ou lorsqu’une API native n’est pas disponible.

Syntaxes :

PREPARE stmt_name FROM preparable_stmt;
EXECUTE stmt_name [USING @var_name [, @var_name] ...];
DEALLOCATE PREPARE stmt_name;

Exemples pratiques :

mysql> PREPARE stmt1 FROM 'select a.libelle, b.libelle 
        from t_si_doc_sections a, t_si_doc_folders b 
        where a.id_fld = b.id 
        and a.libelle like ? 
        and b.libelle like ? ';
Query OK, 0 rows affected (0.01 sec)
Statement prepared
mysql> SET @parent = 'Syb%';
mysql> SET @child = 'Adm%';

mysql> EXECUTE stmt1 USING @child, @parent;
+----------------+--------------+
| libelle        | libelle      |
+----------------+--------------+
| Administration | Sybase A.S.E |
| Administration | Sybase R.S.  |
+----------------+--------------+
mysql> DEALLOCATE PREPARE stmt1;

Dans le second exemple, le texte de la commande SQL est sous la forme d’une variable utilisateur :

mysql> SET @sqlcmd = 'select a.libelle, b.libelle 
            from t_si_doc_sections a, t_si_doc_folders b 
            where a.id_fld = b.id 
            and a.libelle like ? 
            and b.libelle like ? ';

mysql> PREPARE stmt1 FROM @sqlcmd;
Query OK, 0 rows affected (0.01 sec)
Statement prepared
mysql> SET @parent = 'Syb%';
mysql> SET @child = 'Adm%';

mysql> EXECUTE stmt1 USING @child, @parent;
+----------------+--------------+
| libelle        | libelle      |
+----------------+--------------+
| Administration | Sybase A.S.E |
| Administration | Sybase R.S.  |
+----------------+--------------+
mysql> DEALLOCATE PREPARE stmt1;