(How) can I use "LIKE" in SQL queries with MyBatis safely and DB-agnostic?

Question

In MyBatis, you mark the places where parameters should be inserted into your SQL like so:

SELECT * FROM Person WHERE id = #{id}

This syntax activates proper escaping etc to avoid, among other things, SQL injection attacks. If you have trusted input and want to skip escaping, you can insert the parameters verbatim:

SELECT * FROM {tableName} WHERE id = #{id}

Now, I want to do a LIKE search on unsafe input, so what I want to do is this:

SELECT * FROM Person WHERE name LIKE #{beginningOfName} || '%'

Unfortunately, however, important DB servers don't support the || syntax for concatenation:

MSSQL - Breaks the standard by using the '+' operator instead of '||'.

...

MySQL - Badly breaks the standard by redefining || to mean OR.

So, I could do either

SELECT * FROM Person WHERE name LIKE CONCAT(#{beginningOfName}, '%')

and be confined to, in this case, MySQL, or I could do

SELECT * FROM Person WHERE name LIKE '{beginningOfName}%'

and would have to sanitize input myself.

Is there a more elegant solution?

Answer 1

You could use bind syntax

Quoting Official documentation

The bind element lets you create a variable out of an OGNL expression and bind it to the context. For example:

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