Introduction

In MySQL string literals, a single quote ends the string, so an apostrophe inside the text has to be handled correctly. The SQL-level answer is to escape it inside the literal, but the application-level answer is usually not to build that literal by hand at all. In real code, parameterized queries are safer and simpler than manual escaping.

SQL Literal Syntax: Double the Quote

The portable SQL way to include a single quote inside a string literal is to double it.

SELECT 'O''Brien';

That produces the text O'Brien. The doubled quote does not mean two apostrophes in the result. It means one apostrophe inside the SQL string literal.

The same rule applies in INSERT and UPDATE statements:

INSERT INTO authors (name) VALUES ('O''Brien');

If you are writing raw SQL manually, this is the standard SQL-compatible way to represent the character.

MySQL Also Supports Backslash Escaping in Many Setups

In MySQL, you will also see backslash escaping:

SELECT 'O\\'Brien';

This often works, but it depends on SQL mode. If the server runs with NO_BACKSLASH_ESCAPES, backslash is no longer treated as an escape character in string literals.

That is why doubled quotes are usually the safer SQL-level recommendation. They are standard and more portable across databases.

Best Practice: Use Parameters Instead of Manual Escaping

In application code, you should usually avoid building SQL strings with embedded values. Let the database driver handle quoting and escaping through parameters.

Python example:

1import mysql.connector
2
3connection = mysql.connector.connect(
4    host="localhost",
5    user="app_user",
6    password="secret",
7    database="app_db",
8)
9
10cursor = connection.cursor()
11name = "O'Brien"
12
13cursor.execute(
14    "INSERT INTO authors (name) VALUES (%s)",
15    (name,),
16)
17
18connection.commit()
19cursor.close()
20connection.close()

This is better than manually escaping the apostrophe because:

• it avoids SQL injection risks
• it keeps SQL readable
• it lets the driver handle the database-specific quoting rules

If the value contains apostrophes, backslashes, or other special characters, the driver deals with them correctly.

Why Manual String Building Is Dangerous

Apostrophes are not the only problem. Once code starts concatenating SQL strings, every user-controlled value becomes a security concern.

Bad pattern:

name = "O'Brien"
sql = "INSERT INTO authors (name) VALUES ('" + name + "')"

This breaks as soon as the string contains a quote, and it also opens the door to injection attacks if the input is malicious. Escaping by hand tends to grow into fragile code very quickly.

Literal Escaping Still Matters in Direct SQL Tools

Even though parameterization is the application-level best practice, literal escaping is still useful when:

• writing one-off SQL in a MySQL shell
• building migration scripts
• debugging with ad hoc statements

In those contexts, doubling the quote is the cleanest habit:

UPDATE customers
SET last_name = 'D''Angelo'
WHERE id = 42;

That keeps the intent obvious and does not depend on special MySQL backslash behavior.

Character Sets Are a Separate Concern

Escaping an apostrophe is about SQL syntax, not text encoding. If you see mojibake or broken Unicode characters, the problem is probably client encoding, connection collation, or table charset, not single-quote escaping.

It helps to separate these concerns:

• apostrophe escaping fixes parser syntax
• character set configuration fixes text encoding

Mixing them together makes debugging harder.

Common Pitfalls

• Building SQL strings by concatenation and trying to escape quotes by hand.
• Assuming backslash escaping always works regardless of MySQL SQL mode.
• Forgetting that the SQL-standard escape form is doubled single quotes.
• Treating quote escaping as sufficient protection against SQL injection.
• Confusing syntax escaping with character encoding issues.

Summary

• In SQL literals, a single quote inside the text is commonly written as two single quotes.
• MySQL often supports backslash escaping too, but doubled quotes are more portable and reliable.
• In application code, parameterized queries are usually the correct solution.
• Avoid manual SQL string concatenation whenever possible.
• Separate quote-escaping problems from character-encoding problems when debugging.