MyBatis: From SQL Injection to OGNL Injection

Dynamic SQL

Dynamic SQL is one of MyBatis's most powerful features. Constructing SQL manually is painful: spaces must be preserved and the trailing comma after the final column removed. Dynamic SQL eliminates that work.

MyBatis is generally configured in one of two ways: XML files or annotations.

1. XML Files

Four kinds of dynamic SQL tags can be used in MyBatis *Mapper.xml files:

① if

The if tag is frequently used for dynamic SQL in MyBatis, particularly for conditions in a WHERE clause:

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

This SQL makes the condition optional. If title is omitted or empty, it will not append AND title like #{title}

Or add another condition:

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG WHERE state = 'ACTIVE'
  <if test="title != null">
    AND title like #{title}
  </if>
  <if test="author != null and author.name != null">
    AND author_name like #{author.name}
  </if>
</select>

Conclusion: the test attribute of an if tag can contain and evaluate an OGNL expression.

② choose (when, otherwise)

According to the official documentation:

Sometimes we want to select only one among several conditions. MyBatis provides the choose element for this situation; it resembles a Java switch statement.

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

Conclusion: the test attribute of a when tag can contain and evaluate an OGNL expression.

③ trim (where, set)

<select id="findActiveBlogLike"  resultType="Blog">
  SELECT * FROM BLOG WHERE
  <if test="state != null">
    state = #{state}
  </if>
  <if test="title != null">
    AND title like #{title}
  </if>
  <if test="author != null and author.name != null">
    AND author_name like #{author.name}
  </if>
</select>

Consider this SQL statement. If no condition matches, the final query becomes:

SELECT * FROM BLOG
WHERE

This will clearly make the query fail.

Likewise, if only the second condition matches, the SQL becomes:

SELECT * FROM BLOG
WHERE
AND title like 'someTitle'

This query also fails.

MyBatis therefore provides the trim element:

<select id="findActiveBlogLike" resultType="Blog">
  SELECT * FROM BLOG
  <where>
    <if test="state != null">
         state = #{state}
    </if>
    <if test="title != null">
        AND title like #{title}
    </if>
    <if test="author != null and author.name != null">
        AND author_name like #{author.name}
    </if>
  </where>
</select>

A where tag has been added; the set tag works on the same principle.

Conclusion: in this situation, there is generally no place to inject an OGNL expression.

④ foreach

Another common use of dynamic SQL is iterating over a collection, especially when building an IN clause. For example:

<select id="selectPostIn" resultType="domain.blog.Post">
  SELECT *
  FROM POST P
  <where>
    <foreach item="item" index="index" collection="list"
        open="ID in (" separator="," close=")" nullable="true">
          #{item}
    </foreach>
  </where>
</select>

Conclusion: in this situation, there is generally no place to inject an OGNL expression.

⑤ bind

The bind tag lets us create a variable outside an OGNL expression and bind it to the current context. For example:

<select id="selectBlogsLike" resultType="Blog">
  <bind name="pattern" value="'%' + _parameter.getTitle() + '%'" />
  SELECT * FROM BLOG
  WHERE title LIKE #{pattern}
</select>

Conclusion: the value attribute of a bind tag can contain and evaluate an OGNL expression.

2. Annotations

Spring Boot removes much of the burden of XML configuration. MyBatis likewise provides Spring Boot annotations for dynamic SQL, chiefly:

• @Insert
• @Update
• @Delete
• @Select
• @InsertProvider
• @SelectProvider
• @UpdateProvider
• @DeleteProvider

The @Insert, @Update, @Delete, and @Select annotations correspond to database create, update, delete, and query operations. Each has a matching Provider annotation.

With a Provider annotation, you implement the query class yourself, which also makes dynamic SQL much easier to use.

For example, an @Update annotation must use the following to implement dynamic SQL: