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

[Andrew Carney]

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]

try using \' instead of '.

[Andrew Carney]

Tried it, not working.

[Jesper de Jong]

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

String sql = "INSERT INTO USERS (USERNAME, PASSWORD) VALUES ('" + username + "','" + password "')";

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).

Connection cn = ...;
 
String sql = "INSERT INTO USERS (USERNAME, PASSWORD) VALUES (?, ?)";
 
PreparedStatement ps = cn.prepareStatement(sql);
 
ps.setString(1, username);
ps.setString(2, password);
 
ps.executeUpdate();

[Andrew Carney]

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]

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]

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.