TD3 – Requêtes préparées et association de classes SQL JOIN

1. Copiez/collez dans un nouveau dossier TD3 les fichiers ConfigurationBaseDeDonnees.php, ConnexionBaseDeDonnees.php, Utilisateur.php et lireUtilisateurs.php.

2. Copiez la fonction précédente recupererUtilisateurParLogin dans la classe Utilisateur en la déclarant publique et statique.

3. Testez la fonction recupererUtilisateurParLogin dans un nouveau fichier testRequetePrepare.php.

   Remarque : Vous aurez sans doute une erreur Class "ConnexionBaseDeDonnees" not found. Où inclure ConnexionBaseDeDonnees.php : dans Utilisateur.php ou dans creerUtilisateur.php ?
   Règle simple : chaque fichier doit inclure les classes dont il a besoin. Comme Utilisateur.php a besoin de la classe ConnexionBaseDeDonnees (à cause de l’instruction ConnexionBaseDeDonnees::getPdo()), c’est au début de Utilisateur.php qu’il faut faire require_once "ConnexionBaseDeDonnees.php";.

4. On souhaite que recupererUtilisateurParLogin renvoie null s’il n’existe pas d’utilisateur de login $login. Mettez à jour le code et la déclaration de type. Testez votre code.

1. Créez une fonction public function ajouter() : void dans la classe Utilisateur qui insère l’utilisateur courant ($this) dans la BDD. On vous rappelle la syntaxe SQL d’une insertion :

   INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...)
   

   Attention : La requête INSERT INTO ne renvoie pas de résultats ; il ne faut donc pas faire de fetch() sous peine d’avoir une erreur SQLSTATE[HY000]: General error.

2. Testez cette fonction dans testRequetePrepare.php en créant un objet de classe Utilisateur et en l’enregistrant.

Remarque : Le nom de la fonction ajouter() peut prêter à confusion. En effet, lorsqu’on voit une fonction ajouter() dans une classe Utilisateur, on ne s’attend pas forcément à ce qu’il y ait un enregistrement en BD. Pour le moment nous allons garder le nom de cette méthode tel quel, car dans quelques semaines le code de celle-ci sera migré ailleurs (dans une classe de persistance) et le nom ajouter() prendra tout son sens.

Branchons maintenant notre enregistrement d’utilisateur dans la BDD au formulaire de création d’utilisateur du TD1 :

1. Copiez dans le dossier TD3 les fichiers creerUtilisateur.php et formulaireCreationUtilisateur.html du TD1.

2. Modifiez la page creerUtilisateur.php de sorte qu’elle sauvegarde l’objet Utilisateur reçu (en GET ou POST, au choix).

3. Testez l’insertion grâce au formulaire formulaireCreationUtilisateur.html.

4. Vérifiez dans PhpMyAdmin que les utilisateurs sont bien sauvegardés.

5. Essayez d’ajouter un utilisateur dont un champ contient un guillemet simple ', par exemple un nom "D'Artagnan". Est-ce qu’elle a bien été sauvegardée ? Si ce n’est pas le cas, c’est sûrement que vous n’avez pas utilisé les requêtes préparées.

La table utilisateur avec quelques utilisateurs a déjà été créée dans votre PhpMyAdmin. Créez la table trajet comme suit :

