MySQL and PHP
Copyright 1997-2021 the PHP Documentation Group.
mysqli::real_escape_string
mysqli::escape_string
mysqli_real_escape_string
Escapes special characters in a string for use in an SQL statement, taking into account the current charset of the connection
Object oriented style
public string mysqli::escape_string(string escapestr);
string mysqli::real_escape_string(string escapestr);
Procedural style
string mysqli_real_escape_string(mysqli link,
string escapestr);
This function is used to create a legal SQL string that you can use in an SQL statement. The given string is encoded to an escaped SQL string, taking into account the current character set of the connection.
The character set must be set either at the server level, or
with the API function
mysqli_set_charset
for it to affect
mysqli_real_escape_string
.
See the concepts section on
character
sets for more information.
link
Procedural style only: A link identifier returned by
mysqli_connect
or
mysqli_init
escapestr
The string to be escaped.
Characters encoded are NUL (ASCII 0), \n, \r,
\, ', ", and Control-Z
.
Returns an escaped string.
Executing this function without a valid MySQLi connection passed
in will return null
and emit
E_WARNING
level errors.
Example 3.62 mysqli::real_escape_string
example
Object oriented style
<?php $mysqli = new mysqli("localhost", "my_user", "my_password", "world"); /* check connection */ if (mysqli_connect_errno()) { printf("Connect failed: %s\n", mysqli_connect_error()); exit(); } $mysqli->query("CREATE TEMPORARY TABLE myCity LIKE City"); $city = "'s Hertogenbosch"; /* this query will fail, cause we didn't escape $city */ if (!$mysqli->query("INSERT into myCity (Name) VALUES ('$city')")) { printf("Error: %s\n", $mysqli->sqlstate); } $city = $mysqli->real_escape_string($city); /* this query with escaped $city will work */ if ($mysqli->query("INSERT into myCity (Name) VALUES ('$city')")) { printf("%d Row inserted.\n", $mysqli->affected_rows); } $mysqli->close(); ?>
Procedural style
<?php $link = mysqli_connect("localhost", "my_user", "my_password", "world"); /* check connection */ if (mysqli_connect_errno()) { printf("Connect failed: %s\n", mysqli_connect_error()); exit(); } mysqli_query($link, "CREATE TEMPORARY TABLE myCity LIKE City"); $city = "'s Hertogenbosch"; /* this query will fail, cause we didn't escape $city */ if (!mysqli_query($link, "INSERT into myCity (Name) VALUES ('$city')")) { printf("Error: %s\n", mysqli_sqlstate($link)); } $city = mysqli_real_escape_string($link, $city); /* this query with escaped $city will work */ if (mysqli_query($link, "INSERT into myCity (Name) VALUES ('$city')")) { printf("%d Row inserted.\n", mysqli_affected_rows($link)); } mysqli_close($link); ?>
The above examples will output:
Error: 42000 1 Row inserted.
For those accustomed to using
mysql_real_escape_string
,
note that the arguments of
mysqli_real_escape_string
differ from what
mysql_real_escape_string
expects. The link
identifier comes
first in
mysqli_real_escape_string
,
whereas the string to be escaped comes first in
mysql_real_escape_string
.
mysqli_set_charset
|
mysqli_character_set_name
|