Skip to main content

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.

Popular posts from this blog

Javascript: Convert Strings to Binary (and representing in a nerdy way!)

I follow those GoogleDevelopers Videos . Sometime back, in one of the presentations on GoogleIO, there was this interesting string of dots at the bottom of each page of the presentation . They looked like random big and small dots. A similar bunch of dots were also on the T-shirt of a presenter was wearing in another presentation . While it seemed something in the pattern, I could not find what it was. Finally, another presenter cleared the matter that those dots are just binary representation of "GOOGLEIO" (So much for advertizing Google IO, Impressive!). So I wanna do it. Takes me back to days of those DSP classes at school. Nerdy me had to churn some old brain cells. I remember those first programming language classes in Pascal and C when you were asked to do fibonacci series and converting a binary string to ascii codes. That *experience* came handy here: Check it out! Text to Binarize: For those who came to copy the javascript code to convert string to binary,

MySql Copying Table Structures.

Some times you need to copy only table structures across databases. This article describes two ways of doing it. If the whole database schema need to be exported, mysqldump is very effective. A --nodata flag will dump all tables' schema. Like this. mysqldump --nodata -p -u username databaseName But if you want to copy a specific table, individually, you could use "create table like" feature. You could create it even from a different database. However it must be on the same mysqld instance. Like this. create table newtable like oldtable; --Or from a table in other database create table mytable like otherdatabase.tablename;

javascript maxlength for textarea with \r\n breaks in java (esp Firefox)

Textareas allow new lines to enter. These are represented by \n (1) or \r\n (2) characters. But when you save to DB you have a limit to certain length of chars. There is no maxlength attribute in HTML that will stop you from entering data. This is generally acomplished by Javascript. You do a onkeyup hook and stop event or trim after textarea.value.length > maxlength. There are many other solutions out there.. But.. Here is the problem that most of those solutions overlook, How do you deal with the count on \n and \r\n representations. Lets first see how it matters. If the text entered has new lines, the length is calculated differently in Firefox and IE. When you enter a Text like 01234 567890 You expect the textarea.value.length to be 11. (10 chars + new line).On the backend, however, java would recieve it as 12 chars (10 chars + \r\n) (this is irrespective of FF or IE). So you are effectively saving 12 chars to DB. Worse yet, IE seems to figure textarea.value.length as 12 (