Normes de rédaction SQL

Syntaxe des balises SQL dynamique

La fonctionnalité SQL dynamique nous permet de construire des instructions SQL de manière conditionnelle. Grâce à l’utilisation de balises spécifiques, il est facile d’implémenter des logiques SQL complexes. Voici les principales balises utilisées en SQL dynamique et leurs modes d’emploi.

1. Balise if

La balise if sert à effectuer une évaluation conditionnelle, similaire à l’instruction if en Java.

SELECT * FROM BLOG WHERE state = 'ACTIVE'
<if test="title != null">
  AND title like #{title}
</if>

Exemple de paramètre

{
  "title": "MyBlog"
}

SQL réel après traitement

SELECT * FROM BLOG WHERE state = 'ACTIVE' AND title like ?

2. Balises choose, when, otherwise

Ces balises sont comparables à l’instruction switch en Java.

SELECT * FROM BLOG WHERE state = 'ACTIVE'
<choose>
  <when test="title != null">
    AND title = #{title}
  </when>
  <when test="author != null and author.name != null">
    AND author_name = #{author.name}
  </when>
  <otherwise>
    AND featured = 1
  </otherwise>
</choose>

Exemple de paramètre

{
  "title": "MyBlog",
  "author": {
    "name": "AuthorName"
  }
}

SQL réel après traitement

SELECT * FROM BLOG WHERE state = 'ACTIVE' AND title = ?

3. Balises trim, where, set

Ces balises servent à gérer les préfixes, suffixes et conjonctions dans les instructions SQL.

Balise trim

La balise trim permet de personnaliser la suppression de caractères au début ou à la fin d’une chaîne.

SELECT * FROM BLOG 
<trim prefix="WHERE" prefixOverrides="and |or ">
  <if test="title != null">
    and title like #{title}
  </if>
  <if test="author != null">
    and author_name like #{author.name}
  </if>
</trim>

Exemple de paramètre

{
  "title": "MyBlog",
  "author": {
    "name": "AuthorName"
  }
}

SQL réel après traitement

SELECT * FROM BLOG WHERE title like ? AND author_name like ?

Balise where

La balise where ajoute automatiquement le mot-clé WHERE au début de l’instruction SQL générée et supprime le premier AND ou OR.

SELECT * FROM BLOG 
<where>
  <if test="title != null">
    and title like #{title}
  </if>
  <if test="author != null">
    and author_name like #{author.name}
  </if>
</where>

Exemple de paramètre

{
  "title": "MyBlog",
  "author": {
    "name": "AuthorName"
  }
}

SQL réel après traitement

SELECT * FROM BLOG WHERE title like ? AND author_name like ?

Balise set

La balise set permet de mettre à jour dynamiquement une instruction SQL en ajoutant intelligemment le mot-clé SET et en supprimant la dernière virgule.

UPDATE BLOG
<set>
  <if test="title != null">title = #{title},</if>
  <if test="author != null">author_name = #{author.name},</if>
</set>
WHERE id = #{id}

Exemple de paramètre

{
  "title": "MyBlog",
  "author": {
    "name": "AuthorName"
  },
  "id": 123
}

SQL réel après traitement

UPDATE BLOG SET title = ?, author_name = ? WHERE id = ?

4. Balise foreach

La balise foreach est principalement utilisée pour parcourir des collections, souvent pour construire des conditions IN.

SELECT * FROM BLOG
WHERE id in
<foreach item="item" index="index" collection="list" open="(" separator="," close=")">
  #{item}
</foreach>

Exemple de paramètre

{
  "list": [1, 2, 3, 4, 5]
}

SQL réel après traitement

SELECT * FROM BLOG WHERE id in (?, ?, ?, ?, ?)

5. Balise bind

La balise bind crée une variable et lie la valeur d’une expression OGNL à cette variable.

<bind name="pattern" value="'%' + title + '%'" />
SELECT * FROM BLOG
WHERE title LIKE #{pattern}

Exemple de paramètre

{
  "title": "MyBlog"
}

SQL réel après traitement

SELECT * FROM BLOG WHERE title LIKE ?

Introduction des paramètres SQL

Dans le cadre du SQL dynamique, les syntaxes #{} et ${} sont toutes deux utilisées pour référencer des paramètres, mais elles présentent des différences essentielles dans leur traitement.

Utilisation de #{}

1. Précompilation : #{} est utilisé pour la précompilation, équivalente au placeholder ? de JDBC. Il remplace #{} par ?, précompile l’instruction puis définit les paramètres via un PreparedStatement.
2. Échappement automatique : #{} protège efficacement contre les injections SQL en appliquant un échappement automatique aux valeurs passées.
3. Gestion des types : #{} prend en charge la conversion des types des paramètres fournis.

Exemple :

SELECT * FROM BLOG WHERE title LIKE #{title}

Avec le paramètre title = "MyBlog", l’instruction finale devient :

SELECT * FROM BLOG WHERE title LIKE ?

Utilisation de ${}

1. Remplacement direct : ${} remplace directement la valeur du paramètre dans l’instruction SQL sans précompilation. Par conséquent, son usage nécessite une grande prudence afin d’éviter les risques d’injection SQL.
2. Pas d’échappement : ${} ne procède pas à l’échappement des paramètres, ce qui le rend adapté aux noms de tables ou de colonnes dynamiques.
3. Pas de conversion de type : ${} utilise directement la méthode toString() du paramètre sans aucun traitement de type.

Exemple :

SELECT * FROM BLOG WHERE title LIKE '${title}'

Avec le paramètre title = "MyBlog", l’instruction finale devient :

SELECT * FROM BLOG WHERE title LIKE 'MyBlog'

Recommandations d’utilisation

• Utilisez #{} : Dans la plupart des cas, surtout lorsqu’il s’agit d’entrées utilisateur, privilégiez #{} pour éviter les injections SQL.
• Utilisez ${} : Employez ${} lorsque vous devez générer dynamiquement des noms de tables ou de colonnes, tout en veillant à la sécurité des paramètres entrants.

Variables intégrées au système

Le système DBAPI intègre déjà la variable interne __clientId, qui permet d’obtenir l’ID du client actuel et peut être utilisée directement comme paramètre SQL.

Exemple d’utilisation :

SELECT * FROM users WHERE client_id = #{__clientId}

Échappement des caractères spéciaux

Échappement du signe inférieur

Le signe inférieur < doit être échappé sous la forme < dans les instructions SQL.

Exemple incorrect :

-- Erreur : provoque une erreur d’analyse XML
SELECT * FROM users WHERE age < 18

Exemple correct :

-- Correct : utilisation du caractère échappé
SELECT * FROM users WHERE age < 18