Bonnes pratiques SQL

Aide concernant ce tuto

Les objectifs de ce tutoriel sont simples :

• permettre d'utiliser MySQL avec PHP
• permettre d'éviter les erreurs courantes
• développer l'autonomie de chacun

Ce tutoriel ne concerne pas la syntaxe du SQL, vous n'apprendrez pas :

• la syntaxe des SELECT, UPDATE, INSERT, DELETE
• comment faire une procédure stockée
• comment créer une base de données

La connexion à la base de données se fait en deux étapes :

• connexion au serveur de bases de données
• sélection de la base de données

// le serveur
$host = 'localhost';
// l'utilisateur
$user = 'root';
// et le mot de passe associé
$pass = 'lemotdepasse';
//  1. connexion au serveur
$connexion = mysql_connect($host, $user, $pass);
if( $connexion == FALSE )
{
   echo 'erreur de connexion au serveur<br/>';
   echo mysql_errno() . ": " . mysql_error();
   exit;
}
// 2. sélection de la base
$db_name = 'monjeu';
$selectionDb = mysql_select_db($db_name, $connexion);
if( $selectionDb == FALSE )
{
    echo 'erreur de selection de la base<br/>';
    echo mysql_errno() . ": " . mysql_error();
    exit;
}

Si la connexion ou la sélection plante, les fonctions mysql_connect et mysql_select_db renverront FALSE, ce qui entrainera l'exécution du if associé, indiquant où ça a planté.

Les fonction mysql_errno() et mysql_error() indiquent respectivement le numéro et le message de l'erreur. plus d'informations sur la page des codes d'erreurs mysql .

Que faire si ça a planté ?

• vérifier les paramètres de connexion
• vérifier que le serveur MySQL est en cours d'exécution, via phpMyAdmin par exemple

Une fois la connexion établie, on peut lancer des requêtes. Pour cela, on utilise la fonction mysql_query .

Les règles à respecter pour l'exécution d'une requête :

Pour cela on écrit la requête et on la lance dans phpMyAdmin. c'est tout bête et ça rapporte gros :

• on se fait jeter tout de suite en cas d'erreur
• on corrige très rapidement toute faute de frappe
• on n'est pas paumé au beau milieu d'un script PHP. on a juste la requête, si ça ne marche pas, ça vient de la requête
• c'est simple à corriger car on n'a que ça à l'écran

La requête fonctionne dans phpMyAdmin ? très bien! on peut commencer à l'intégrer au script.

Comme ça :

// la requête dans une variable
$query = "SELECT nom, xp FROM joueur WHERE joueur_id = 1";

Ça permet de faire du debug visuel, via un simple :

// affichage de la requête
echo $query;
// puis on arrête le script
exit;

Si ça fonctionne dans le script PHP, on peut continuer, en intégrant les vraies valeurs des variables dans la requête.

On ne veut pas :

$query = "SELECT nom, xp FROM joueur WHERE joueur_id = 1";

Mais quelque chose de dynamique, avec par exemple la valeur de joueur_id provenant d'un formulaire en POST, ou toute autre variable.

Ici on fait les choses dans l'ordre :

( paragraphe à compléter ) via mysql_real_escape_string et aussi pour vérifier la config : get_magic_quotes_gpc Ceci permet d'éviter les injections SQL

Ceci pour :

• éviter les plantages
• éviter la triche / le piratage
• être sûr de ce que l'on met dans la requête

Pour cela, on vérifie, selon les besoins :

• le type
• la valeur
• un domaine de valeurs
• la présence de caractères non valide
• la taille de la chaine
• …

Si une ou plusieurs conditions ne sont pas respectées, on l'indique à l'écran ( if, echo ) . sinon, on passe à l'étape 2

Pour cela, on peut concaténer les données :

$query = "SELECT nom, xp FROM joueur WHERE joueur_id = " . $_POST['joueur_id'];

C'est faisable, mais source d'erreur. la plupart du temps, on ne s'y retrouve plus à cause des apostrophes ' autour des chaines, qui rendent la concaténation peu lisible.

On préfèrera concaténer avec la fonction sprintf :

$query = sprintf("SELECT nom, xp FROM joueur WHERE joueur_id = %u", $_POST['joueur_id'] );
// %u = c'est un entier, un nombre décimal non signé

En pratique, on a une requête à trous, avec des %s, %d, %u à la place des paramètres puis la liste des paramètres, dans leur ordre d'apparition, séparés par une virgule.

C'est très lisible et simple à modifier, les risques de plantage liés aux apostrophes ' sont minimisés par rapport à la concaténation.

On peut ensuite lancer la requête :

// tests sur les paramètres
// [... à écrire selon votre code... ]
// création de la requête
$query = sprintf("SELECT nom, xp FROM joueur WHERE joueur_id = %u", $_POST['joueur_id'] );
// exécution de la requête
$resultat = mysql_query($query);

