I post on various forums around the net, and a few of my posts there get some very gratifying kudos. I’ve been a diligent contributor to StackOverflow, the Q-and-A site for software developers. I’m in the top 5% of contributors overall. Here’s my top-voted answer in StackOverflow currently.

The question, How to escape apostrophe (’) in MySql?,  was asked by anonymous user4951 in March 2012 (and copy-edited by someone else). In abbreviated form, it was:


How to escape apostrophe (’) in MySql? 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?


This is my answer to StackOverflow.


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 [as of 2012, now 8.0 as of 2019 —JDLH] — but the current Table [9].1. Special Character Escape Sequences [link and table number updated for 8.0 as of 2019 —JDLH] looks pretty similar.)

I think the Postgres note on the backslash_quote (string) parameter [link updated to current version as of 2019 —JDLH] 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.


profile for Jim DeLaHunt on Stack Exchange, a network of free, community-driven Q&A sitesPostscript: It is a wonder of the Stack Exchange Q&A sites that some answers seem to really strike a chord, and earn upvotes year after year. I would have picked other answers as providing a special technical insight. However, this information clearly seems to meet a need.  I’ll take it.