Problem analysis of mysql query and single quotation marks for inserting data

Regarding the problem of single quotes encountered when MySQL queries or inserts data, the three most used functions are mysql_real_escape_string, addslashes and mysql_escape_string to deal with related problems.

This article mainly introduces mysql_real_escape_string to escape form data submitted by users. And introduce the usage differences of three functions with similar functions: addslashes and mysql_escape_string.

The difference between Mysql query with quotes and without quotes When the database field ID is an integer select ID from table where ID=1 and select ID from table where ID='1' Both SQLs are acceptable, but the first SQL does not require implicit conversion and is slightly faster than the second SQL

Insert a single-quoted string into the mysql database. No error is reported, but the statement execution fails. The reason is that the single quotes must be escaped. You can use the functions: mysql_real_escape_string and addslashes functions; In terms of sql anti-injection, the problem with addslashes is that hackers can use 0xbf27 instead of single quotes, and addslashes only changes 0xbf27 to 0xbf5c27, which becomes a valid multi-byte character, and 0xbf5c is still regarded as a single quote, so addslashes could not be intercepted successfully.

Of course, addslashes is not useless. It is used for processing single-byte strings. For multi-byte characters, use mysql_real_escape_string.

Example of get_magic_quotes_gpc in php manual:

<?php
if (!get_magic_quotes_gpc()) {
$lastname = addslashes($_POST["lastname"]);
} else {
$lastname = $_POST['lastname'];
}
?>

When magic_quotes_gpc is already open, check $_POST['lastname'].

The difference between the two functions mysql_real_escape_string and mysql_escape_string: mysql_real_escape_string can only be used when (PHP 4 >= 4.3.0, PHP 5). Otherwise, you can only use mysql_escape_string. The difference between the two is: mysql_real_escape_string takes into account the connection's current character set, while mysql_escape_string does not.

Summary: addslashes() is forcibly added; mysql_real_escape_string() will determine the character set, but there are requirements for the PHP version; mysql_escape_string does not take into account the current character set of the connection.