How to escape apostrophe (') in MySql?
Standard SQL uses doubled-up quotes; MySQL has to accept that to be reasonably compliant.
'He said, "Don''t!"'
There are three ways I am aware of. The first not being the prettiest and the second being the common way in most programming languages:
- Use another single quote:
'I mustn''t sin!'
- Use the escape character
\
before the single quote'
:'I mustn\'t sin!'
- Use double quotes to enclose string instead of single quotes:
"I mustn't sin!"
The MySQL documentation you cite actually says a little bit more than you mention. It also says,
A “
'
” inside a string quoted with “'
” may be written as “''
”.
(Also, you linked to the MySQL 5.0 version of Table 8.1. Special Character Escape Sequences, and the current version is 5.6 — but the current Table 8.1. Special Character Escape Sequences looks pretty similar.)
I think the Postgres note on the backslash_quote (string)
parameter is informative:
This controls whether a quote mark can be represented by
\'
in a string literal. The preferred, SQL-standard way to represent a quote mark is by doubling it (''
) but PostgreSQL has historically also accepted\'
. However, use of\'
creates security risks...
That says to me that using a doubled single-quote character is a better overall and long-term choice than using a backslash to escape the single-quote.
Now if you also want to add choice of language, choice of SQL database and its non-standard quirks, and choice of query framework to the equation, then you might end up with a different choice. You don't give much information about your constraints.
What I believe user2087510 meant was:
name = 'something'
name = name.replace("'", "\\'")
I have also used this with success.