• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to Escape single quotes with PreparedStatment while using PostGresql?

 
Vic Hood
Ranch Hand
Posts: 477
Eclipse IDE Java Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,
I'm trying to write a preparedstatement query as below.

However , the query fails with postgresql when a single quote is passed into it.I was under the impression that Prepared statement would take care of the same . But can anyone explain why I'm getting the error?
Thank you.
 
Matthew Brown
Bartender
Posts: 4567
8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
PreparedStatement will take care of it if you use parameterized queries. But not if you try to dynamically build the SQL statement yourself - then you're just passing invalid SQL to the Statement, and it can't cope.

If you look at the Javadocs for PreparedStatement, there's a simple example of how a parameterized query is used.
 
Vic Hood
Ranch Hand
Posts: 477
Eclipse IDE Java Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Matthew Brown wrote:PreparedStatement will take care of it if you use parameterized queries. But not if you try to dynamically build the SQL statement yourself - then you're just passing invalid SQL to the Statement, and it can't cope.

If you look at the Javadocs for PreparedStatement, there's a simple example of how a parameterized query is used.

Thanks for replying Mathew!

So your suggestiong something like this..?
 
Matthew Brown
Bartender
Posts: 4567
8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, that doesn't have any parameters, so it makes no difference.

Using parameterised queries means that instead of doing this:
You do this:

Then you don't have to worry about escaping nameVar, as the database drivers will do it for you. It's easier and safer.
 
Vic Hood
Ranch Hand
Posts: 477
Eclipse IDE Java Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you for your replies, Matt! The prepared statement block that I try to execute , after adding parameters is as follows ,(I've simplified the query so that I can understand the concept)

However upon executing the block, I get an error as follows

Any idea why this could be happening?
 
Vic Hood
Ranch Hand
Posts: 477
Eclipse IDE Java Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
-- EDIT :It was a case of missing quotes.Figured it out.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic