aspose file tools*
The moose likes JDBC and the fly likes handling quotes in sql queries Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "handling quotes in sql queries" Watch "handling quotes in sql queries" New topic
Author

handling quotes in sql queries

shankar vembu
Ranch Hand

Joined: May 10, 2001
Posts: 309
This is in refernce to an
old post.
I am trying to insert/update string containing quotes. One way to do is of course modify the string to replace ' with ''. Another way as given in the above post is to use prepared statements which would take care of this.
I tried using a prepared statement but could not succeed.
This is my sql query

But I get an java.lang.ArrayIndexOutOfBoundsException: 0 exception when I set the int...
As per the old post, the quote in the String str should be auto handled by the prepared statement. But I am unable to see it work.
What am I missing here.....
Shankar.
Bhupendra Malviya
Greenhorn

Joined: Oct 01, 2003
Posts: 15
Hi Shankar,
change your code to the one given below and then it should work.

String str = "t'est";
String query = "update test_table set col1='test', col2=? " +
" where id = ?";
pst.setString(1,str);
pst.setInt(2,10);
shankar vembu
Ranch Hand

Joined: May 10, 2001
Posts: 309
Originally posted by Bhupendra Malviya:
Hi Shankar,
change your code to the one given below and then it should work.

String str = "t'est";
String query = "update test_table set col1='test', col2=? " +
" where id = ?";
pst.setString(1,str);
pst.setInt(2,10);

Thanx for the reply.
OK, so it means that this will work only for the strings in the parameter set.....
Thank you,
Shankar.
shankar vembu
Ranch Hand

Joined: May 10, 2001
Posts: 309
This leaves me with another question from a design point of view.
I have sql queries spread out in my application where I need to handle this escaping quote EVERYWHERE. So I wrote a helper method which would modify my string. And I call this method everywhere for all the strings before I insert something into the database.
Now I am considering changing all the insert queries into prepared statement queries , so that I dont have to handle this escaping quote stuff. Would this be a better design, also given the fact that I dont execute these insert queries repeatedly, its a one time insert. So would it make sense to modify my queries to prepared statement queries just to avoid handling this escaping quote.
Thanx,
Shankar.
Rohit Lal
Greenhorn

Joined: Sep 25, 2003
Posts: 20
Hello,
Well, I would recommend that you do switch over to Prepared Statements instead of trying work-around solutions. Prepared Statements may be expensive to build the first time round, but I feel they add a great deal of flexibility and maintainability to an application if used properly.
Sounds like a caveat? It isn't actually. Not if you have already built yourself a neat little Data Access Object (DAO) class with all the prepared statements initialised on instantiation of this class. Then with appropriate method calls you can pass arguements to call the relevant statement and execute it. All your JDBC code encapsulated in one DAO class.
Makes life a lot simpler.


Rohit Lal<br />SCPJ2 (2000)
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: handling quotes in sql queries