You can avoid all character escaping issues (on the PHP side) if you use prepare() and bind_param(), as an alternative to placing arbitrary string values in SQL statements. This works because bound parameter values are NOT passed via the SQL statement syntax.
mysqli::real_escape_string
mysqli_real_escape_string
(PHP 5, PHP 7, PHP 8)
mysqli::real_escape_string -- mysqli_real_escape_string — Escapa los caracteres especiales de una cadena para usarla en una sentencia SQL, tomando en cuenta el conjunto de caracteres actual de la conexión
Descripción
Estilo orientado a objetos
Estilo por procedimientos
Esta función se usa para crear una cadena SQL legal que se puede usar en una sentencia SQL. La cadena dada es codificada a una cadena SQL escapada, tomando en cuenta el conjunto de caracteres actual de la conexión.
Precaución
Seguridad: el conjunto de caracteres predeterminado
El conjunto de caracteres debe ser establecido a nivel del servidor, o con la función mysqli_set_charset() de la API para que afecte a mysqli_real_escape_string(). Véase la sección de conceptos sobre conjuntos de caracteres para más información.
Parámetros
-
link -
Sólo estilo por procediminetos: Un identificador de enlace devuelto por mysqli_connect() o mysqli_init()
-
escapestr -
La cadena a escapar.
Los caracteres codificados son
NUL (ASCII 0), \n, \r, \, ', ", y Control-Z.
Valores devueltos
Devuelve una cadena escapada.
Ejemplos
Ejemplo #1 Ejemplo de mysqli::real_escape_string()
Estilo orientado a objetos
<?php
$mysqli = new mysqli("localhost", "mi_usuario", "mi_contraseña", "world");
/* verificar la conexión */
if (mysqli_connect_errno()) {
printf("Falló la conexión: %s\n", mysqli_connect_error());
exit();
}
$mysqli->query("CREATE TEMPORARY TABLE miCiudad LIKE City");
$ciudad = "'s Hertogenbosch";
/* esta consulta fallará debido a que no escapa $ciudad */
if (!$mysqli->query("INSERT into miCiudad (Name) VALUES ('$ciudad')")) {
printf("Error: %s\n", $mysqli->sqlstate);
}
$ciudad = $mysqli->real_escape_string($ciudad);
/* esta consulta con $ciudad escapada funcionará */
if ($mysqli->query("INSERT into miCiudad (Name) VALUES ('$ciudad')")) {
printf("%d fila insertada.\n", $mysqli->affected_rows);
}
$mysqli->close();
?>Estilo por procedimientos
<?php
$enlace = mysqli_connect("localhost", "mi_usuario", "mi_contraseña", "world");
/* verificar la conexión */
if (mysqli_connect_errno()) {
printf("Falló la conexión: %s\n", mysqli_connect_error());
exit();
}
mysqli_query($enlace, "CREATE TEMPORARY TABLE miCiudad LIKE City");
$ciudad = "'s Hertogenbosch";
/* esta consulta fallará debido a que no escapa $ciudad */
if (!mysqli_query($enlace, "INSERT into miCiudad (Name) VALUES ('$ciudad')")) {
printf("Error: %s\n", mysqli_sqlstate($enlace));
}
$ciudad = mysqli_real_escape_string($enlace, $ciudad);
/* esta consulta con $ciudad escapada funcionará */
if (mysqli_query($enlace, "INSERT into miCiudad (Name) VALUES ('$ciudad')")) {
printf("%d fila insertada.\n", mysqli_affected_rows($enlace));
}
mysqli_close($enlace);
?>El resultado de los ejemplos sería:
Error: 42000 1 fila insertada.
Notas
Nota:
Para quienes estén acostumbrados a usar mysql_real_escape_string(), se ha de observar que el argumento de mysqli_real_escape_string() difiere de lo que espera mysql_real_escape_string(). El identificador
enlaceva primero en mysqli_real_escape_string(), mientras que la cadena a escapar va primero en mysql_real_escape_string().
Ver también
- mysqli_set_charset() - Establece el conjunto de caracteres predeterminado del cliente
- mysqli_character_set_name() - Devuelve el juego de caracteres predeterminado para la conexión a la base de datos
add a note
User Contributed Notes 7 notes
dave at mausner.us ¶
13 years ago
Josef Toman ¶
14 years ago
For percent sign and underscore I use this:
<?php
$more_escaped = addcslashes($escaped, '%_');
?>
arnoud at procurios dot nl ¶
20 years ago
Note that this function will NOT escape _ (underscore) and % (percent) signs, which have special meanings in LIKE clauses.
As far as I know there is no function to do this, so you have to escape them yourself by adding a backslash in front of them.
therselman at gmail dot com ¶
7 years ago
Presenting several UTF-8 / Multibyte-aware escape functions.
These functions represent alternatives to mysqli::real_escape_string, as long as your DB connection and Multibyte extension are using the same character set (UTF-8), they will produce the same results by escaping the same characters as mysqli::real_escape_string.
This is based on research I did for my SQL Query Builder class:
https://github.com/twister-php/sql
<?php
/**
* Returns a string with backslashes before characters that need to be escaped.
* As required by MySQL and suitable for multi-byte character sets
* Characters encoded are NUL (ASCII 0), \n, \r, \, ', ", and ctrl-Z.
*
* @param string $string String to add slashes to
* @return $string with `\` prepended to reserved characters
*
* @author Trevor Herselman
*/
if (function_exists('mb_ereg_replace'))
{
function mb_escape(string $string)
{
return mb_ereg_replace('[\x00\x0A\x0D\x1A\x22\x27\x5C]', '\\\0', $string);
}
} else {
function mb_escape(string $string)
{
return preg_replace('~[\x00\x0A\x0D\x1A\x22\x27\x5C]~u', '\\\$0', $string);
}
}
?>
Characters escaped are (the same as mysqli::real_escape_string):
00 = \0 (NUL)
0A = \n
0D = \r
1A = ctl-Z
22 = "
27 = '
5C = \
Note: preg_replace() is in PCRE_UTF8 (UTF-8) mode (`u`).
Enhanced version:
When escaping strings for `LIKE` syntax, remember that you also need to escape the special characters _ and %
So this is a more fail-safe version (even when compared to mysqli::real_escape_string, because % characters in user input can cause unexpected results and even security violations via SQL injection in LIKE statements):
<?php
/**
* Returns a string with backslashes before characters that need to be escaped.
* As required by MySQL and suitable for multi-byte character sets
* Characters encoded are NUL (ASCII 0), \n, \r, \, ', ", and ctrl-Z.
* In addition, the special control characters % and _ are also escaped,
* suitable for all statements, but especially suitable for `LIKE`.
*
* @param string $string String to add slashes to
* @return $string with `\` prepended to reserved characters
*
* @author Trevor Herselman
*/
if (function_exists('mb_ereg_replace'))
{
function mb_escape(string $string)
{
return mb_ereg_replace('[\x00\x0A\x0D\x1A\x22\x25\x27\x5C\x5F]', '\\\0', $string);
}
} else {
function mb_escape(string $string)
{
return preg_replace('~[\x00\x0A\x0D\x1A\x22\x25\x27\x5C\x5F]~u', '\\\$0', $string);
}
}
?>
Additional characters escaped:
25 = %
5F = _
Bonus function:
The original MySQL `utf8` character-set (for tables and fields) only supports 3-byte sequences.
4-byte characters are not common, but I've had queries fail to execute on 4-byte UTF-8 characters, so you should be using `utf8mb4` wherever possible.
However, if you still want to use `utf8`, you can use the following function to replace all 4-byte sequences.
<?php
// Modified from: https://stackoverflow.com/a/24672780/2726557
function mysql_utf8_sanitizer(string $str)
{
return preg_replace('/[\x{10000}-\x{10FFFF}]/u', "\xEF\xBF\xBD", $str);
}
?>
Pick your poison and use at your own risk!
Anonymous ¶
9 years ago
If you wonder why (besides \, ' and ") NUL (ASCII 0), \n, \r, and Control-Z are escaped: it is not to prevent sql injection, but to prevent your sql logfile to get unreadable.
ASchmidt at Anamera dot net ¶
3 years ago
Caution when escaping the % and _ wildcard characters. According to an often overlooked note at the bottom of:
https://dev.mysql.com/doc/refman/5.7/en/string-literals.html#character-escape-sequences
the escape sequences \% and \_ will ONLY be interpreted as % and _, *if* they occur in a LIKE! (Same for MySQL 8.0)
In regular string literals, the escape sequences \% and \_ are treated as those two character pairs. So if those escape sequences appear in a WHERE "=" instead of a WHERE LIKE, they would NOT match a single % or _ character!
Consequently, one MUST use two "escape" functions: The real-escape-string (or equivalent) for regular string literals, and an amended escape function JUST for string literals that are intended to be used in LIKE.
Lawrence DOliveiro ¶
7 years ago
Note that the “like” operator requires an *additional* level of escaping for its special characters, *on top of* that performed by mysql_escape_string. But there is no built-in function for performing this escaping. Here is a function that does it:
function escape_sql_wild($s)
/* escapes SQL pattern wildcards in s. */
{
$result = array();
foreach(str_split($s) as $ch)
{
if ($ch == "\\" || $ch == "%" || $ch == "_")
{
$result[] = "\\";
} /*if*/
$result[] = $ch;
} /*foreach*/
return
implode("", $result);
} /*escape_sql_wild*/
