About Mysql queries with single quotes and inserting single quoted strings
- 2020-05-15 02:21:16
- OfStack
Mysql queries with and without quotation marks
When the database field ID is an integer
select ID from table where ID=1
and
select ID from table where ID='1'
Both sql are fine, but the first sql does not have an implicit conversion and is slightly faster than the second sql
Today, when inserting a single quoted string into the mysql database, nothing was reported and the statement failed to execute. Later, I learned that to escape single quotes, I could use the following functions: mysql_real_escape_string and addslashes;
The following is an introduction: before inserting data into the database, it is necessary to escape before inserting data into the database.
The difference between addslashes and mysql_real_escape_string is well explained in this paper. Although many PHP coder in China still rely on addslashes to prevent SQL injection, I still recommend that you strengthen the inspection of SQL injection in Chinese. The problem with addslashes is that hackers can use 0xbf27 instead of single quotation marks, while addslashes only changes 0xbf27 to 0xbf5c27, which becomes a valid multi-byte character. Among them, 0xbf5c will still be regarded as single quotation marks, so addslashes cannot successfully intercept.
Of course, addslashes is not useless, it is used for single-byte string processing, multi-byte characters or mysql_real_escape_string.
In addition, for the example of get_magic_quotes_gpc in php manual:
It is best to check $_POST['lastname'] once magic_quotes_gpc is already open.
Again, the difference between mysql_real_escape_string and mysql_escape_string functions:
mysql_real_escape_string must be in (PHP 4) > = 4.3.0, PHP 5). Otherwise, only mysql_escape_string can be used. The difference between the two is:
mysql_real_escape_string takes into account the current character set of the connection, while mysql_escape_string does not.
Conclusion 1:
addslashes() is forced;
mysql_real_escape_string() determines the character set, but requires the PHP version;
mysql_escape_string does not consider the current character set of the connection.
When the database field ID is an integer
select ID from table where ID=1
and
select ID from table where ID='1'
Both sql are fine, but the first sql does not have an implicit conversion and is slightly faster than the second sql
Today, when inserting a single quoted string into the mysql database, nothing was reported and the statement failed to execute. Later, I learned that to escape single quotes, I could use the following functions: mysql_real_escape_string and addslashes;
The following is an introduction: before inserting data into the database, it is necessary to escape before inserting data into the database.
The difference between addslashes and mysql_real_escape_string is well explained in this paper. Although many PHP coder in China still rely on addslashes to prevent SQL injection, I still recommend that you strengthen the inspection of SQL injection in Chinese. The problem with addslashes is that hackers can use 0xbf27 instead of single quotation marks, while addslashes only changes 0xbf27 to 0xbf5c27, which becomes a valid multi-byte character. Among them, 0xbf5c will still be regarded as single quotation marks, so addslashes cannot successfully intercept.
Of course, addslashes is not useless, it is used for single-byte string processing, multi-byte characters or mysql_real_escape_string.
In addition, for the example of get_magic_quotes_gpc in php manual:
<?php
if (!get_magic_quotes_gpc()) {
$lastname = addslashes($_POST["lastname"]);
} else {
$lastname = $_POST['lastname'];
}
?>
It is best to check $_POST['lastname'] once magic_quotes_gpc is already open.
Again, the difference between mysql_real_escape_string and mysql_escape_string functions:
mysql_real_escape_string must be in (PHP 4) > = 4.3.0, PHP 5). Otherwise, only mysql_escape_string can be used. The difference between the two is:
mysql_real_escape_string takes into account the current character set of the connection, while mysql_escape_string does not.
Conclusion 1:
addslashes() is forced;
mysql_real_escape_string() determines the character set, but requires the PHP version;
mysql_escape_string does not consider the current character set of the connection.