How to escape ' (apostrophe) in mysql?

Question

When i am reading some text from an Xml and putting it to the database i am getting a error if the text contains '(apostrophe) . How to overcome this problem while i am inserting into the DB.

Don't escape - *except* if writing in raw SQL for manual processing. [*Use placeholders* for all other cases](http://stackoverflow.com/a/60496/2246674) (adapt for language). Also, see the official MySQL reference for [string literal syntax](http://dev.mysql.com/doc/refman/5.7/en/string-literals.html). — user2246674, May 21 '13 at 20:58
Use the official escaping mechanism for the programming language you're working in. Don't escape by hand because there are other characters that also need to be escaped, and doing stuff like this by hand almost always has bugs and will leave you vulnerable. — Spudley, May 21 '13 at 21:10

score 2 · Accepted Answer · answered May 21 '13 at 17:25

Where ever the apostrophe is add a \ (backslash) before it. Using your text editor to do a find and replace all for ' to \' should work. BE CAREFUL not to mess up the XML structure.

ex.

John's

Needs to be

John\'s

You can also use PHP or C# to escape it for you also. Here is the PHP function.

Mike Brant · Answer 2 · 2013-05-21T18:16:14.863

1

You should always escape you input data with whatever language-specific methodology you have for doing so. For MySQL the escape character is \. Another alternative is to use prepared statements with parametrized inputs. This would eliminate the need to escape the single apostrophe.

My guess is that you also have a significant SQL injection vulnerability with the way you are doing things. If you are not even escaping your input values or using parametrized prepared statements, then one could easily inject malicious code into the XML.

edited May 21 '13 at 18:16

answered May 21 '13 at 17:23

Mike Brant

70,514
10
99
103

If i use the prepared statement i don't need to modify my xml to replace 's by /'s right ? – Naveen May 21 '13 at 18:12
@Naveen That is correct if you use parametrized inputs. If you just do a normal SQL query with the input included in the query and just try to prepare/execute it it will still fail. – Mike Brant May 21 '13 at 18:12
i tried the prepared statement but its throwing same error. Does it depend on datatype in the DB? I am using longtext. – Naveen May 21 '13 at 18:16
sorry ! I was testing a wrong file .Its working .Thanks a lot – Naveen May 22 '13 at 06:06
@Naveen Sorry missed your follow up comment yesterday. As long as you are actually using parameterized prepared statements, you don't have the need to escape the apostrophes – Mike Brant May 22 '13 at 17:44

score 0 · Answer 3 · answered May 21 '13 at 17:19

0

I don't know what language or method you're using for your import. Typically it's the backslash character, \ . So you would need to replace 's with \'. Make sense?

answered May 21 '13 at 17:19

Henry Rivera

88
5

Without changing my XML how can i achieve this alone in Java ? – Naveen May 21 '13 at 18:17
1

Use prepared statements :) – Naveen May 22 '13 at 06:06

score 0 · Answer 4 · edited Jun 20 '20 at 09:12

From 9.1.1 String Literals in the MySQL reference:

There are several ways to include quote characters within a string:

A “'” inside a string quoted with “'” may be written as “''”.

A “"” inside a string quoted with “"” may be written as “""”.

Precede the quote character by an escape character (“\”).

A “'” inside a string quoted with “"” needs no special treatment and need not be doubled or escaped. In the same way, “"” inside a string quoted with “'” needs no special treatment.

Thus, the given string foo'bar can be written in MySQL¹:

'foo''bar'
'foo\'bar'
"foo'bar"

While the above addresses the primary question, use placeholders (aka prepared statements) - look up the correct method per language/adapter. Placeholders eliminate the need to quote (which prevents mistakes introduced by custom logic) and prevent against many cases of malicious SQL injection.

¹ The syntax chosen by MySQL differs from other common SQL implementations; this syntax is not universal.

How to escape ' (apostrophe) in mysql?

4 Answers4