Introduction
Unescaped single quotes in SQL strings break queries and create SQL injection vulnerabilities. The correct solution is prepared statements (PDO or MySQLi), which separate SQL structure from data entirely. Manual escaping with mysqli_real_escape_string() is a fallback, but it is error-prone and unnecessary when prepared statements are available. Never use addslashes() or string replacement for SQL. They do not handle all edge cases and leave you vulnerable.
The Problem
1$name = "O'Brien";
2
3// BROKEN: single quote terminates the string early
4$sql = "INSERT INTO users (name) VALUES ('$name')";
5// Becomes: INSERT INTO users (name) VALUES ('O'Brien')
6// ^^^ syntax error
7
8// DANGEROUS: attacker can inject SQL
9$name = "'; DROP TABLE users; --";
10$sql = "INSERT INTO users (name) VALUES ('$name')";
11// Becomes: INSERT INTO users (name) VALUES (''; DROP TABLE users; --')
Fix 1: PDO Prepared Statements (Recommended)
1$pdo = new PDO('mysql:host=localhost;dbname=mydb', 'user', 'password');
2$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
3
4$name = "O'Brien";
5$email = "[email protected]";
6
7// Named parameters
8$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");
9$stmt->execute(['name' => $name, 'email' => $email]);
10
11// Positional parameters
12$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
13$stmt->execute([$name, $email]);
Prepared statements send the SQL template and values separately to MySQL. The database handles quoting and escaping, so the single quote in "O'Brien" is never part of the SQL structure.
Fix 2: MySQLi Prepared Statements
1$mysqli = new mysqli('localhost', 'user', 'password', 'mydb');
2
3$name = "O'Brien";
4$email = "[email protected]";
5
6$stmt = $mysqli->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
7$stmt->bind_param("ss", $name, $email);
8// "ss" = two string parameters. Use "i" for int, "d" for double, "b" for blob
9$stmt->execute();
10
11// Check for errors
12if ($stmt->affected_rows > 0) {
13 echo "Inserted successfully, ID: " . $stmt->insert_id;
14}
15
16$stmt->close();
Fix 3: PDO with SELECT
1// Search with user input, safe from injection
2$search = "O'Brien";
3
4$stmt = $pdo->prepare("SELECT * FROM users WHERE name LIKE :search");
5$stmt->execute(['search' => "%$search%"]);
6$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
7
8foreach ($users as $user) {
9 echo $user['name'] . "\n";
10}
Fallback: mysqli_real_escape_string()
If you cannot use prepared statements (legacy code), escape manually:
1$mysqli = new mysqli('localhost', 'user', 'password', 'mydb');
2
3$name = $mysqli->real_escape_string("O'Brien");
4// Returns: O\'Brien
5
6$sql = "INSERT INTO users (name) VALUES ('$name')";
7$mysqli->query($sql);
This escapes single quotes, double quotes, backslashes, NUL bytes, and other special characters. The connection object is required because escaping depends on the connection's character set.
Why NOT to Use These Approaches
1// WRONG: addslashes() does not respect MySQL's character encoding
2$name = addslashes("O'Brien");
3// May fail with multi-byte characters (e.g., GBK encoding)
4
5// WRONG: str_replace misses other special characters
6$name = str_replace("'", "\\'", "O'Brien");
7// Does not escape backslashes, NUL bytes, etc.
8
9// WRONG: magic_quotes_gpc (removed in PHP 5.4)
10// Never rely on this. It was removed for good reason
Batch Inserts with Prepared Statements
1$pdo = new PDO('mysql:host=localhost;dbname=mydb', 'user', 'password');
2
3$users = [
4 ['name' => "O'Brien", 'email' => '[email protected]'],
5 ['name' => "D'Angelo", 'email' => '[email protected]'],
6 ['name' => "McDonald's", 'email' => '[email protected]'],
7];
8
9$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");
10
11$pdo->beginTransaction();
12foreach ($users as $user) {
13 $stmt->execute($user);
14}
15$pdo->commit();
The prepared statement is parsed once and executed multiple times. This is faster and safer than building individual SQL strings.
PDO Connection Best Practices
1$dsn = 'mysql:host=localhost;dbname=mydb;charset=utf8mb4';
2$options = [
3 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
4 PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
5 PDO::ATTR_EMULATE_PREPARES => false, // Use real prepared statements
6];
7
8$pdo = new PDO($dsn, 'user', 'password', $options);
Setting EMULATE_PREPARES to false ensures the database driver sends the query template and parameters separately, giving true prepared statement protection.
LIKE Queries with Special Characters
1// User input may contain % or _ which are LIKE wildcards
2$search = "100%";
3
4// Escape LIKE wildcards manually, then use prepared statement
5$escaped = str_replace(['%', '_'], ['\\%', '\\_'], $search);
6$stmt = $pdo->prepare("SELECT * FROM products WHERE name LIKE :search");
7$stmt->execute(['search' => "%$escaped%"]);
Common Pitfalls
String interpolation with quotes: Never put user input directly in SQL strings ("VALUES ('$name')"). Always use prepared statements. No amount of escaping is as safe as parameterized queries.
PDO emulated prepares: By default, PDO emulates prepared statements in PHP. Set PDO::ATTR_EMULATE_PREPARES => false to use real server-side prepared statements for proper type handling and security.
Forgetting the charset: Without charset=utf8mb4 in the DSN, mysqli_real_escape_string() may not correctly handle multi-byte characters, leaving SQL injection possible even with escaping.
Using deprecated functions: mysql_real_escape_string() (without the i) is from the removed mysql_* extension. Use mysqli_real_escape_string() or PDO.
Escaping integers: For integer parameters, use bind_param("i", $id) or cast with (int)$id. Quoting integers as strings can cause index performance issues.
Summary
Always use prepared statements (PDO or MySQLi). They separate SQL from data completely
PDO with named parameters (:name) is the cleanest approach
Set EMULATE_PREPARES => false and charset=utf8mb4 in the PDO connection
mysqli_real_escape_string() is a fallback for legacy code only
Never use addslashes(), str_replace(), or string interpolation for SQL
Prepared statements prevent SQL injection by design, not by escaping
