I have a single quote in a name ('test') that needs to be saved to the db. As db requires me to have another single quote for this special character I am constructing hql as below:

name = processSingleQuote(name) ;
-> select dataset from DataSetImpl dataset inner join dataset.parent parent where dataset.name = '" + name + "' and parent.name = '" + parentname;

The processSingleQuote method prepends and appends a single quote and returns the modified string. So, the resultant query looks like:

-> select dataset from DataSetImpl dataset inner join dataset.parent parent where dataset.name = '''test''' and parent.name = 'parenttest'

Until now, I didn't find anything strange. Then, I am doing a flush/commit and noticing that the world ''test'' is being persisted instead of 'test'. I thought the logic used in find/select would be applicable to update/insert as well but found to be different. Is this an expected behavior?

Thanks,
Madhan.

Ummm I don't quite understand/believe what you are saying here. Perhaps you don't understand that in SQL/HQL, two single quotes is interpreted as an escaped single quote....


But anyway. Correct JDBC and Hibernate usage is to NEVER use string manipulation to set parameters! Use a named or positional parameter as per the documentation, and then your JDBC driver can handle all the subtle quoting issues correctly.

I think I should make it more clear. I understand that in order to have a single quote in name I should add another single quote (escape character) to preserve the actual single quote and that is what the processSingleQuote() method does. So, my in query it would look like

-> where name = '''test'''

The only point that I missed is that I create another object with one of its member like this.

-> obj.name = ''test''

as I believe that the db would require the escape character to preserve the single quote in name.

I then save the object obj. Now, in another session I load the same object and notice that it contains

-> obj.name = ''test''

and NOT

-> obj.name = 'test' . I was actually expecting only 'test' since the escape character should be 'escaped' while saving and it was not happening.

Anyway, I solved it just by doing this while ASSINGNING

-> obj.name = 'name' as I found that during update the extra, escape single quote wasn't necessary during save/update.

I was only curious why an escape character was required during "find" and not required later in member assignment or "save". Perhaps Hibernate is smart enough to add escape characters (while doing an insert) with an understanding that the programmer might have forgotten to do so. I hope now it is clear.

Thanks,
Madhan.

You should never do any manual escaping or quoting anywhere. Just use parameter placeholders and don't do manual query string building.