PHP et l’extension MySQLi (Prepared Statements)

Introduction

Une nouvelle extension MySQL (ext / MySQLi ou MySQL Improved Extension) pour PHP a été implémentée pour supporter les nouvelles fonctionnalités de MySQL 4.1 (commandes préparées, etc.).

Cet article présente les objectifs de cette nouvelle extension, son utilisation basique avec PHP et la mise en œuvre des commandes préparées (Prepared Statements, nouveauté MySQL 4.1).

L’extension MySQLi (PHP 4 / PHP 5)

Fonctionnalités principales

Parmi les fonctionnalités principales de l’extension MySQLi :

• Performances accrues avec la mise en œuvre du nouveau protocole binaire d’échange de données (binary protocol) : les requêtes ne sont plus converties en chaînes de caractères lourdes entre le client et le serveur, ces dernières sont transmises avec un nouveau protocole optimisé au format binaire. Certaines opérations sont 40 fois plus rapides avec l’extension MySQLi par rapport à l’ancienne extension.
• Possibilité d’utiliser un mode procédural ou un mode orienté objet dans l’utilisation de l’extension MySQLi, le mode orienté objet étant la grande nouveauté de l’extension MySQLi.
• Compatibilité avec les nouveautés MySQL 4.1 (prepared statements, character sets, etc.)
• Support additionnel pour l’activation des traces, le debuggage, le load balancing et les fonctionnalités liées à la réplication.

Utilisation basique

Toutes les fonctions PHP liées à l’extension MySQLi commencent par le préfixe mysqli_%.

Exemple mode procédural

Le mode procédural ne diffère pas de l’utilisation classique de la précédente extension ext /mysql.

<?php
 $link = mysqli_connect("localhost", "my_user", "my_password", "world");
 
 /* Vérification de la connexion */
 if (mysqli_connect_errno()) {
     printf("Connect failed: %s\n", mysqli_connect_error());
     exit();
 }
 
 $query = "SELECT Name, CountryCode FROM City ORDER by ID DESC LIMIT 50,5";
 
 if ($result = mysqli_query($link, $query)) {

    /* Récupération du tableau associé */
    while ($row = mysqli_fetch_row($result)) {
       printf ("%s (%s)\n", $row[0], $row[1]);
    }
 
    /* Libération du jeu de résultats */
    mysqli_free_result($result);
 }
 
 printf("System status: %s\n", mysqli_stat($link));
 mysqli_close($link);
?>

Exemple mode Orienté objet

<?php
 $mysqli = new mysqli("localhost", "my_user", "my_password", "world");

 /* Vérification de la connexion */
 if (mysqli_connect_errno()) {
     printf("Connect failed: %s\n", mysqli_connect_error());
     exit();
 }

 $query = "SELECT Name, CountryCode FROM City ORDER by ID DESC LIMIT 50,5";

 if($result = $mysqli->query($query)) {
 /* Récupération du tableau associé */
   while ($row = $result->fetch_row()) {
       printf ("%s (%s)\n", $row[0], $row[1]);
   }
   /* Libération du jeu de résultats */
   $result->close();
 }

 printf ("System status: %s\n", $mysqli->stat());
 $mysqli->close();
?>

À la différence du mode procédural où un objet ressource $link est utilisé (ex : mysql_query($link,query) ), dans le mode orienté objet, un objet mysqli disposant de méthodes est créé avec la syntaxe :

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

et les méthodes de l’objet mysqli sont appliquées sur l’objet en n’ayant plus à se soucier d’un objet ressource :

$mysqli->query($query);

Il en est de même pour les résultats en mode orienté objet : un objet de type mysqli_result disposant de méthodes et de propriétés est créé lors du retour de résultats :

$result->fetch_row();

Un autre type d’objet existe avec l’extension MySQLi et concerne uniquement les commandes préparées (prepared statements) : il s’agit de l’objet mysqli_stmt. Cet objet sera abordé dans le paragraphe 3.

Mode orienté objet : objets, méthodes et propriétés de l’extension MySQLi

Dans le tableau ci-dessous sont récapitulés les types d’objet, méthodes et propriétés disponibles avec l’extension MySQLi en mode orienté objet.

Prepared Statements (Commandes Préparées)

