Padrões de Escrita SQL

Sintaxe das Tags de SQL Dinâmico

A funcionalidade de SQL dinâmico permite construir instruções SQL de forma dinâmica, com base em condições. Utilizando tags específicas, é possível implementar facilmente lógicas SQL complexas. A seguir estão as principais tags do SQL dinâmico e suas respectivas utilizações.

1. Tag if

A tag if é utilizada para realizar verificações condicionais, similar à instrução if em Java.

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

Exemplo de parâmetro

{
  "title": "MyBlog"
}

SQL real após a análise

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

2. Tags choose, when e otherwise

Essas tags funcionam de maneira semelhante ao comando switch em 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>

Exemplo de parâmetro

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

SQL real após a análise

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

3. Tags trim, where e set

Essas tags são usadas para manipular prefixos, sufixos e conectivos em instruções SQL.

Tag trim

A tag trim permite personalizar a remoção ou adição de partes de uma string.

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>

Exemplo de parâmetro

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

SQL real após a análise

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

Tag where

A tag where adiciona automaticamente a palavra-chave WHERE no início da consulta e remove o primeiro 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>

Exemplo de parâmetro

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

SQL real após a análise

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

Tag set

A tag set é utilizada para atualizar dinamicamente instruções SQL, adicionando automaticamente a palavra-chave SET e removendo a vírgula final.

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

Exemplo de parâmetro

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

SQL real após a análise

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

4. Tag foreach

A tag foreach é utilizada principalmente para iterar sobre coleções, sendo frequentemente empregada na construção de condições IN.

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

Exemplo de parâmetro

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

SQL real após a análise

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

5. Tag bind

A tag bind cria uma variável e associa o valor de uma expressão OGNL a essa variável.

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

Exemplo de parâmetro

{
  "title": "MyBlog"
}

SQL real após a análise

SELECT * FROM BLOG WHERE title LIKE ?

Introdução de Parâmetros SQL

No SQL dinâmico, tanto #{} quanto ${} são utilizados para referenciar parâmetros, mas apresentam diferenças importantes no processamento dos mesmos.

Uso de #{}

1. Processamento Pré-compilado: #{} é utilizado para pré-compilação, equivalente ao placeholder ? do JDBC. Substitui #{} por ?, realiza a pré-compilação e define os parâmetros via PreparedStatement.
2. Escapamento Automático: #{} previne eficazmente ataques de injeção SQL, pois realiza o escapamento automático dos valores passados.
3. Tratamento de Tipos: #{} realiza a conversão de tipos dos parâmetros recebidos.

Exemplo:

SELECT * FROM BLOG WHERE title LIKE #{title}

Após a inserção do parâmetro title = MyBlog, o SQL analisado fica assim:

SELECT * FROM BLOG WHERE title LIKE ?

Uso de ${}

1. Substituição Direta: ${} substitui diretamente o valor do parâmetro na instrução SQL, sem pré-compilação. Portanto, deve-se ter cuidado ao usar ${} para evitar riscos de injeção SQL.
2. Sem Escapamento: ${} não realiza o escapamento dos parâmetros, sendo adequado para nomes de tabelas ou colunas dinâmicos.
3. Sem Conversão de Tipos: ${} ignora o tratamento de tipos e utiliza diretamente o método toString() do parâmetro.

Exemplo:

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

Após a inserção do parâmetro title = MyBlog, o SQL analisado fica assim:

SELECT * FROM BLOG WHERE title LIKE 'MyBlog'

Recomendações de Uso

• Utilize #{}: Em geral, especialmente em cenários envolvendo entrada do usuário, dê preferência a #{} para prevenir ataques de injeção SQL.
• Utilize ${}: Use ${} quando for necessário gerar dinamicamente nomes de tabelas ou colunas, desde que os parâmetros sejam seguros.

Variáveis Internas do Sistema

O sistema DBAPI já possui internamente a variável __clientId, utilizada para obter o ID do cliente atual, podendo ser usada diretamente como parâmetro SQL.

Exemplo de uso:

SELECT * FROM users WHERE client_id = #{__clientId}

Escapamento de Caracteres Especiais

Escapamento do Sinal de Menor Que

O sinal de menor que < em instruções SQL deve ser escapado como <.

Exemplo incorreto:

-- Erro: causará falha na análise XML
SELECT * FROM users WHERE age < 18

Exemplo correto:

-- Correto: utilizando o caractere escapado
SELECT * FROM users WHERE age < 18