MySQLi, présentation succincte

M

ySQLi est peut-être le gestionnaire de base de données le plus populaire, et il est utilisable avec PHP. Cette page donne une initiation pratique mais pas suffisante: la sécurité dépasse le cadre de cette présentation. Il en est un peu question sur la page consacrée au PHP.

Attention! PHP 7.0 a définitivement remplacé les fonctions mysql_*() par mysqli_*(), mais les nouvelles fonctions ne correspondent pas toujours aux anciennes. Les corrections pour cette section n'ont pas toutes été testées.

Il n'est pas possible de traiter ici des conditions d'utilisation de mySQLi chez votre serveur, qui changent d'un fournisseur à l'autre. Vous trouverez des informations plus pointues sur dev.mysql.com. On considère donc que vous avez un accès aux services PHP et mySQL (c'est-à-dire une adresse de connexion, un identifiant et un mot de passe).

Si vous avez installé PHPmyAdmin sur votre système, vous y accédez par l'adresse http://localhost/phpmyadmin/index.php

1. Base de données

1.1 Connexion au serveur
1.2 Création d'une base de données
1.3 Connexion à la base de données

2. Tables de données

2.1 Création d'une table
2.2 Effacement d'une table
2.3 Structure d'une table

3. Exploitation des données

3.1 Création d'un enregistrement
3.2 Effacement d'enregistrement(s)
3.3 Lecture d'enregistrement(s)
3.4 Modification d'enregistrement(s)

4. Conditions

4.1 Nombres
4.2 Chaînes

5. Autres

5.1 Comptage d'enregistrements
5.2 Tri de table ou de sélection
5.3 Compter et sommer
5.4 Jointure de fichiers

6. Identification par cookie

7. Sécurité

1. Base de données

La location d'un espace WEB chez un serveur implique souvent le service MySQL, à activer ou non. En général, le nom de la base de données (DB pour DataBase) est décidé pour vous, et parfois même le mot de passe. Cela est à vérifier sur votre compte chez le serveur.

Les serveurs utilisant PHP et mySQL ont généralement le service PHPmyAdmin, qui possède une interface graphique permettant la gestion de la base de données. Mais il est possible de tout faire à partir de scripts PHP. La première chose à préciser est la différence entre base de donnée et table. Une base de données est un ensemble de tables, ces dernières contenant les données, sous forme de tableaux à deux dimensions, voir 2.

Il est également possible d'installer un serveur sur son système (LAMP: Linux-Apache-MySQL-PHP) ou WAMP avec Windows. Dans ce cas, l'adresse de connexion est localhost, l'identifiant et mot de passe sont choisis lors de l'installation, et le nom de la base de données lors de votre premier passage sur PHPmyAdmin, ou voir 1.2.

Nous considérons ici une connexion à un serveur extérieur.

1.1 Connexion au serveur

Vous disposez bien évidemment d'une adresse, d'un identifiant et d'un mot de passe de connexion.

Toute page PHP utilisant une base de donnée doit comprendre (ou charger un fichier contenant) ceci:

$connexion=mysqli_connect("sql.monserveur.org", "utilisateur", "motdepasse");

La fin du fichier devrait également comporter:

mysqli_close($connexion);

1.2 Création de base de données

Dans le cas où la création même de la base de donnée est à faire (serveur WEB sans PHPmyAdmin, ou chez vous), il faut créer (un seule fois!) la DB. Le fichier minimal sera:

<?php
$connexion=mysqli_connect("sql.monserveur.org", "utilisateur", "motdepasse");
mysqli_query($connexion, "CREATE DATABASE 'MaDB'");
mysqli_close($connexion);
?>

À ce moment, la base de données est créée mais ne contient rien, même pas une table vide.

mysqli_query($connexion, "DROP DATABASE 'MaDB'"); détruit irrémédiablement la base de données MaDB, ses tables et leurs enregistrements.

1.3 Connexion à la base de données

Pour les prochaines étapes, il faudra toujours que la connexion ait été faite et une base de donnée sélectionnée (mais ce ne sera plus jamais précisé):

<?php
$connexion=mysqli_connect("sql.monserveur.org", "utilisateur", "motdepasse", "basededonnee");

  ... votre script PHP contenant les commandes mysqli_xxx();

mysqli_close($connexion);
?>

mysqli_connect permet une connection qui s'arrête normalement avec le script. Il existe bien la connexion permanente avec p: en préfixe du nom de serveur:

$connexion=mysqli_connect("p:sql.monserveur.org", "utilisateur", "motdepasse");

...mais elle est déconseillée (il vaut mieux fermer les portes) et il n'est pas sûr qu'elle fonctionne dans toutes les configurations.

2. Tables de données

On visualise généralement une table comme étant composée de colonnes de données de même type (les champs: taille, age, tva1, code_postal... ne peuvent comprendre que des lettres non accentuées, tiret_bas et chiffres (pas en première place) et des rangées de données appartenant à un même enregistrement (une personne particulière, un "individu" statistique).

+----+------------+---------+--------+----------+------+
| id | date       | couleur | forme  | grandeur | etc. |
+----+------------+---------+--------+----------+-----
|  0 | 1253467287 | rouge   | ovoïde | petit    | ...
|  1 | 1156726761 | vert    | carré  | moyen    |
|  2 | 1232627637 | vert    | rond   | petit
|  3 | 1023101231 | etc.    | ...    | ...
|  4 | ...
| ...
|

Attention: pour des raisons de sécurité, les noms de tables et de variables doivent avoir des noms non prédictibles (S4dZnd plutôt que name), parce que certaines techniques de crackage se basent sur les noms les plus courants.

2.1 Création d'une table

Étant connecté à une base de données (voir section précédente), on crée une table en indiquant le nom de chaque champ, suivi de la nature de la variable (entier, chaîne, et éventuellement le type d'organisation interne de la table (MyISAM est économe en place utilisée pour les textes et les blobs).

mysqli_query("CREATE TABLE Gens (nb INT AUTO_INCREMENT, nom VARCHAR(25) NOT NULL) ENGINE=MYISAM";

Note: en PHP 5.3, $reponses=mysql_list_dbs($connexion); permettait de lister les tables de la base de données, avec $reponses comme variable-tableau contenant les noms des bases de données. On peut se demander qui a besoin d'interroger sa propre base de données sur les tables créées.

2.2 Effacement d'une table

Pour: mysqli_query($connexion, "DROP TABLE 'Gens'"); efface irrémédiablement une table, sa structure et ses enregistrements éventuels.

2.3 Structure d'une table

Pour ajouter un champ:

mysqli_query($connexion, "ALTER TABLE 'Gens' ADD 'nom' VARCHAR(25) AFTER 'id'");

L'ajout du champ se fait en fin de liste, sauf si l'endroit est précisé par FIRST (en première place) ou AFTER 'nom' (après le champ nom):

Pour retirer un champ d'une table (MySQLi):

mysqli_query($connexion, "ALTER TABLE 'Gens' DROP 'nom'");

3. Création, effacement et modification de données

On suppose que la table est créée et connectée. Ce qui est écrit en majuscule est une instruction mySQL. S'il y a plusieurs champs, il faut les séparer par une virgule.

Le résultat de $res=mysqli_query("Votre requête MySQL") est un booléen pour les requêtes qui ne renvoient aucune donnée, l'identifiant d'un tableau de réponses sinon.

mysqli_query("Votre requête MySQL") or die(mysqli_error()); arrêtera un script si MySQL détecte une erreur (die()) et en donne la raison (mysql()). C'est intéressant pour une mise au point, mais devrait être ensuite être enlevé pour plus de confidentialité: personne ne doit connaître votre cuisine interne.

mysqli_connect_errno(); renvoie le numéro (non nul) de l'erreur.

3.1 Création d'enregistrements

Pour ajouter un nouvel enregistrement (une rangée, un individu):

mysqli_query($connexion, "INSERT INTO Gens (id, nom) VALUES (123, 'Robert')");

Autant de couple champ/valeur que l'on veut ; les valeurs doivent être entourées de guillemets.

3.2 Effacement d'enregistrements

Pour supprimer tous les enregistrements d'une table (vider sans effacer la table elle-même):

mysqli_query($connexion, "DELETE FROM 'Gens'");

Pour supprimer une sélection d'enregistrements, il faut utiliser un filtre (voir Conditions):

mysqli_query($connexion, "DELETE FROM 'Gens' WHERE condition");

3.3 Lecture

Pour la lecture de certains champs de tous les enregistrements d'une table:

$resultats= mysqli_query($connexion, "SELECT nom, prenom FROM 'Gens'");
while($rangee=mysqli_fetch_array($resultats))
  {
  $var1=$rangee[champ1];
  $var2=$rangee[champ2];
  $var3=$rangee[champ3];
  echo "$var1 - $var2 - $var3<br>";
  }

La clause WHERE permet de sélectionner certains enregistrements. Dans le cas d'une recherche d'articles dont le prix doit être inférieur à 10, achetables par paquets d'au plus 5 articles, la requête sera:

$resultats=mysqli_query(SELECT nom, prenom, age FROM gens WHERE age > '40');
while($rangee=mysqli_fetch_array($resultats))
   {
   // voir l'exemple précédent
   }

3.4 Modifier des enregistrements

Pour modifier des champs de tous les enregistrements d'une table:

mysqli_query($connexion, "UPDATE Gens SET champ1='$val1', champ2='$val2');

Pour modifier une sélection d'enregistrements, il faut utiliser un filtre (voir Conditions):

mysqli_query($connexion, "UPDATE Gens SET champ1='$val1', champ2='$val2' WHERE condition");

4. Condition(s) avec WHERE

Les commandes DELETE, UPDATE (supra) et SELECT (infra) traitent par défaut tous les enregistrements d'une table. Le mot WHERE permet de filtrer les enregistrements selon des critères à définir. Par exemple, pour ne détruire que le(s) enregistrement(s) ayant le nombre ou la chaîne '43' dans le champ nbr:

mysqli_query("DELETE FROM table WHERE nbr='43'");

Attention: contrairement à de nombreux langages, le test d'égalité est représenté par un simple =

4.1 WHERE pour les nombres

... WHERE age=50 lorsque le champs age est exactement égal à 50
... WHERE age<107 lorsque le champs age est strictement inférieur à 107
... WHERE age<=107 lorsque le champs age est inférieur ou égal à 107
... WHERE age>18 lorsque le champs age est strictement supérieur à 18
... WHERE age>=18 lorsque le champs age est supérieur ou égal à 18
... WHERE age<>43 lorsque le champs age est différent de 43
... WHERE age BETWEEN 45 AND 55 lorsque le champs age est compris entre 45 et 55 (45 et 55 inclus)

Comme pour les chaînes:

... WHERE age IN (40, 50, 60) si age doit être égal à une de ces trois valeurs
... WHERE age NOT IN (40, 50, 60) si age ne peut être égalà une de ces trois valeurs

5.1 WHERE pour les chaînes

... WHERE champ='cabot' définit l'entièreté du champ
... WHERE champ LIKE 'cab%' définit un début de champ
... WHERE champ LIKE '%ot' définit une fin de champ
... WHERE champ LIKE '%b%' définit un milieu de champ
... WHERE champ LIKE 'ca__t' définit un champ, avec une indétermination sur les 3e et 4e caractères

Notes:

Comme pour les nombres:

... WHERE sigle IN ('TPI', 'ONU') si sigle doit être une de ces valeurs
... WHERE sigle NOT IN ('TPI', 'ONU') si sigle ne peut pas avoir une de ces deux valeurs

5. Autres

5.1 Comptage d'enregistrements

Pour compter le nombre d'enregistrements:

$resultats=mysqli_query("SELECT champ1 FROM table");
$nombre=mysqli_num_rows($resultats);

Pour compter le nombre d'enregistrements répondant à un critère:

$resultats=mysqli_query("SELECT champ1 FROM table WHERE champ>'50'");
$nombre=mysqli_num_rows($resultats);

Pour compter tous les enregistrements d'une table:

$resultat=mysqli_query("SELECT COUNT(*) gens")

5.2 Tri de table ou de sélection

Pour sélectionner des articles, classés par prix décroissant, sous-classement par nom d'article:

$resultat=mysqli_query("SELECT article, prix, nombre FROM catalogue ORDER BY prix DESC, article");

ORDER BY prix DESC donnerait les enregistrements du plus grand au plus petit prix.

Vaut-il mieux ordonner la table ou la sélection des enregistrements? Plus la table aura d'enregistrements et plus le temps de tri, et donc la charge du serveur, croîtra. Si la sélection comporte peu d'enregistrements, nul doute qu'il vaut mieux trier le résultat de la requête plutôt que la table elle-même. Mais si une table reste désordonnée, le tri sur requêtes réitère un même résultat qui sera à chaque fois perdu. Si une table n'est que rarement modifiée et que le tri sur un seul champ est nécessaire, il est peut-être bon de l'ordonner après chaque modification, rendant les tris sur requêtes inutiles.

Pour ordonner une table, avec enregistrement physique de la modification dans la table (non testé):

mysqli_query("ALTER TABLE gens ORDER BY nom, prenom DESC");

5.3 Compter et sommer

Pour compter le nombre de valeurs non NULL, on utilise COUNT(variable). Dans le cas où l'on veut ne compter qu'une fois chaque valeur pour un champ, on utilise COUNT(DISTINCT variable). GROUP BY précise le champ des valeurs qui décident du regroupement. Pour faire une sommation des valeurs d'un champ numérique, on utilise la fonction SQL SUM(variable).

Par exemple, dans le cadre d'une table 'factures_payees' comprenant les champs 'montant', 'prestataire' et 'categorie', pour connaître selon chaque catégorie la sommation des factures ainsi que le nombre de prestataires concernés:

$resultat=mysqli_query("SELECT categorie, SUM(montant), COUNT(DISTINCT prestataire)
FROM factures_payees GROUP BY categorie");

La sortie des résultats se fait de cette manière (ce sont les paramètres 'SUM(montant)' et 'COUNT(DISTINCT prestataire)' qui servent d'indices au tableau $rangee) :

while($rangee=mysqli_fetch_array($resultat))
 {
 $sommation=$rangee['SUM(montant)'];
 $nbr_prest=$rangee['COUNT(DISTINCT prestataire)'];
 $categorie=$rangee['categorie'];
 echo "Cat. $categorie: $sommation pour $nbr_prest<br>";
 }

5.4 Jointure de tables

Pour une base de données comportant un grand nombre d'ouvrages écrits par un nombre (plus) restreint d'auteurs, il est rationnel de ne pas réencoder les prénoms, nom, biographie... de chaque auteur dans la table des ouvrages. On utilise alors deux tables, la première pour les ouvrages, comprenant les champs id, titre... et un champ 'auteur', ou est inscrit un nombre correspondant à l'identifiant de l'auteur décrit dans une seconde table: id, nom...

La lecture de deux tables amène à un traitement plus lourd des lectures pour un affichage des ouvrages écrits par chaque auteur: le premier réflexe est de lire la table des auteurs, et à partir de l'id, refaire une requête sur les ouvrages écrits par chaque auteur:

$resultat=mysql("SELECT id, nom FROM auteurs");
while($data=mysqli_fetch_array($resultats))
  {
  $nom=$data['nom'];
  id=$data['id'];
  echo "<p><b>$auteur</b></p>";
  $resultat1=mysqli_query("SELECT titre FROM ouvrages WHERE aut='$id'");
  while($data1=mysqli_fetch_array($resultats1))
    {
    $titre=$data1['titre'];
    echo "$titre<br>";
    }
  }

Au total, cela nous fait une requête sur la table des auteurs, plus autant de requêtes dans la table des ouvrages qu'il y a d'auteurs. Il est possible de simplifier le travail par une jointure des deux tables:

$res=mysql("SELECT auteurs.nom, ouvrages.titre FROM auteurs, ouvrages WHERE auteurs.id=livres.aut");
$nom0="";
while($data=mysqli_fetch_array($res))
  {
  $nom=$data['auteurs.nom'];
  $titre=$data['titre'];
  if($nom!=$nom0) { echo "<p><b>$auteur</b></p>"; $nom0=$nom; }
  echo "$titre<br>";
  }

La sélection portent sur deux table: auteurs et ouvrages; les champs ont été préfixés du nom de la table dont ils font partie. La jointure se fait dans la clause WHERE, ou le numéro unique pour chaque auteur doit correspondre au champ auteur de la table des ouvrages.

Si vous devez octroyer une connexion sql sous condition d'identification, il n'est pas question de la recommencer à chaque page. Il est possible de s'en sortir par les cookies. Trois fichiers.

<html><body>
<form method='post' action='log.php'>
<table><tr><td align='right'>
Identifiant <input name='id' size=10 value='' />
</td></tr><tr><td align='right'>
Mot de passe <input name='mp' size=10 value='' />
</td></tr><tr><td align='right' valign='top'>
<input type=submit value='Envoyer' />
</td></tr></table>
</form>
</body></html>

Le fichier log.php se charge de vérifier le mot de passe et de délivrer le cookie:

<?php

$id=$_POST[id];
$mp=$_POST[mp];

$connexion=mysqli_connect($adresse_connexion,$votre_identifiant,$votre_mot_de_passe);
mysqli_select_db($nom_DB,$connexion);

$resultats=mysqli_query("SELECT id,mp FROM mdp WHERE id='$id'");

if ($mp=$rangee['mp'])
 {
  set_cookie('nom_de_cookie','chaine');
 }

header('Location: page.php');
?>

Pour des raisons de protocole, il est nécessaire que set_cookie() soit écrit avant tout echo "<html><head>...";

Tous les fichiers devant se connecter à la base de donnée devront contenir cette séquence:

<?php

if ($HTTP_COOKIE_VARS['nom_de_cookie']=='chaine')
 {
  $connexion=mysqli_connect($adresse,$identifiant,$motdepasse);
  mysqli_select_db($connexion, $nom_DB);
 }

...votre code...

mysqli_close($link);
?>

Notons qu'il ne s'agit pas de la méthode la plus sécurisée, les cookies pouvant être récupérés ou interceptés.

7. Sécurité

Protéger la base de donnée est du ressort du langage du script utilisé pour l'exploiter. Pour PHP, voir cette page.

Utiliser le protocole https

Les données envoyées par un internaute au serveur de votre site peuvent faire le tour de la terre. Il faut donc qu'elles soient transmises par liaison sécurisée, ce que permet le protocole https («s» pour «secure»).

Utiliser des noms improbables

Certaines techniques s'attaquant à des pages PHP/MySQL naïves permettent de lire des enregistrements, voire les effacer, ainsi que des tables. C'est surtout vrai quand on peut facilement deviner leur nom. Utilisez donc toujours des noms de tables et de champs qui «ne tombent pas sous le sens», donc pas de main, personnes, users, articles... pour les tables, ni de id, name, user, age, password... pour les champs. C'est d'autant plus vrai pour l'installation de scripts récupérés sur Internet qui n'ont de secret pour personne.

Vérifiez les valeurs saisies par les internautes

Une technique souvent utilisée est l'«injection de code» par introduction de chaînes spéciales dans un formulaire (POST) ou d'une adresse URL (GET), qui parvient à modifier la requête en permettant la lecture de données, la destruction de champs ou de table, ou la connexion en tant qu'administrateur.

Il faut donc toujours vérifier les données renvoyées par formulaires et URL avant de les confier à SQL.

Ne jamais stocker les mots de passe en clair

La sécurité n'est jamais sûre à 100%, il est donc possible que la table contenant les identifiants et mots de passe ait été lue. Tout n'est pas perdu si les mots de passe n'ont pas été codé en clair, mais transformés en bouillie de chiffres et de lettres avant stockage par une fonction de hashage.