Les commandes préparées (prepared statements) fournissent la possibilité de créer des requêtes plus sécurisées, plus performantes et plus simples à écrire.

(pour plus d’informations sur les commandes préparées, voir l’article MySQL 4.1 et les commandes préparées).

Paramètres substituables liés à une commande préparée (bound parameters)

L’objectif est d’utiliser un modèle de requête une seule fois, modèle dans lequel les valeurs des paramètres substituables… (bound parameters) sont données dynamiquement pour l’exécution.

Exemples :

INSERT INTO City (ID, Name) VALUES (?, ?);
SELECT id FROM t_si_users WHERE login like ?;

Avantages

Réduction des coûts de parsing

Ce modèle de requête (Prepared Statement) est validé et créé au niveau du serveur MySQL et pourra être exécuté plusieurs fois de suite avec des valeurs différentes pour les paramètres substituables.

Dans cette cinématique, la requête est parsée une bonne fois pour toutes au niveau du serveur MySQL, ce qui accroît les performances, et retourne un handle qui n’est autre qu’un objet mysqli_stmt permettant de faire référence à cette commande préparée stockée dans un buffer spécial du serveur MySQL.

Réduction de la taille des échanges réseaux

L’extension MySQLi utilise le nouveau protocole binaire (binary protocol), ce qui réduit considérablement la taille des échanges entre le serveur et le client, en effet dans l’ancienne extension les requêtes étaient échangées entre le client et le serveur avec des chaînes de caractères plus volumineuses.

Sécurité sur les valeurs des paramètres substituables

La sécurité est renforcée pour les valeurs données aux paramètres substituables et il n’est plus nécessaire par exemple d’utiliser la fonction mysql_real_escape_string( ) pour la gestion des quotes et autres caractères spéciaux.

Le client et le serveur avec l’extension MySQLi travaillent communément pour gérer la sécurité sur l’échange des données (caractères spéciaux, etc.), notamment pour les paramètres substituables (bound parameters).

Exemple

L’exemple qui suit repose sur le modèle orienté objet de l’objet MySQLi.

<?php
   $mysqli = new mysqli('localhost', 'user', 'password', 'world');
   
   /* Préparation de la commande */ 
   $stmt = $mysqli->prepare("INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)");

   /* Association de valeurs aux paramètres */ 
   $stmt->bind_param('sssd', $code, $language, $official, $percent);
   
   $code = 'DEU'; $language = 'Bavarian';  $official = "F"; $percent = 11.2;
   
   /* Exécution de la commande préparée */ 
   $stmt->execute();
   
   printf("%d Row inserted.\n", $stmt->affected_rows);
   
   $stmt->close(); /* Fermeture de la commande */  
   $mysqli->close(); /* Fermeture de la connexion */

La fonction bind_param() de l’objet mysqli_stmt a comme premier paramètre une chaîne de caractères qui est utilisé pour spécifier comment les données variables doivent être traitées (chaînes de caractères, numérique, blob…).

Dans l’exemple plus haut, 'sssd' indique que les trois premiers paramètres $code, $language et $official doivent être envoyés comme chaînes de caractères, alors que le quatrième paramètre $percent contiendra une valeur de type double ou float.

Le tableau ci-dessous regroupe les types possibles à utiliser avec la méthode bind_param :

Ces types permettent à l’extension MySQLi d’encoder les données avec une meilleure efficacité.

Résultats liés à une commande préparée

Autre grande nouveauté : l’extension MySQLi permet de lier automatiquement des variables PHP aux valeurs des champs dans le résultat d’une commande préparée grâce à la méthode bind_result de l’objet mysqli_stmt :

Exemple pratique :

<?php
    $mysqli = new mysqli("localhost", "user", "password", "world");
    
    /* Commande préparée */
    
    if ($stmt = $mysqli->prepare("SELECT Code, Name FROM Country ORDER BY Name LIMIT 5"))
    {
      $stmt->execute();
      /* Association des résultats à des variables PHP */ 
      $stmt->bind_result($col1, $col2);
      
      /* Récupération des valeurs */
      while ($stmt->fetch()) {
          printf("%s %s\n", $col1, $col2);
      }
      
      $stmt->close();  /* Fermeture de la commande */ 
    } 
    
    $mysqli->close();  /* Fermeture de la connexion */

?>