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?

use the urlencode() function – Naveen D Almeida Mar 04 '13 at 09:49 — Naveen D Almeida, Mar 04 '13 at 09:49

score 11 · Accepted Answer · answered Mar 04 '13 at 09:50

11

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

answered Mar 04 '13 at 09:50

John Woo

258,903
69
498
492

+1 Prepared statements are the way to go here if the data is coming from somewhere else. – T.J. Crowder Mar 04 '13 at 09:52
Simply Great sir. I also tried all combinations of \\\' , \\' ,\' Everything returned me SQL Syntax Error. It worked awesome – Shirishkumar Bari Feb 11 '15 at 06:33

score 4 · Answer 2 · edited May 23 '17 at 12:30

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/

score 2 · Answer 3 · answered Mar 04 '13 at 09:49

2

You could also use "

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

answered Mar 04 '13 at 09:49

Riho

4,523
3
33
48

1

Unless, of course, you're using [ANSI quotes](http://dev.mysql.com/doc/refman/5.6/en/server-sql-mode.html#sqlmode_ansi_quotes). – T.J. Crowder Mar 04 '13 at 09:52

score 2 · Answer 4 · answered Mar 04 '13 at 09:50

2

Why dont you use PreparedStatements

It will also take care of SQL Injections

answered Mar 04 '13 at 09:50

rajesh

3,247
5
31
56

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

4 Answers4

Linked