(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>

Answer 2

Typically this is done by adding the % to the parameter itself before passing it in, in whatever language you're using outside of SQL. However note that either way you might still need to do an escaping step if your search term may have _ or % in it. See eg this question for background.)

To fix the concatenation problem in general, put MySQL into ANSI sql_mode and you get proper support for the || operator, as well as correct handling of double quotes for schema names rather than string literals.

(If you can't do that you'd have to build a function to build the statement out of either || or CONCAT(), abstracting away the difference.)