PHP mysqli real_escape_string() Function

Example - Object Oriented style

Escape special characters in strings:

<?php
$mysqli = new mysqli("localhost","my_user","my_password","my_db");

if ($mysqli -> connect_errno) {
echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
exit();
}

// Escape special characters, if any
$firstname = $mysqli -> real_escape_string($_POST['firstname']);
$lastname = $mysqli -> real_escape_string($_POST['lastname']);
$age = $mysqli -> real_escape_string($_POST['age']);

$sql="INSERT INTO Persons (FirstName, LastName, Age) VALUES ('$firstname', '$lastname', '$age')";

if (!$mysqli -> query($sql)) {
printf("%d Row inserted.\n", $mysqli->affected_rows);
}

$mysqli -> close();
?>

Look at example of procedural style at the bottom.

Definition and Usage

The real_escape_string() / mysqli_real_escape_string() function escapes special characters in a string for use in an SQL query, taking into account the current character set of the connection.

This function is used to create a legal SQL string that can be used in an SQL statement. Assume we have the following code:

<?php

$lastname = "D'Ore";

$sql="INSERT INTO Persons (LastName) VALUES ('$lastname')";

// This query will fail, cause we didn't escape $lastname
if (!$mysqli -> query($sql)) {
printf("%d Row inserted.\n", $mysqli->affected_rows);
}

?>

Syntax

Object oriented style:

$mysqli -> real_escape_string(escapestring)

Procedural style:

mysqli_real_escape_string(connection, escapestring)

Parameter Values

Parameter	Description
connection	Required. Specifies the MySQL connection to use
escapestring	Required. The string to be escaped. Characters encoded are NUL (ASCII 0), \n, \r, \, ', ", and Control-Z.

Technical Details

Return Value:	Returns the escaped string
PHP Version:	5+

Example - Procedural style

Escape special characters in strings:

<?php
$con = mysqli_connect("localhost","my_user","my_password","my_db");

if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
exit();
}

// Escape special characters, if any
$firstname = mysqli_real_escape_string($con, $_POST['firstname']);
$lastname = mysqli_real_escape_string($con, $_POST['lastname']);
$age = mysqli_real_escape_string($con, $_POST['age']);

$sql="INSERT INTO Persons (FirstName, LastName, Age) VALUES ('$firstname', '$lastname', '$age')";

if (!mysqli_query($con, $sql)) {
printf("%d Row inserted.\n", mysqli_affected_rows($con));
}

mysqli_close($con);
?>

❮ PHP MySQLi Reference