• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Andrew Carney
Ranch Hand
Posts: 96
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 150
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
try using \' instead of '.
 
Andrew Carney
Ranch Hand
Posts: 96
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Tried it, not working.
 
Jesper de Jong
Java Cowboy
Saloon Keeper
Posts: 15441
41
Android IntelliJ IDE Java Scala Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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).
 
Andrew Carney
Ranch Hand
Posts: 96
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 96
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 114
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic