iBatis SQL with dynamic like operator

In iBatis, a parameter is escaped, autoquoted and replaced automatically. So a #stringParam# will be auto autoquoted and then replaced. Its also escaped, meaning symbols like ' and % are escaped. This causes a problem when you have to do wildcard searches. With a like operator.

Say, You have to look up employees by first name. The SQL would look like
select * from emp where first_name = 'sarath'
and the iBatis Query would be simply

<select id="getEmpByFName">
select * from emp where first_name = #value#
</select>

On the same lines, A wildcard search of firstname would be (in SQL)
select * from emp where first_name like '%sar%'
But, if you just make an iBatis select like:

<select id="getEmpByFName">
select * from emp where first_name like #value#
</select>

and make it concatinate with "%" before passing it to queryForList(), You will see nothing in results. This is because iBatis will escape % too.

Some googling got me to use $value$ substitution instead. What this means is you would do something like

<select id="getEmpByFName">
select * from emp where first_name like '%$value$%'
</select>

This will DO JUST FINE, but you get the same SQL injection vulnerability, that you dropped java.sql.Statement for, in the first place.

Here is an elegant solution: concatinate the '%' in sql instead of java.

<select id="getEmpByFName">
select * from emp where first_name like '%' || #value# || '%'
</select>

Try it on, if it fails, comment on.