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#

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#
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$%'

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# || '%'
Try it on, if it fails, comment on.

Popular posts from this blog

Powered By

As it goes, We ought to give thanks to people who power us. This page will be updated, like the version page , to show all the tools, and people this site is Powered By! Ubuntu GIMP Firebug Blogger Google [AppEngine, Ajax and other Apis] AddtoAny Project Fondue jQuery

One page Stock

Alright.. That was a long absence. The whole last week I dint blog. I dint go away. I was "occupied". I was learning stock trading. Its very fascinating. I have a good weeeked blog for you all. Here is my experience. I can literally hyper-link every word from the following paragraphs, but I am writing it as simple as I can so you can look up the italicised words in wikipedia . I got a paper trading account from a brokerage firm . You need one brokerage account first. Then it can be an Equity account where all your money is yours or a Margin account , where some of the money is lent by the brokerage firm. Then I get Buying power , which is the dollor value of how much stocks you can buy. I can make profit by simple rules. Buy when Price is low. Sell when price is high. There is another more intersting way of earning money. Selling short . Thats when price is not high, per say, but when are confident that the price WILL go down. then buy back when its lowest. This is what

Decorator for Memcache Get/Set in python

I have suggested some time back that you could modularize and stitch together fragments of js and css to spit out in one HTTP connection. That makes the page load faster. I also indicated that there ways to tune them by adding cache-control headers. On the server-side however, you could have a memcache layer on the stitching operation. This saves a lot of Resources (CPU) on your server. I will demonstrate this using a python script I use currently on my site to generate the combined js and css fragments. So My stitching method is like this @memize(region="jscss") def joinAndPut(files, ext): res = files.split("/") o = StringIO.StringIO() for f in res: writeFileTo(o, ext + "/" + f + "." + ext) #writes file out ret = o.getvalue() o.close() return ret; The method joinAndPut is * decorated * by memize. What this means is, all calls to joinAndPut are now wrapped (at runtime) with the logic in memize. All you wa