How to escape apostrophe (a single quote) in MySql?

by

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.

More Related Contents:

  1. How do I escape reserved words used as column names? MySQL/Create Table
  2. How to escape apostrophe (‘) in MySql?
  3. How to escape literal percent sign when NO_BACKSLASH_ESCAPES option is enabled?
  4. What characters have to be escaped to prevent (My)SQL injections?
  5. How do I specify unique constraint for multiple columns in MySQL?
  6. Select all columns except one in MySQL?
  7. MYSQL OR vs IN performance
  8. ERROR 1452: Cannot add or update a child row: a foreign key constraint fails
  9. MySQL Conditional Insert
  10. How to properly create composite primary keys – MYSQL
  11. Is there a MySQL option/feature to track history of changes to records?
  12. MySQL JOIN ON vs USING?
  13. How can I Insert many rows into a MySQL table and return the new IDs?
  14. How to store IPv6-compatible address in a relational database
  15. MySQL: View with Subquery in the FROM Clause Limitation
  16. MySql export schema without data
  17. Scaling solutions for MySQL (Replication, Clustering)
  18. What is cardinality in MySQL?
  19. How can I import a large (14 GB) MySQL dump file into a new MySQL database?
  20. MySql: Tinyint (2) vs tinyint(1) – what is the difference?
  21. MySQL Enum performance advantage?
  22. MySQL Inner Join Query Multiple Tables
  23. GROUP_CONCAT equivalent in Django
  24. How to make sure there is no race condition in MySQL database when incrementing a field?
  25. how to delete duplicate rows from a table in mysql
  26. Case Expression vs Case Statement
  27. “Unknown column in ‘field list'”, but column does exist
  28. MySQL licensing and GPL [closed]
  29. How to pass a variable to a IN clause?
  30. Insert and set value with max()+1 problems

Leave a Comment