JeuWeb - Crée ton jeu par navigateur
Article Bonnes pratiques SQL - Version imprimable

+- JeuWeb - Crée ton jeu par navigateur (https://jeuweb.org)
+-- Forum : Discussions, Aide, Ressources... (https://jeuweb.org/forumdisplay.php?fid=38)
+--- Forum : Programmation, infrastructure (https://jeuweb.org/forumdisplay.php?fid=51)
+--- Sujet : Article Bonnes pratiques SQL (/showthread.php?tid=8206)



Bonnes pratiques SQL - Xenos - 14-09-2020

Bonnes pratiques SQL

Introduction

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


1. Se connecter à la 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


2. Exécuter une requête

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 :

2.1. tester la 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.

2.2. écrire la requête dans une variable

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.

2.3. intégrer les paramètres proprement 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 :

Filtrer les données

( 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

Vérifier les données à intégrer à la requête

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

Intégrer les données dans la requête

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);

2.4. vérifier le succès de l'exécution

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…

2.5. vérifier le nombre de lignes affectées par la requête

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


3. récupérer les données

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.

Récupérer un seul résultat

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>';
}

Récupérer plusieurs résultats

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>';
}
}

Comment récupérer une expression calculée?

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'] .

4. conseils généraux
  • 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