How to escape single quote in string literal using MySQL from Java

Question

I have a MySql table person_details which contains p_id and p_name. Now, if I want to insert a record where p_name contains a single quote ', I'd execute it this way-

insert into person_details values (1, 'D\'souza');

Now, I'm trying to execute the same through a java code this way-

insert into person_details values (1, 'D\\\'souza');

and I get MySQLSyntaxErrorException.

Anything wrong?

Answer 1

To answer your question directly, double the quotes.

insert into person_details values (1, 'D''souza');

But I rather parameterized the query using PreparedStatement.

Here are the PROs:

• avoid from SQL Injection
• doesn't need to use single quotes.

example,

String str = "insert into person_details values (?, ?)";
query = con.prepareStatement(str);
query.setInt(1, 1);
query.setString(2, "D'souza");
query.executeUpdate();

• Using Prepared Statements

Answer 2

In MySQL, you use '' for a single ' inside a string:

insert into person_details values (1, 'D''souza');

Link to docs

But that's only for when you're providing the data literally, such as an SQL script to pre-populate a table with data you control, etc. If you're receiving that string from an external source (an end user, for instance, or an external system) then presumably you won't be writing a literal, but rather using a string variable. In that case, you want to use prepared statements as JW. describes in his answer. Why: http://xkcd.com/327/

Answer 3

You could also use "

insert into person_details values (1, "D'souza");

Answer 4

Why dont you use PreparedStatements

It will also take care of SQL Injections