SQL-Schreibkonventionen

Syntax der dynamischen SQL-Tags

Die dynamische SQL-Funktion ermöglicht es uns, SQL-Anweisungen je nach Bedingungen dynamisch zu konstruieren. Mithilfe spezieller Tags können wir komplexere SQL-Logiken leicht umsetzen. Im Folgenden werden die gängigen Tags für dynamisches SQL und ihre Verwendung vorgestellt.

1. Das if-Tag

Das if-Tag dient zur bedingten Ausführung und ähnelt der if-Anweisung in Java.

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

Beispielparameter

{
  "title": "MyBlog"
}

Echte SQL-Anweisung nach der Analyse

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

2. Die Tags choose, when und otherwise

Diese Tags ähneln der switch-Anweisung in 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>

Beispielparameter

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

Echte SQL-Anweisung nach der Analyse

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

3. Die Tags trim, where und set

Diese Tags dienen dazu, Präfixe, Suffixe sowie Verknüpfungswörter in SQL-Anweisungen zu verarbeiten.

Das trim-Tag

Mit dem trim-Tag können Sie benutzerdefinierte Zeichenfolgenkürzungen festlegen.

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>

Beispielparameter

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

Echte SQL-Anweisung nach der Analyse

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

Das where-Tag

Das where-Tag fügt der generierten SQL-Anweisung dynamisch das Schlüsselwort WHERE hinzu und entfernt das erste AND oder 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>

Beispielparameter

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

Echte SQL-Anweisung nach der Analyse

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

Das set-Tag

Das set-Tag wird verwendet, um Anweisungen dynamisch zu aktualisieren; es fügt intelligent das Schlüsselwort SET hinzu und entfernt das letzte Komma.

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

Beispielparameter

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

Echte SQL-Anweisung nach der Analyse

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

4. Das foreach-Tag

Das foreach-Tag dient zum Durchlaufen von Sammlungen und wird häufig verwendet, um IN-Bedingungen zu erstellen.

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

Beispielparameter

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

Echte SQL-Anweisung nach der Analyse

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

5. Das bind-Tag

Das bind-Tag wird verwendet, um eine Variable zu erstellen und den Wert eines OGNL-Ausdrucks an diese Variable zu binden.

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

Beispielparameter

{
  "title": "MyBlog"
}

Echte SQL-Anweisung nach der Analyse

SELECT * FROM BLOG WHERE title LIKE ?

Einführung von SQL-Parametern

In dynamischen SQL-Anweisungen werden sowohl #{} als auch ${} zur Referenzierung von Parametern verwendet, doch sie unterscheiden sich hinsichtlich ihrer Behandlung erheblich.

Verwendung von #{}

1. Vorkompilierung: #{} wird für die Vorkompilierung verwendet und entspricht dem Platzhalter ? in JDBC. Der Inhalt von #{} wird durch ? ersetzt, anschließend erfolgt eine Vorverarbeitung, und die Parameter werden über PreparedStatement gesetzt.
2. Automatische Escapierung: #{} schützt effektiv vor SQL-Injection, da die übergebenen Parameter automatisch escapiert werden.
3. Typverarbeitung: #{} kann Typkonvertierungen der übergebenen Parameter berücksichtigen.

Beispiel:

SELECT * FROM BLOG WHERE title LIKE #{title}

Nach Übermittlung des Parameters title = MyBlog lautet die analysierte SQL-Anweisung:

SELECT * FROM BLOG WHERE title LIKE ?

Verwendung von ${}

1. Direkte Ersetzung: ${} ersetzt den Parameterwert direkt in die SQL-Anweisung, ohne vorherige Kompilierung. Daher ist bei der Verwendung von ${} besondere Vorsicht geboten, um SQL-Injection zu vermeiden.
2. Keine Escapierung: ${} führt keine Escapierung der übergebenen Parameter durch; daher eignet es sich besonders für dynamische Tabellen- oder Spaltennamen.
3. Keine Typkonvertierung: ${} ignoriert den Typ des Parameters und verwendet stattdessen dessen toString()-Methode.

Beispiel:

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

Nach Übermittlung des Parameters title = MyBlog lautet die analysierte SQL-Anweisung:

SELECT * FROM BLOG WHERE title LIKE 'MyBlog'

Empfehlungen zur Verwendung

• Verwendung von #{}: In den meisten Fällen, insbesondere bei Benutzereingaben, sollte #{} bevorzugt werden, um SQL-Injection zu verhindern.
• Verwendung von ${}: Verwenden Sie ${} nur dann, wenn dynamische Tabellen- oder Spaltennamen generiert werden müssen und die Sicherheit der übergebenen Parameter gewährleistet ist.

Systeminterne Variablen

Das DBAPI-System enthält bereits die interne Variable __clientId, mit der die ID des aktuellen Clients abgerufen werden kann. Diese Variable kann direkt als SQL-Parameter verwendet werden.

Beispiel:

SELECT * FROM users WHERE client_id = #{__clientId}

Escapierung von Sonderzeichen

Escapierung von kleineren Zeichen

Kleinere Zeichen wie < müssen in SQL-Anweisungen als < escapiert werden.

Fehlerhaftes Beispiel:

-- Fehler: Führt zu XML-Parsing-Fehlern
SELECT * FROM users WHERE age < 18

Richtiges Beispiel:

-- Korrekt: Mit Escapesequenz
SELECT * FROM users WHERE age < 18