CODEWITHSUNDEEP
javasqlsql-servermybatis
user7311580
user7311580

Reputation:

Mybatis - preventing agains SQL Injection in LIKE query

I am using sql server. Lets look following query in mybatis:
param is type of string, in database SomeColumn is type of VARCHAR

...
 = SomeColumn LIKE '%#{param, jdbcType=VARCHAR}%'

It returns error:

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='param', mode=IN, javaType=class java.lang.Object, jdbcType=VARCHAR, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #1 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #1 with JdbcType VARCHAR . Try setting a different JdbcType for this parameter or a different configuration property. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: The index 1 is out of range.

I can't deal with it (any ideas?), but I find something: 

...    
<bind name="param" value="'%' + param + '%'" />
...
     = SomeColumn LIKE #{param}

And it does work. I think that prevent me agains SQL Injection but I am not sure. Can you answer to it ? Is it SQL Injection safe ?

Upvotes: 1

Views: 3442

Answers (1)

Rosmi Domini
Rosmi Domini

Reputation: 41

We faced the same issue and the below method worked for us:

e.g select * from TABLE1 where COLUMN1 like '%' || #{PARAM_NAME} || '%';

This prevents SQL Injection and allows to use # in LIKE query.

Upvotes: 3

Related Questions