#####
Préparation des requêtes SQL
#####

Introduction
*****

Dans le travail pratique que nous avons effectué, la requête ``SQL`` suivante nécessitait la valeur de ``id_discussion`` pour pouvoir être exécutée :

.. captionup:: ./serveur/sql.js
.. code-block:: javascript

  var requete_temp = '\
    SELECT\
      COUNT(messages.id_message) AS "nombre_messages",\
      discussions.sujet,\
      discussions.id_discussion,\
      messages.nom_utilisateur AS "utilisateur_createur",\
      messages.date_ecriture\
    FROM discussions, messages\
    WHERE discussions.id_discussion = messages.id_discussion\
    AND messages.id_discussion = ' 
    /* ici, nous concaténons l'identifiant de la discussion à la
    requête SQL pour que celle-ci puisse être exécutée correctement */
    + results[i].id_discussion +
    'ORDER BY messages.date_ecriture\
    LIMIT 0,1;';

En concaténant la variable ``results[i].id_discussion`` à la chaîne de caractères ``requete_temp``, nous avons obtenu une requête ``SQL`` "personnalisée" dans laquelle ``id_discussion`` changeait à chaque nouveau passage dans la boucle. Dans la suite de notre application, outre pour la sélection de données, nous aurons besoin de requêtes "personnalisées" pour l'insertion, la mise à jour et la suppression de données. Pour ce faire, nous pourrions utiliser la même méthode que ci-dessus ; par exemple, une requête pour insérer un nouveau message ressemblerait à ceci :

.. code-block:: javascript

  /* nous supposons que les variables nom_utilisateur, contenu, 
  date_ecriture, id_discussion et likes existent */
  var requete_sql = '\
    INSERT INTO messages(nom_utilisateur, contenu, date_ecriture, id_discussion, likes)\
    VALUES("' + nom_utilisateur + '","' + contenu + '", NOW(),' + id_discussion + ', 0)';

Mais il en découlerait un problème important : ce code serait **vulnérable aux injections** ``SQL``.

.. only:: html

  .. seealso:: Les injections ``SQL`` ? Qu'est-ce que c'est ?

.. only:: latex

  .. hint:: Les injections ``SQL`` ? Qu'est-ce que c'est ?

Il s'agit de requêtes ``SQL`` que peut effectuer un utilisateur en injectant une requête dans le contenu d'un champ qu'il peut remplir à l'aide de l'application ; par exemple, un utilisateur pourrait faire une injection ``SQL`` en écrivant le message suivant :

.. code-block:: guess

  Mouhaha, je peux faire planter cette application ! 
  En voici la preuve... ", NOW(), 1, 0; DROP TABLE messages;

En publiant ce message, un utilisateur exécuterait la première requête ``SQL`` (qui ajoute son message dans la base de données), mais exécuterait ensuite une autre requête ``SQL``, ``DROP TABLE messages``, qui supprimerait la table ``messages``.

Il va sans dire que cette technique permet également d'exécuter d'autres instructions ``SQL``, allant de la récupération des données des utilisateurs à la suppression de la base de données.

Pour éviter des abus de ce type, il convient donc de **préparer** les requêtes ``SQL`` lorsqu'elles contiennent des données venant des utilisateurs. La préparation de requêtes permet d'éviter les désagréments des injections ``SQL`` en échappant tous les caractères qui pourraient corrompre la requête ``SQL``. Dans une requête préparée, le message qui faisait planter le serveur auparavant est donc formaté de la manière suivante :

.. code-block:: guess

  Mouhaha, je peux faire planter cette application ! 
  En voici la preuve... \", NOW(), 1, 0; DROP TABLE messages;

Dans le cas présent, la préparation ajoute à la requête un antislash avant le guillemet, empêchant ainsi la fermeture de la chaîne de caractères contenant le message et, par conséquent, l'exécution des instructions qui suivent ce guillemet.

Code
*****

Maintenant que nous avons vu les danger des injections ``SQL``, regardons comment nous en prémunir.

``node-mysql``, le module ``NPM`` que nous utilisons pour dialoguer avec la base de données, fournit une fonction, ``mysql.format``, qui permet de préparer les requêtes ``SQL``. Celle-ci accepte deux paramètres ; le premier est la requête ``SQL``, et le deuxième est un array qui contient les valeurs à ajouter à la requête.

Toutefois, cet outil comporte quelques bugs, notamment au niveau des accents graves (`) et des points (.). Pour résoudre ces problèmes, nous allons donc créer une nouvelle fonction dans ``sql.js``

.. captionup:: ./serveur/sql.js
.. code-block:: javascript

  exports.preparer = function(mysql, requete_sql, inserts) {
    requete_sql = mysql.format(requete_sql, inserts)
      // nous utilisons la méthode .remplace avec une expression régulière
      // pour supprimer les accents graves et les points
      .replace(/`/g, "'")
      .replace(/'\.'/g, ".")
      .replace(/'/g, "\\'");
    return requete_sql;
  }

Utilisation
*****

Voyons comment utiliser ``sql.preparer()`` pour formater correctement nos requêtes. Dans cet exemple, nous allons reprendre notre requête ``SQL`` pour insérer un nouveau message mais, cette fois, en préparant la requête ``SQL`` :

.. code-block:: javascript

  var requete_sql = '\
    INSERT INTO messages(nom_utilisateur, contenu, date_ecriture, id_discussion, likes)'+
    // ici, les valeurs prennent "??" lorsque nous insérons une chaîne de caractères
    // et "?" lorsqu'il s'agit d'un nombre
    'VALUES(??, ??, NOW(), ?, 0)';
  // l'array "inserts" contient, dans l'ordre d'apparition dans la requête, les éléments
  // qui doivent remplacer les points d'interrogation
  var inserts = [
    nom_utilisateur,
    contenu,
    id_discussion
  ];
  // enfin, nous préparons la requête SQL en exécutant la fonction sql.preparer()
  requete_sql = sql.preparer(mysql, requete_sql, inserts);

Et voilà ! Nous sommes désormais parés contre toute injection ``SQL``.