aspose file tools*
The moose likes JDBC and the fly likes How to make INSERT and UPDATE with the ' character in the String? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to make INSERT and UPDATE with the Watch "How to make INSERT and UPDATE with the New topic
Author

How to make INSERT and UPDATE with the ' character in the String?

Andrew Carney
Ranch Hand

Joined: Oct 17, 2006
Posts: 96
Hello,

When I use INSERT and UPDATE queries to a database and one of the fields contain the ' char in it (for example: ab'c) the query fails.
I can run replaceAll on the String before I do the INSERT and UPDATE but I don't want to lose this character.
Any ideas...?
Anand Loni
Ranch Hand

Joined: Jan 20, 2006
Posts: 150
try using \' instead of '.


~ Anand,
SCJP 1.5, SCWCD 1.5
Andrew Carney
Ranch Hand

Joined: Oct 17, 2006
Posts: 96
Tried it, not working.
Jesper de Jong
Java Cowboy
Saloon Keeper

Joined: Aug 16, 2005
Posts: 14351
    
  22

Are you creating the SQL statement by concatenating parts and parameters together, like this?

You should not do that. Not only do you get problems like this with special characters, you might also make your code vulnerable to an SQL injection attack.

To avoid this, you should always use PreparedStatements. By doing this, the JDBC driver will take care of handling special characters in the parameters. It is also more efficient if you execute the same statement more than once, because the database then only has to parse the statement once (and execute it multiple times, but just with different data).


Java Beginners FAQ - JavaRanch SCJP FAQ - The Java Tutorial - Java SE 8 API documentation
Andrew Carney
Ranch Hand

Joined: Oct 17, 2006
Posts: 96
Hi Jesper,

I tried using PreparedStatement.
It indeed sloves the ' problem but only if it's in the middle of the word.
So:
ab'c will work
abc' won't work.

Try it...
Andrew Carney
Ranch Hand

Joined: Oct 17, 2006
Posts: 96
Hi Jesper,

My mistake it's working in all cases, I had a different problem in my code.
I will continue working with PreparedStatement, thank you for your kind help.

Roy
Chris Corbyn
Ranch Hand

Joined: Jan 14, 2007
Posts: 114
You probably get into a habit of using prepared statements from a security point of view. The ' problem you experienced the first time is not just a pain, it's a security risk because it's not *only* the ' character you need to escape. It varies on what needs escaping depending on the DBMS and the character encoding used. You risk SQL injection attacks if you simply escape '.

The prepared statement method works by using an underlying transport API provided by the DMBS so you let the DBMS deal with the raw security exploits.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to make INSERT and UPDATE with the ' character in the String?