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?