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

Have you tried adding the to the beginningOfName value before you make the myBatis call? — DwB, Sep 20 '11 at 20:22
@DwB - No, that might be an idea. I sort of assumed that it would be escaped, but now that I think about it, it might not, as it's not treated specially other than by the `LIKE`. Will test. — Hanno Fietz, Sep 20 '11 at 20:25
I did some iBatis (older xml format) work and for my Likes I added the % before the iBatis call. I had to use a special format char to get iBatis to accept the strings. I think it was #value# (for append value without processing). — DwB, Sep 20 '11 at 20:39
This will work `SELECT * FROM Person WHERE name LIKE '%' + #{beginningOfName} + '%'` — Santosh Jadi, Oct 15 '20 at 14:15

score 16 · Answer 1 · answered May 19 '15 at 07:28

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>

score 13 · Accepted Answer · edited May 23 '17 at 12:10

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.)

This works `myMapper.getAllUrlsLikeId('%'+query+'%');` – Chloe Nov 25 '18 at 00:50 — Chloe, Nov 25 '18 at 00:50

score 8 · Answer 3 · answered Jul 30 '18 at 08:48

8

if you're using mybatis, you can write this for s

SELECT(" * ");
FROM(" student ");
WHERE(" ten LIKE '%' #{ten} '%' ");

answered Jul 30 '18 at 08:48

Trang NT

99
1
2

1

Amazing that even works! `select * from allurls where id like '%' '5' '%';` – Chloe Nov 25 '18 at 00:46

score 6 · Answer 4 · answered May 19 '17 at 16:31

the bind include inside the if

<select id="select" parameterType="java.util.Map" resultType="ViajeDTO">

SELECT A.ID_VIAJE ID, A.NOMBRE, A.DESCRIPCION, A.FINICIO, A.FFIN, A.LOGO, A.URL, 
        A.ID_CLIENTE IDCLIENTE, B.NOMBRE CLIENTE
FROM VIAJE A
INNER JOIN CLIENTE B ON (A.ID_CLIENTE = B.ID_CLIENTE)
WHERE A.ESTATUS = 1 

<if test="P_NOMBRE != null">
    <bind name="pattern" value="'%' + P_NOMBRE + '%'" />
      AND A.NOMBRE LIKE #{pattern}
</if>
</select>

score 2 · Answer 5 · answered Feb 20 '17 at 23:00

We can make use of Bind. The bind element lets you create a variable out of an expression and bind it to the context. For example:

<select id="select" parameterType="java.util.Map" resultType="ViajeDTO">

    <bind name="pattern" value="'%' + P_NOMBRE + '%'" />

    SELECT A.ID_VIAJE ID, A.NOMBRE, A.DESCRIPCION, A.FINICIO, A.FFIN, A.LOGO, A.URL, 
            A.ID_CLIENTE IDCLIENTE, B.NOMBRE CLIENTE
    FROM VIAJE A
    INNER JOIN CLIENTE B ON (A.ID_CLIENTE = B.ID_CLIENTE)
    WHERE A.ESTATUS = 1 

    <if test="P_NOMBRE != null">
        AND A.NOMBRE LIKE #{pattern}
    </if>
</select>

Taking into account that the parameter to look for is P_NAME.

score 1 · Answer 6 · answered Oct 15 '20 at 14:14

1

This will work:

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

answered Oct 15 '20 at 14:14

Santosh Jadi

1,479
6
29
55

score 1 · Answer 7 · answered Sep 01 '21 at 11:41

1

SELECT("*");
FROM(Dao.TableName);
WHERE(Dao.TableFields.URL + " LIKE LOWER('%' || #{url} || '%') ");

answered Sep 01 '21 at 11:41

Eliasz Kubala

3,836
1
23
28

score 0 · Answer 8 · answered Aug 05 '20 at 15:19

0

In mybatis annotation @Select

for SQL server "... LIKE '%' + #{param} + '%' ..."

for ORACLE "... LIKE '%' || #{param} || '%' ..."

ref : https://mybatis.org/mybatis-3/java-api.html

answered Aug 05 '20 at 15:19

top spin

11

score 0 · Answer 9 · answered Oct 11 '20 at 16:35

The bind feature is available with mybatis version 3.2 or greater. For versions below that:- The following worked for me, in Mysql. I have used the concat function.

<select id="getUserNamesAndEmails" parameterType="com.ashish.cardservices.models.UserCreds" resultMap="userCreds">
        select name,email from users where upper(name) like concat("%",concat(upper(#{searchQuery}),"%")) or upper(email) like concat("%",concat(upper(#{searchQuery}),"%")) and upper(email) != upper(#{email}) ;
</select>

score 0 · Answer 10 · edited Oct 29 '22 at 15:30

0

None of the other answers worked for me, I solved the problem with this:

<select id = "id" resultType = "resultType" >
SELECT * FROM Person WHERE name LIKE '%${PARAM}%'
</select>

edited Oct 29 '22 at 15:30

James Risner

5,451
11
25
47

answered Oct 25 '22 at 12:36

stupiddunkey

5
3

score -1 · Answer 11 · answered Jan 24 '12 at 16:36

-1

|| operator worked for me in IBatis but not in Mybatis.

In MyBatis i had to use + operator.

SELECT *
FROM Employee
WHERE
name like '%' + #{searchName,jdbcType=NVARCHAR} + '%'

answered Jan 24 '12 at 16:36

changed

2,103
8
36
56

3

Are you sure that this is a behaviour of iBatis/Mybatis? Because, the difference is actually at database level, and I doubt that iBatis had some magic that tried to rewrite your SQL. – Hanno Fietz Jan 25 '12 at 20:57

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

11 Answers11

Linked