Introduction

Apostrophes (single quotes ') in SQL are string delimiters. Inserting text like O'Brien or it's directly into a query breaks the SQL syntax and can create SQL injection vulnerabilities. The correct solution is to use parameterized queries (prepared statements), which handle escaping automatically. Manual escaping with backslashes or doubled quotes is error-prone and should be avoided.

The Problem

1-- This breaks — the apostrophe terminates the string early
2INSERT INTO users (name) VALUES ('O'Brien');
3-- MySQL sees: VALUES ('O' followed by Brien');  — syntax error
4
5-- This is an SQL injection attack
6INSERT INTO users (name) VALUES (''; DROP TABLE users; --');

Solution 1: Parameterized Queries (Recommended)

Always use parameterized queries. The database driver handles all escaping:

Python (mysql-connector / PyMySQL)

1import mysql.connector
2
3conn = mysql.connector.connect(host='localhost', database='mydb',
4                                user='root', password='pass')
5cursor = conn.cursor()
6
7# Parameterized — safe from injection
8name = "O'Brien"
9cursor.execute("INSERT INTO users (name) VALUES (%s)", (name,))
10conn.commit()
11
12# Multiple values
13data = [("O'Brien",), ("D'Angelo",), ("It's fine",)]
14cursor.executemany("INSERT INTO users (name) VALUES (%s)", data)
15conn.commit()

PHP (PDO)

1$pdo = new PDO('mysql:host=localhost;dbname=mydb', 'root', 'pass');
2
3// Prepared statement — safe
4$stmt = $pdo->prepare("INSERT INTO users (name) VALUES (:name)");
5$stmt->execute(['name' => "O'Brien"]);
6
7// Or with positional placeholders
8$stmt = $pdo->prepare("INSERT INTO users (name) VALUES (?)");
9$stmt->execute(["O'Brien"]);

Java (JDBC)

1String sql = "INSERT INTO users (name) VALUES (?)";
2PreparedStatement stmt = conn.prepareStatement(sql);
3stmt.setString(1, "O'Brien");
4stmt.executeUpdate();

Node.js (mysql2)

1const mysql = require('mysql2/promise');
2
3const conn = await mysql.createConnection({ host: 'localhost', database: 'mydb' });
4
5// Parameterized
6await conn.execute('INSERT INTO users (name) VALUES (?)', ["O'Brien"]);

C# (MySqlConnector)

using var cmd = new MySqlCommand("INSERT INTO users (name) VALUES (@name)", conn);
cmd.Parameters.AddWithValue("@name", "O'Brien");
cmd.ExecuteNonQuery();

Solution 2: Escape the Apostrophe in SQL

If you must write raw SQL (e.g., in MySQL CLI), double the single quote:

1-- Double the apostrophe to escape it
2INSERT INTO users (name) VALUES ('O''Brien');
3
4-- MySQL also accepts backslash escaping (non-standard)
5INSERT INTO users (name) VALUES ('O\'Brien');

The doubled-quote approach ('') is the SQL standard. The backslash approach (\') is MySQL-specific and depends on the NO_BACKSLASH_ESCAPES SQL mode.

Solution 3: mysql_real_escape_string (PHP Legacy)

1// Old approach — use PDO prepared statements instead
2$name = mysqli_real_escape_string($conn, "O'Brien");
3$sql = "INSERT INTO users (name) VALUES ('$name')";
4mysqli_query($conn, $sql);

This is the legacy approach and is not recommended. It's error-prone (forget to call it once and you have an injection vulnerability). Use PDO prepared statements.

Why Not Just Escape Manually?

1# WRONG — manual escaping is fragile
2name = "O'Brien"
3name_escaped = name.replace("'", "\\'")
4query = f"INSERT INTO users (name) VALUES ('{name_escaped}')"
5# Seems to work but is vulnerable to:
6# - Double-encoding attacks
7# - Character set mismatches
8# - Missing edge cases

Parameterized queries separate the SQL structure from the data at the protocol level. The database engine never confuses data for SQL syntax, making injection impossible.

Handling Apostrophes in SELECT/WHERE

1# Parameterized WHERE clause — safe
2cursor.execute("SELECT * FROM users WHERE name = %s", ("O'Brien",))
3
4# LIKE with wildcards
5cursor.execute("SELECT * FROM users WHERE name LIKE %s", ("%O'Brien%",))

-- Raw SQL in MySQL CLI
SELECT * FROM users WHERE name = 'O''Brien';
SELECT * FROM users WHERE name LIKE '%O''Brien%';

Common Pitfalls

• String concatenation in queries: Never build SQL by concatenating user input: f"SELECT * FROM users WHERE name = '{user_input}'". This is the #1 cause of SQL injection vulnerabilities.
• NO_BACKSLASH_ESCAPES mode: If MySQL has NO_BACKSLASH_ESCAPES enabled, \' does not work — only '' (doubled quotes) is valid. Parameterized queries work regardless of SQL mode.
• ORM safety: ORMs like SQLAlchemy, Django ORM, and ActiveRecord use parameterized queries internally. Direct .filter(name="O'Brien") is safe. But raw SQL through the ORM (.raw()) needs manual parameterization.
• Stored procedures: Even inside stored procedures, use prepared statements for dynamic SQL: PREPARE stmt FROM ...; EXECUTE stmt USING @param;
• Multi-byte character attacks: In some character sets, specially crafted multi-byte characters can bypass escaping functions. Parameterized queries are immune to this class of attacks.

Summary

• Always use parameterized queries (prepared statements) — they handle apostrophes and prevent SQL injection automatically
• Never concatenate user input into SQL strings
• In raw SQL (MySQL CLI), escape apostrophes by doubling them: 'O''Brien'
• Every language has parameterized query support: %s (Python), ? (PHP/Java/Node), @param (C#)
• Manual escaping functions (mysql_real_escape_string, replace("'", "\\'")) are fragile and should not be used