Today when I started my regular daily job , I came across an interesting problem that was related to sql queries and how they treat special characters. The root of the problem was whenever I tried to search any record on the basis of some condition and if that condition contains special characters then my query was not returning me any record.
Focal Point of this post will be – String Append Vs Parameter Binding comparison
e.g Let assume there is a table (USER_DATA) that contains 3 columns – Id, UserName and Location and below is some sample data .
Now suppose if I execute this query
Select * from USER_DATA where USER_NAME like ‘%%A%’;
Here , %A is my search criteria, after this query execution it is not returning me any record.
I was getting No Records Found.
The reason behind this ( %(Percentile) , _(Under Score) , \ (Backward Slash) ,’ (Single Quote)) are considered as special characters in SQL and all of them have their special meanings.
So, now if you want to fetch those records that contain these special characters, you have to handle that thing separately in your sql query or java code (if you are using String append).
e.g If I run the same query again after doing slight modification like this
select * from USER_DATA where USER_NAME like ‘%\%A%’;
Now it will return me a record as expected.
As you can see here I had used ‘\’ (backslash) to remove the special meaning of ‘%’ and after that sql engine treated it as a normal character. The same logic will be applicable for other two characters (_ (UnderScore) , ‘(Single Quote)) but in the case of ‘\’ (backslash) it will be different because it is considered as a special character by both ‘like’ and string literals. So, you have to take an extra care of it.
Select * from USER_DATA where USER_NAME like ‘%\\\%’;
All this is applicable when I running all these queries from sql editor , but main problem starts arising when we are using java to execute these queries , as all we know in Java ‘\’ (backslash) has a very special meaning. So, extreme care will be needed when we are executing the queries from java code and to add condition in where clause we are using String Append instead of Parameter Binding. You have to handle like this in your java code.
So , in my case when I looked at my code it was using String Append everywhere to add the params to where clause and that was the root cause of the problem that I had explained above , as code was not handling special characters separately.
So, after this I had two options either to solve this problem as explained above or use parameter binding. I decided to use Parameter binding instead of String append and as expected after doing this all the special characters cases have been handled automatically without any extra code.
That was the one of the advantage of using Parameter Binding ,very soon I am going to write the next part of this article where I will focus on other advantages of Parameter Binding.
You can read the next post here.
Latest posts by Saurabh Jain (see all)
- java.lang.IncompatibleClassChangeError: Found interface org.apache.hadoop.mapreduce.TaskInputOutputContext, but class was expected - August 8, 2014
- org.datanucleus.store.rdbms.exceptions.MappedDatastoreException: INSERT INTO “TABLE_PARAMS” – Hive with Kite Morphlines - July 17, 2014
- java.io.IOException: can not read class parquet.format.PageHeader: null – Hive - July 12, 2014