+- 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 : Bonnes pratiques SQL (/showthread.php?tid=1303)
Bonnes pratiques SQL - pascal - 19-06-2007
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
Code PHP :
<?php
// 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. selection 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 numero 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 :
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. écrire la requête dans une variable
comme ça :
Code PHP :
<?php
// la requete dans une variable
$query = "SELECT nom, xp FROM joueur WHERE joueur_id = 1";
ça permet de faire du debug visuel, via un simple :
Code PHP :
<?php
// affichage de la requête
echo $query;
// puis on arrete 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.
3. intégrer les paramètres proprement dans la requête
on ne veut pas :
Code PHP :
<?php
$query = "SELECT nom, xp FROM joueur WHERE joueur_id = 1";
mais quelquechose 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 ( _contre les injections SQL_ )
_ vérifier les données à intégrer à la requête ( _cohérence des données_ )
_ intégrer ces données dans la requête
filtrer les données
( paragraphe à compléter )
via mysql_real_escape_string
et aussi pour vérifier la config :
get_magic_quotes_gpc
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 :
Code PHP :
<?php
$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 :
Code PHP :
<?php
$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 :
Code PHP :
<?php
// tests sur les parametres
// [... à é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);
4. vérifier le succès de l'éxécution
mysql_query renvoie FALSE en cas d'erreur, ou une ressource en cas de succès. on teste donc sa valeur de retour :
Code PHP :
<?php
// 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...
5. vérifier le nombre de lignes affectées par la requête
on vérifie le nombre de lignes attendues :
_ zero
_ 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 :
Code PHP :
<?php
// 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
Code PHP :
<?php
// 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{
// recupé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
Code PHP :
<?php
// 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{
// recupé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:
Code PHP :
<?php
$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 :
Code PHP :
<?php
$query = "SELECT SUM(xp) AS somme_xp FROM joueur";
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 chaînes 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
RE: Bonnes pratiques SQL - Kevin - 19-06-2007
L'aide pour ce tutoriel ce trouve à cette adresse :
http://www.jeuweb.org/board/showthread.php?tid=1854&pid=20045#pid20045