mysql_query renvoie FALSE en cas d'erreur, ou une ressource en cas de succès. on teste donc sa valeur de retour :

// exécution de la requête
$resultat = mysql_query($query);
// test de la valeur de retour
if( $resultat === FALSE )
{
   echo "erreur dans la requete : " . $query . "<br/>";
   echo mysql_errno() . ": " . mysql_error();
   exit;
}

Il y a un problème dans la requête ? on est prévenu tout de suite ! pratique !

Mais une requête qui ne plante pas n'est pas forcément une requête qui fait ce qu'on lui demande de faire…

On vérifie le nombre de lignes attendues :

• zéro
• une seule
• plusieurs

Si ce n'est pas le bon nombre, on a soulevé un problème.

Pour vérifier, on utilise les fonctions :

• mysql_num_rows pour les requêtes de type SELECT ou SHOW
• mysql_affected_rows pour les requêtes de type INSERT, UPDATE, REPLACE ou DELETE

Par exemple :

// exécution de la requête
$resultat = mysql_query($query);
// test de la valeur de retour
// [...]
// test du nombre de lignes
if(mysql_num_rows($resultat) != 1)
{
echo "requete :" . $query;
echo "<br/>erreur, on n'a pas le nombre de lignes attendues . on a : " . mysql_num_rows($resultat);
exit;
}

Si le nombre obtenu n'est pas le bon, on peut le gérer ( if, echo )

En résumé :

• tester la requête dans phpMyAdmin
• copier la requête dans une variable PHP et l'exécuter
• vérifier les variables à intégrer dans la requête
• intégrer les variables dans la requête, via sprintf
• tester le résultat de mysql_query
• vérifier le nombre de lignes affectées par la requête

Pour récupérer des données de l'exécution d'une requête, on utilise les fonctions mysql_fetch_* :

• mysql_fetch_assoc tableau, nom des clés = nom des champs
• mysql_ fetch_array tableau, nom des clés = nom des champs et/ou index numérique
• mysql_fetch_row tableau, nom des clés = index numérique
• mysql_fetch_object objet, nom des membres = noms des champs

L'utilisation de mysql_fetch_assoc est recommandée : c'est le plus lisible, car les noms des clés correspondent aux noms des colonnes renvoyées.

On vérifie que l'on a bien une seule ligne de résultat, puis on récupère cette ligne

// exécution de la requête
$resultat = mysql_query($query);
// test de la valeur de retour
// [...]
// test du nombre de lignes
if(mysql_num_rows($resultat) != 1)
{
echo "requete :" . $query;
echo "<br/>erreur, on n'a pas le nombre de lignes attendues  . on a : " . mysql_num_rows($resultat) . " ligne(s) au lieu d'une seule ligne";
exit;
}else{
// récupération de la ligne dans un tableau associatif
$ligne = mysql_fetch_assoc($resultat);
// affichage à l'écran  du contenu de la ligne
echo '<pre>';
print_r($ligne);
echo '</pre>';
}

On vérifie que l'on a bien une ou plusieurs lignes de résultat, puis on récupère ces lignes

// exécution de la requête
$resultat = mysql_query($query);
// test de la valeur de retour
// [...]
// test du nombre de lignes
if(mysql_num_rows($resultat) <= 1)
{
echo "requete :" . $query;
echo "<br/>erreur, on n'a pas le nombre de lignes attendues  . on a : " . mysql_num_rows($resultat) . " ligne au lieu de plusieurs lignes";
exit;
}else{
// récupération des lignes dans un tableau associatif
while( $ligne = mysql_fetch_assoc($resultat)
{
// affichage à l'écran  du contenu de chaque ligne
echo '<pre>';
print_r($ligne);
echo'q</pre>';
}
}

On peut avoir une expression calculée dans une requête:

$query = "SELECT SUM(xp) FROM joueur";

Que doit-on utiliser pour récupérer cette valeur? $ligne['SUM(xp)'] ? le plus simple est d'utiliser un alias de champ en sortie :

$query = "SELECT SUM(xp) AS somme_xp FROM joueur";

Dans ce cas, on récupèrera tout simplement $ligne['somme_xp'] .

• mots clé SQL en MAJUSCULE, nom des éléments de la base ( _tables, champs…_ ) en minuscule : pour la lisibilité
• jointures réelles ( JOIN, USING ) au lieu de jointures dans la clause WHERE : c'est plus lisible
• on entoure la requête de “guillemets” et les chaines par des 'apostrophes', à l'intérieur de la requête
• on évite d'utiliser des mots réservés pour nommer les champs et les tables; donc pas de:

or, password, force, select

• on utilise un fichier unique pour la connexion à la base, que l'on inclue avant chaque script

Composée par pascaltje sur le forum de jeuphp.net
Mis en forme par Zamentur