Practice Now
PHP
MySQL
single quote
escaping
database insertion

Escaping single quote in PHP when inserting into MySQL

System Design practice on Codemia

Work through 120+ system design problems with detailed solutions, from rate limiters to multi-region storage.

Practice system design
Databases

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

php
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; --')
php
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

php
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

php
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:

php
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

php
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

php
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

php
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

php
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
Related reading
Course
Beginner
27 lessons
10 hours
System Design Fundamentals

Build a strong foundation in designing scalable, reliable distributed systems.

View the course
Track what you have practised

A free account saves your progress, solutions and study plan across every problem on Codemia.

System Design practice on Codemia

Work through 120+ system design problems with detailed solutions, from rate limiters to multi-region storage.

Practice system design
Codemia
© 2026 Codemia
Resources
Legal
Social
All Rights Reserved.