bases de données en PHP sommaire S2 développement web Objectifs PPN : Échange de données client/serveur : principe. Éléments du langage. Formulaire de saisie. Liaison avec une base de données (en TD). Présentation la BDD est un moyen structuré pour stocker les données. PHP peut travailler avec plusieurs type de bases : • MySQL (fonctionne comme un serveur), • MySQL lite (fonctionne sous forme de fichier - sans serveur) • Oracle (la c’est du GROS !), • Microsoft SQL server, • ODBC sur ACCESS • … 5 étapes pour travailler avec une BDD très simple 1 Ouvrir une connexion avec le serveur MySQL 2 Sélection de la base le plus intéressant 3 Lancer les requêtes 4 Exploiter les résultats des requêtes 5 Fermer la connexion avec le serveur MySQL rien à écrire Les forces en présence 1. Le serveur WEB et le serveur MYSQL sur la même machine Code PHP Ouvrir une connexion port : 3306 Récupérer les informations Serveur MYSQL Serveur APACHE (port 80) 2. deux machines distinctes pour WEB et MYSQL Code PHP Ouvrir une connexion Récupérer les informations Serveur APACHE Serveur MYSQL PDO Pour travailler efficacement avec les bases de données, nous utiliserons un objet nommé PDO PHP Data Object PDO est un socle qui nous permettra de travailler de la même manière avec n'importe quel type de base. PDO met en place 3 classes de travail : • PDO pour faire le lien sur la base • PDOstatement qui permet de travailler avec les requêtes • PDOException pour gérer les erreurs Connexion à la base la connexion sur la base MYSQL est sécurisée : la base de données est caractérisée par son DSN (Data Source Name) Exemple de nom de DSN : mysql, oci pour oracle , pgsql pour postgre $db = new PDO ( DSN de la base , nom de l'utilisateur , mot de passe ); Il faut créer un objet à partir de la classe PDO. $db est donc une variable objet qui représente une connexion sur la base; cette connexion sera utilisée pour toutes les opérations sur la base de données. Exemple : Pour permettre à l'utilisateur 'root', mot de passe 'secret' de travailler avec la base 'mabase' en local, on écrit : $db = new PDO ( 'mysql:host=localhost;dbname=mabase', 'root', 'secret' ); Connexion à la base Pour travailler avec les bases, il faut toujours s'organiser pour se simplifier (ultérieurement) le travail. Nous allons créer un fichier nommé config.php qui contiendra tout ce qui relève de la configuration de notre projet sous forme de constantes. projet-web/ |-- index.php |-- config.php Cette organisation est intéressante car elle permet de gérer 2 configurations : • une pour le développement en local (avec WAMPP) • une pour le serveur de production (OVH pour nous) Exemple : define( define( define( define( 'DB_USER', 'root'); 'DB_PWD', 'secret'); 'DB_DBNAME', 'mabase'); 'DB_DSN', 'mysql:host=localhost;dbname=' . DB_DBNAME); $db = new PDO(DB_DSN, DB_USER, DB_PWD); déconnexion à la base La déconnexion La connexion à la base prend fin quand l'exécution du script PHP est terminée. Mais, il est possible d'anticiper la déconnexion en mettant fin explicitement à la connexion pour libérer les ressources mémoire du serveur MYSQL. Exemple : $db = null; Envoi de requêtes Les opérations à réaliser avec la base nécessitent l'envoi de requêtes SQL au serveur. Il existe 2 situations que l'on traite différemment : • les requêtes qui retournent un résultat type : SELECT ( EXPLAIN, SHOW, ...) méthode query() • les requêtes qui ne retournent pas de valeurs type : INSERT, UPDATE, DELETE elles indiquent simplement le nombre de lignes concernées par le traitement méthode exec() Requêtes de sélection Il faut comprendre qu'une requête de sélection n'affiche pas directement les résultats. Les données sont simplement mise en mémoire. Il faut les chercher et les afficher. Pour illustrer, nous utiliserons une table users composée de 5 champs Exemple : pour rechercher les informations name et email des 2 premiers utilisateurs, il faut écrire : $sql = "SELECT name, email FROM users LIMIT 0, 2"; $results = $db->query($sql); un debug sur la variable $results affiche : object(PDOStatement)#2 (1) { ["queryString"]=> string(58) "SELECT name, email FROM users LIMIT 0, 2" } On constate que $results est bien une variable objet type PDOstatement; mais dont le contenu n'est pas directement exploitable en l'état. Requêtes de sélection On peut récupérer d'un seul coup toutes les données dans un tableau associatif. On doit utiliser la méthode fetchAll de l'objet PDOstatement : Exemple : $users = $results->fetchAll(PDO::FETCH_ASSOC); un debug sur la variable $users affiche : array(2) { [0]=> array(2) { ["name"]=> string(7) "Timothy" ["email"]=> string(33) "[email protected]" } [1]=> array(2) { ["name"]=> string(5) "Lance" ["email"]=> string(27) "[email protected]" } } $users[0]['name'] = 'Timothy' $users[0]['email'] = '[email protected]' $users[1]['name'] = 'Lance' $users[1]['email'] = '[email protected]' $users est donc un tableau associatif que l'on peut parcourir avec une boucle foreach : Exemple : foreach( $users as $user ) { d($user, 'data'); } Requêtes de sélection L'autre approche consiste à aller chercher les valeurs une par une. Exemple : while( $user = $results->fetch(PDO::FETCH_ASSOC)) { d($user, '$user'); } Ici, pour lire toutes les lignes du résultat, on écrit une boucle while qui effectue un nouvel appel de la méthode fetch() pour chaque ligne. Cette boucle teste s’il y a encore des lignes à lire; la méthode fetch() retournant la valeur NULL quand il n’y en a plus. Requêtes d'insertion Ajoutons un utilisateur "marcel pagnol" : Exemple : $sql = " INSERT INTO users (name, email, pwd, country) VALUES ('PAGNOL', '[email protected]', MD5('mon_enfance'), 'France') "; $result = $db->exec($sql); Dans la variable $result, on peut avoir : • FALSE si un problème d'exécution est apparu (souvent une erreur de syntaxe SQL) • le nombre de lignes affectées si le traitement s'est exécuté normalement Remarque 1 : il est pertinent de gérer les erreurs possibles if ($result === FALSE) { echo "ERREUR pendant le traitement"; } else { echo "nombre de lignes affectée : " . $result; } Remarque 2 : si on veut connaitre le numéro (généralement auto-incrementé) du dernier utilisateur ajouté : $iduser = $db->lastInsertId(); Requêtes édition Modifions le mot de passe de "marcel pagnol" : Exemple : $sql = "UPDATE users SET pwd = MD5('mon_enfance') WHERE email='[email protected]'"; $result = $db->exec($sql); Dans la variable $result, on peut avoir : • FALSE si un problème d'exécution est apparu • le nombre de lignes affectées si le traitement s'est correctement exécuté Sécurité Il faut toujours contrôler la nature des données AVANT de les placer dans la base. Examinons le risque potentiel des requêtes à partir d'un exemple. Tout d'abord, il faut savoir que le caractère QUOTE ' joue un rôle particulier en SQL. Il est utilisé pour borner les chaines de caractères. Supposons que nous devons récupérer les données d'un formulaire d'authentification : NOM : marcel MOT DE PASSE: sesame le principe de vérification consiste à rechercher dans la table user d'une base un tuplet correspondant aux donnée saisies par l'internaute users (id, username, password ) le PHP (en version simplifiée) peut ressembler à : $sql = "SELECT * FROM users WHERE username='$username' AND userpwd='$userpwd'"; $results = $db->query($sql)->fetch(); avec les valeurs saisies, l'écriture SQL devient : SELECT * FROM users WHERE username='marcel' AND userpwd='sesame' Si l'utilisateur existe, la variable $results contient les données de l'utilisateur, sinon la variable est un boolean false Sécurité Mais que se passe t-il si l'utilisateur est malveillant et écrit : NOM : lala' OR MOT DE PASSE: 1=1 -- dans ce cas, la variable $sql contient : SELECT * FROM users WHERE username='lala' OR 1=1 -- ' AND userpwd='' Note : en SQL le symbole -- (tiret tiret) marque le début d'un commentaire. Ce traitement SQL est donc équivalent à : SELECT * FROM users WHERE username='lala' OR 1=1 Le critère WHERE contient 2 parties (2 tests) en OU logique. username='lala' est toujours false (pas d'utilisateur portant ce nom) MAIS 1=1 est toujours true donc globalement le WHERE retourne true donc la requête retourne tous les utilisateurs (sans que ni le nom, ni le mot de passe correct n'ait été entré!!!) Protection APPROCHE 1 Il faut se PROTÉGER des caractères spéciaux en utilisant la méthode quote(). Exemple : $username = $db->quote( $_POST['username'] ); $userpwd = $db->quote( $_POST['userpwd'] ); $sql = "SELECT * FROM users WHERE username='$username' AND userpwd='$userpwd'"; La requête devient : SELECT * FROM users WHERE username=''lala\' OR 1=1 -- '' AND userpwd='''' cette écriture produit une erreur d'exécution. APPROCHE 2 L'utilisation de requêtes préparées peut aussi traiter cette situation. Le pilote des RP gère automatiquement les guillemets. Exemple : $stmt = $db->prepare("SELECT * FROM users WHERE username=:username AND userpwd=:userpwd"); $stmt->bindParam(':username', $username); $stmt->bindParam(':userpwd', $userpwd); $stmt->execute(); d( $stmt->fetch() );