1. Créez une table trajet avec les champs suivants :
   • id : INT, clé primaire, qui s’auto-incrémente (voir en dessous)
   • depart : VARCHAR 64
   • arrivee : VARCHAR 64
   • date : DATE
   • prix : INT
   • conducteurLogin : VARCHAR 64
   • nonFumeur : BOOLEAN

   Note : On souhaite que le champ primaire id s’incrémente à chaque nouvelle insertion dans la table. Pour ce faire, cochez la case A_I (auto-increment) pour le champ id.

   Note : Observez qu’à l’enregistrement de votre table dans PhpMyAdmin le type BOOLEAN est remplacé par tinyint, où 0 correspond à "faux" et 1` correspond à “vrai”.

   Important : Avez-vous bien pensé à InnoDB et utf8_general_ci comme précédemment ?

2. Insérez quelques trajets en prenant soin de ne pas remplir la case id (pour que l’auto-incrément marche) et en mettant dans conducteurLogin un login d’utilisateur valide (pour éviter des problèmes par la suite).

1. Enregistrez la classe suivante : Trajet.php.

2. En vous inspirant de lireUtilisateurs.php, créez lireTrajets.php qui liste les trajets. Vous allez devoir modifier la fonction Trajet::construireDepuisTableauSQL car

   • la date renvoyée par MySQL est un string, alors que Trajet attend un DateTime. Pour transformer une date string en DateTime, utilisez le constructeur new DateTime($dateString).
   • MySQL ne renvoie que le login du conducteur tandis que Trajet attend un Utilisateur. Utilisez la méthode Utilisateur::recupererUtilisateurParLogin.
   • MySQL renvoie le booléen nonFumeur comme un entier 0 ou 1. Par chance, PHP converti automatiquement les entiers en booléen donc il n’y a rien à faire.

Voici les étapes pour faire ce lien :

1. À l’aide de l’interface de PhpMyAdmin, faites de trajet.conducteurLogin un index.

   Aide : Dans l’onglet Structure de la table trajet, cliquez sur l’icône de l’action index en face du champ conducteurLogin.

   Plus de détails : Dire que le champ conducteurLogin est un index revient à dire à MySql que l’on veut trouver rapidement les lignes qui ont un conducteurLogin donné. Du coup, MySql va construire une structure de donnée pour permettre cette recherche rapide. Une clé étrangère est nécessairement un index, car on a besoin de ce genre de recherches pour tester rapidement la contrainte de clé étrangère.

2. Ajoutez la contrainte de clé étrangère entre trajet.conducteurLogin et utilisateur.login. Pour ceci, allez dans l’onglet Structure de la table trajet et cliquez sur Vue relationnelle pour accéder à la gestion des clés étrangères.

   Nous allons utiliser le comportement ON DELETE CASCADE pour qu’une association soit supprimée si la clé étrangère est supprimée, et le comportement ON UPDATE CASCADE pour qu’une association soit mise à jour si la clé étrangère est mise à jour.

   Attention : Pour supporter les clés étrangères, il faut que le moteur de stockage de toutes vos tables impliqués soit InnoDB. Vous pouvez choisir ce paramètre à la création de la table ou le changer après coup dans l’onglet Opérations.

1. Nous vous fournissons le formulaire de création de trajets. Enregistrez formulaireCreationTrajet.html.
   Notez que la date est un <input type="date">, et que le booléen nonFumeur est un <input type="checkbox">.

2. En vous inspirant de creerUtilisateur.php, créez creerTrajet.php qui traite les données du formulaire précédent. Les 2 étapes clés sont la création d’un objet Trajet et l’appel à la méthode ajouter() de Trajet. Voici comment faire :

   1. À la création de l’objet Trajet
      • mettez id à null pour indiquer que vous ne connaissez pas son identifiant ;
      • le formulaire renvoie une date sous forme de string alors que la date du trajet doit être un objet DateTime. Appliquez la transformation de l’exercice sur la lecture des tables.
      • le formulaire ne renvoie que le login du conducteur tandis que Trajet attend un Utilisateur. Appliquez la transformation de l’exercice sur la lecture des tables.
      • le formulaire indique le booléen nonFumeur en remplissant, ou pas, la case $_GET["nonFumeur"]. Utilisez donc isset($_GET["nonFumeur"]) pour lire le booléen envoyé par le formulaire.
   2. Créez la méthode dynamique ajouter() de Trajet.
      La requête INSERT doit remplir tous les champs sauf id, qui sera rempli automatiquement par MySQL.
      Pour le tableau de valeurs donné à la requête préparée
      • MySQL attend une date string. Si $date est un DateTime, alors $date->format("Y-m-d") permet de la convertir en string ;
      • MySQL attend seulement le login du conducteur ;
      • MySQL stocke le booléen nonFumeur comme un entier. Il faut ainsi donner à MySQL 1 si le trajet est non-fumeur, ou 0 sinon.
3. Testez l’enchaînement du formulaire de création et de creerTrajet.php. Vérifiez dans votre base de données que le trajet est bien créé.

1. Créer la table passager en utilisant l’interface de PhpMyAdmin.

   Important : Avez-vous bien pensé à InnoDB et utf8_general_ci comme précédemment ?

2. Assurez-vous que vous avez bien le bon couple en tant que clé primaire. Cela se voit dans la section Index de l’onglet Structure.

3. Ajoutez la contrainte de clé étrangère entre passager.trajetId et trajet.id, puis entre passager.passagerLogin et utilisateur.login. Utiliser encore les comportements ON DELETE CASCADE et ON UPDATE CASCADE pour qu’une association soit mise à jour si la clé étrangère est mise à jour.

4. À l’aide de l’interface de PhpMyAdmin, insérer quelques associations pour que la table passager ne soit pas vide.

5. Vous allez maintenant vous assurer de la bonne gestion des clés étrangères en testant le comportement ON DELETE CASCADE. Pour cela :

   1. créez un trajet correspondant à un certain conducteur,
   2. puis inscrivez des passagers pour ce trajet
   3. supprimez ensuite le conducteur en question de la table utilisateur et vérifiez que les lignes de la table passager précédemment insérées ont bien été supprimées elles aussi.

1. Codez une fonction recupererPassagers() dans Trajet.php qui retournera un tableau d’Utilisateur correspondant aux passagers du trajet courant en faisant la requête adéquate. Voici la signature de la fonction.

    /**
     * @return Utilisateur[]
     */
    private function recupererPassagers() : array {
      // À coder
    }
   

   Indices :

   • Utiliser une requête à base d’INNER JOIN. Une bonne stratégie pour développer la bonne requête est d’essayer des requêtes dans l’onglet SQL de PhpMyAdmin jusqu’à tenir la bonne.
   • Inspirez-vous de Utilisateur::recupererUtilisateurs pour la création d’objets Utilisateurs depuis une réponse SQL.
   • Avez-vous bien utilisé une requête préparée dans recupererPassagers ?
2. Nous allons stocker la liste des passagers comme un attribut de la classe Trajet.
   1. Ajoutez l’attribut

         /**
         * @var Utilisateur[]
         */
         private array $passagers;
      

   2. Mettez à jour le constructeur pour qu’il gère cet attribut avec la valeur par défaut [].
   3. Générez à l’aide de PHPStorm les accesseurs getPassagers et setPassagers.
   4. Modifiez la fonction construireDepuisTableauSQL pour qu’elle instancie le nouveau $trajet avec une liste des passagers vide, qu’elle récupère les passagers de ce trajet, et qu’elle stocke ces passagers dans l’attribut.
3. Testez votre code en modifiant lireTrajets.php pour qu’il affiche pour chaque trajet sa liste des passagers.

Mettons en place notre attaque SQL :

1. Pour ne pas supprimer une table importante, créons une table utilisateur2 qui ne craint rien :
   • allez dans PHPMyAdmin et cliquez sur votre base de donnée (celle dont le nom est votre login à l’IUT)
   • Dans l’onglet SQL Importer, donnez le fichier utilisateur2.sql qui créera une table utilisateur2 avec quelques utilisateurs.

2. Nous vous fournissons le fichier PHP que nous allons attaquer : formulaireLectureUtilisateur.php
   Ce fichier contient un formulaire qui affiche les informations d’un utilisateur étant donné son login.
   Testez ce fichier en donnant un login existant.
   Lisez le code pour être sûr de bien comprendre le fonctionnement de cette page (et demandez au professeur si vous ne comprenez pas tout !).

3. Trouvez ce qu’il faut taper dans le formulaire pour que recupererUtilisateurParLogin vide la table utilisateur2 (SQL Truncate).

   Aide : Le point clé de ce fichier est que la fonction recupererUtilisateurParLogin a été codée sans requête préparée et est vulnérable aux injections SQL.

   function recupererUtilisateurParLogin(string $login) : ?Utilisateur {
      $sql = "SELECT * from utilisateur2 WHERE login='$login'";
      echo "<p>J'effectue la requête <pre>$sql</pre></p>";
      $pdoStatement = ConnexionBaseDeDonnees::getPDO()->query($sql);
      $utilisateurTableau = $pdoStatement->fetch();
   
      if ($utilisateurTableau !== false) {
            return Utilisateur::construireDepuisTableauSQL($utilisateurTableau);
      }
      return null;
   }
   

1. Créez une fonction recupererTrajetsCommePassager() dans Utilisateur.php qui retournera les trajets auxquels l’utilisateur courant est inscrit en tant que passager. Voici la signature de la fonction :

   /**
    * @return Trajet[]
    */
   private function recupererTrajetsCommePassager() : array
   

2. Nous allons stocker la liste des trajets comme un attribut de la classe Utilisateur.
   1. Ajoutez l’attribut

         /**
         * @var Trajet[]|null
         */
         private ?array $trajetsCommePassager;
      

   2. Mettez à jour le constructeur pour qu’il initialise cet attribut à null.
   3. Générez à l’aide de PHPStorm l’accesseur getTrajetsCommePassager et le modifieur setTrajetsCommePassager.
   4. Modifiez le code de getTrajetsCommePassager pour que, si $trajetsCommePassager est null, alors on l’initialise à l’aide de recupererTrajetsCommePassager.
3. Testez votre code en modifiant lireUtilisateurs.php pour qu’il affiche pour chaque utilisateur la liste de ses trajets en tant que passager.

1. Créer une méthode public function supprimerPassager(string $passagerLogin): bool dans Trajet.php. Cette fonction devra désinscrire l’utilisateur passagerLogin du trajet courant. Laissons cette fonction retourner true pour le moment.

2. Créez une page supprimerPassager.php qui reçoit un login d’utilisateur et un trajet_id via le query string de l’URL, et qui désinscrit cet utilisateur comme passager de ce trajet.

   Remarque : Vous aurez besoin de créer un $trajet pour pouvoir appeler la méthode $trajet->supprimerPassager(). Deux possibilités :

   • Soit vous rajoutez une méthode Trajet::recupererTrajetParId similaire à Utilisateur::recupererUtilisateurParLogin.
   • Soit vous créez un $trajet avec des fausses données, sauf l’id qui est correct. Une manière propre de procéder est de mettre les autres attributs à null, ce qui implique de changer les types pour autoriser null.

3. Ajoutez à lireTrajets.php de liens <a> de désinscription pour chaque passager de chaque trajet qui renvoient sur supprimerPassager.php en transmettant via le query string de l’URL les bons login et trajet_id.

4. Testez la désinscription.

1. Pour la méthode ajouter(), les cas particuliers génèrent une exception de la classe PDOException. Modifiez la déclaration de type de la méthode pour qu’elle retourne un booléen pour indiquer si la sauvegarde s’est bien passée. Modifiez la méthode pour intercepter les PDOException avec un try/catch et retourner false en cas de problème.

2. Pour la méthode supprimerPassager(), utilisez la méthode rowCount() de la classe PDOStatement pour vérifier que la requête de suppression a bien supprimé une ligne de la BDD. Modifiez la déclaration de type de la méthode pour qu’elle retourne un booléen pour indiquer si la suppression s’est bien passée.

3. Lors de l’insertion dans la base de données avec ajouter() de Trajet, il est possible de récupérer l’identifiant auto-incrémenté généré par la base de données et le renseigner dans l’objet courant. Pour ceci, utilisez ConnexionBaseDeDonnees::getPdo()->lastInsertId() après l’exécution de $pdoStatement->execute(...).