real_escape_string

The mysqli_real_escape_string() function escapes special characters in a string so it can be safely placed inside an SQL query. This page explains what the function does, its syntax and return value, the charset gotcha that breaks it, the deprecated mysql_real_escape_string() it replaced, and why prepared statements are the better choice today.

What mysqli_real_escape_string() does

When you build an SQL query by concatenating user input directly into the query string, a value like O'Reilly ends the quoted string early and the rest is treated as SQL. An attacker can exploit this to read, modify, or delete data — an SQL injection attack.

mysqli_real_escape_string() prevents this by adding backslashes before the characters that have special meaning inside a MySQL string literal: the single quote ', double quote ", backslash \, NUL byte, newline \n, carriage return \r, and Ctrl+Z. After escaping, the value is safe to embed between quotes in a query.

It is "real" because it consults the connection's character set, so it escapes correctly even for multi-byte encodings — something the older addslashes() cannot do.

Syntax

mysqli_real_escape_string(mysqli $connection, string $string): string

It returns the escaped string. Note that it does not add the surrounding quotes — you still write '$escaped' in the query yourself.

In object-oriented style, call it as a method: $mysqli->real_escape_string($string).

How to use it

<?php
$con = mysqli_connect('localhost', 'username', 'password', 'database');

if (!$con) {
    exit('Could not connect: ' . mysqli_error($con));
}

// Set charset to prevent multi-byte character vulnerabilities
mysqli_set_charset($con, 'utf8mb4');

$name = "John O'Reilly";
$name = mysqli_real_escape_string($con, $name);

$sql = "INSERT INTO customers (name) VALUES ('$name')";

if (!mysqli_query($con, $sql)) {
    exit('Error: ' . mysqli_error($con));
}

echo '1 record added';

mysqli_close($con);
?>

Here we connect with mysqli_connect(), define $name with a single quote, and escape it. The escaped value John O\'Reilly slots safely into the '$name' placeholder, so the INSERT runs without a syntax error and without opening an injection hole. See Insert data into MySQL for the full insert workflow.

Set the charset first

mysqli_real_escape_string() only escapes correctly if the connection knows its character set. Always call mysqli_set_charset() right after connecting:

mysqli_set_charset($con, 'utf8mb4');

Skipping this on certain encodings (notably GBK) leaves a multi-byte injection vector where the escaping backslash gets "eaten" by a multi-byte sequence. Setting the charset on the connection — not just in the query — closes that hole.

What it does not protect

Escaping makes a value safe inside a quoted string literal. It does not make values safe in places where you can't use quotes — table and column names, LIMIT numbers, or keywords. Never escape an identifier and drop it into a query; validate it against an allow-list instead.

// WRONG — escaping does nothing useful for an identifier
$column = mysqli_real_escape_string($con, $_GET['sort']);
$sql = "SELECT * FROM users ORDER BY $column"; // still injectable

// RIGHT — allow-list
$allowed = ['name', 'email', 'created_at'];
$column  = in_array($_GET['sort'], $allowed, true) ? $_GET['sort'] : 'name';

mysql_real_escape_string() vs mysqli_real_escape_string()

The old mysql_real_escape_string() (no i) belonged to the original mysql_* extension, which was removed in PHP 7. Use the mysqli_* version — or PDO — on any modern PHP. If you call it without a live connection it silently opens a default one and emits a warning, which is another reason it was retired.

Prefer prepared statements

Escaping works, but it relies on you never forgetting a single value. Prepared statements are safer because the query template and the data travel separately, so user input can never change the structure of the query:

<?php
$con = mysqli_connect('localhost', 'username', 'password', 'database');
mysqli_set_charset($con, 'utf8mb4');

$stmt = mysqli_prepare($con, 'INSERT INTO customers (name) VALUES (?)');
mysqli_stmt_bind_param($stmt, 's', $name);

$name = "John O'Reilly"; // no manual escaping needed
mysqli_stmt_execute($stmt);

echo '1 record added';
mysqli_close($con);
?>

Use mysqli_real_escape_string() when you genuinely have to build dynamic SQL by hand; reach for mysqli_prepare() the rest of the time. For an overview of the extension, see PHP MySQLi.

Conclusion

mysqli_real_escape_string() escapes the special characters that would otherwise break — or be exploited in — an SQL string literal. Set the connection charset first, remember it does nothing for identifiers, and prefer prepared statements wherever you can.

Practice