Creates a legal SQL string literal that can be used in an SQL statement.
The given string is escaped in the same way as
mysqli::real_escape_string(), taking into account
the current character set of the connection, and the result is wrapped in
single quotes. The returned value must therefore be inserted into the
query without adding any further quotes around it.
Unlike mysqli::real_escape_string(), the result is
also safe when the NO_BACKSLASH_ESCAPES SQL mode is
enabled on the connection.
Caution
Security: the default character set
The character set must be set either at the server level, or with
the API function mysqli_set_charset() for it to affect
mysqli_quote_string(). See the concepts section
on character sets for
more information.
Note:
Quoting is only suitable for string literals. It does not make
identifiers, such as table or column names, safe. Whenever possible, use
prepared
statements instead of building queries manually.
Characters encoded are NUL (ASCII 0),
\n, \r, \,
', ", and
CTRL+Z.
If the NO_BACKSLASH_ESCAPES SQL mode is enabled on
the connection, only ' is encoded, by doubling it.
Return Values
Returns the escaped string enclosed in single quotes.
Examples
Example #1 mysqli::quote_string() example
Object-oriented style
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
$city = "'s-Hertogenbosch";
/* this query with quoted $city will work */
$query = sprintf("SELECT CountryCode FROM City WHERE name=%s", $mysqli->quote_string($city));
$result = $mysqli->query($query);
printf("Select returned %d rows.\n", $result->num_rows);
/* this query will fail, because we didn't quote and escape $city */
$query = sprintf("SELECT CountryCode FROM City WHERE name='%s'", $city);
$result = $mysqli->query($query);
Procedural style
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = mysqli_connect("localhost", "my_user", "my_password", "world");
$city = "'s-Hertogenbosch";
/* this query with quoted $city will work */
$query = sprintf("SELECT CountryCode FROM City WHERE name=%s", mysqli_quote_string($mysqli, $city));
$result = mysqli_query($mysqli, $query);
printf("Select returned %d rows.\n", mysqli_num_rows($result));
/* this query will fail, because we didn't quote and escape $city */
$query = sprintf("SELECT CountryCode FROM City WHERE name='%s'", $city);
$result = mysqli_query($mysqli, $query);
The above examples will output
something similar to:
Select returned 1 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 's-Hertogenbosch'' at line 1 in...
See Also
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
mysqli::query
mysqli