escaping - How to escape apostrophe (') in MySql?

Question

The MySQL documentation says that it should be '. However, both scite and mysql shows that '' works. I saw that and it works. What should I do?

Answer 1

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.