1

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 ?

  • You'll need to escape all `%` in your parameter before concatenating the ones you want on to the beginning and end. For example: http://stackoverflow.com/questions/8247970/using-like-wildcard-in-prepared-statement – Jeremy Mar 04 '17 at 19:27
  • Ok, but tell me if my approach with `bind` is SQLInjection safe ? –  Mar 04 '17 at 19:29
  • And could you show me how to escape it in your way? This example doesn't help me –  Mar 04 '17 at 19:37
  • I have lately learned another way to do it:`LIKE '%'+#{param}+'%'`, that does not need escaping `%` in the param. About injection: as long as the param value is not concatenated with the query (and then parsed and executed), but bound as parameter, it should be ok. – blackwizard Mar 05 '17 at 00:22
  • @blackwizard you way works for me. Simply, database engine before using LIKE filter evalues expression. My solution is not clear for me: ``. From what it does know that `param` in `value="'%' + param + '%'"` refers to external variable ? –  Mar 06 '17 at 00:11

1 Answers1

2

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.

Rosmi Domini
  • 31
  • 3