PHP mysqli real_escape_string() Method

The PHP mysqli::real_escape_string() / mysqli_real_escape_string() function is used to create a legal SQL string that can be used in an SQL statement. The given string is encoded to produce an escaped SQL string, taking into account the current character set of the connection.

Syntax

//Object-oriented style
public mysqli::real_escape_string(string)

//Procedural style
mysqli_real_escape_string(mysql, string)

Parameters

`mysql`	`Required.` For procedural style only: Specify a mysqli object returned by mysqli_connect() or mysqli_init().
`string`	`Required.` Specify the string to be escaped. Characters encoded are NUL (ASCII 0), \n, \r, \, ', ", and Control-Z..

Return Value

Returns an escaped string.

Example: Object-oriented style

The example below shows the usage of mysqli::real_escape_string() method.

<?php
//establishing connection to the database
$mysqli = new mysqli("localhost", "user", "password", "database");
if ($mysqli->connect_errno) {
  echo "Failed to connect to MySQL: ". $mysqli->connect_error;
  exit();
}

$city = "N'Djamena";

//this query with escaped $city will work
$sql = sprintf("SELECT Name, Age, Salary FROM Employee WHERE City='%s'",
                                 $mysqli->real_escape_string($city));
$result = $mysqli->query($sql);
printf("Select returned %d rows.\n", $result->num_rows);

//this query will fail, because $city is not escaped
$sql = sprintf("SELECT Name, Age, Salary FROM Employee WHERE City='%s'", $city);
$result = $mysqli->query($sql);
?>

The output of the above code will be similar to (word wrapped for readability):

Select returned 6 rows.

Fatal error: Uncaught mysqli_sql_exception: You have an 
error in your SQL syntax; check the manual that corresponds 
to your MySQL server version for the right syntax to 
use near N'Djamena'' at line 1 in...

Example: Procedural style

The example below shows the usage of mysqli_real_escape_string() function.

<?php
//establishing connection to the database
$mysqli = mysqli_connect("localhost", "user", "password", "database");
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: ". mysqli_connect_error();
  exit();
}

$city = "N'Djamena";

//this query with escaped $city will work
$sql = sprintf("SELECT Name, Age, Salary FROM Employee WHERE City='%s'",
                              mysqli_real_escape_string($mysqli, $city));
$result = mysqli_query($mysqli, $sql);
printf("Select returned %d rows.\n", mysqli_num_rows($result));

//this query will fail, because $city is not escaped
$sql = sprintf("SELECT Name, Age, Salary FROM Employee WHERE City='%s'", $city);
$result = mysqli_query($mysqli, $sql);
?>

The output of the above code will be similar to (word wrapped for readability):

Select returned 6 rows.

Fatal error: Uncaught mysqli_sql_exception: You have an 
error in your SQL syntax; check the manual that corresponds 
to your MySQL server version for the right syntax to 
use near N'Djamena'' at line 1 in...

❮ PHP MySQLi Reference