Seeking detailed explanation of mysql_real_escape_string() injection prevention

mysql_real_escape_string() is regarded as a good alternative to addslashes() and mysql_escape_string(), which can solve wide byte and injection problems, but the official description is unclear:

mysql_real_escape_string — Escape special characters in strings used in SQL statements, taking into account the current character set of the connection

I really don’t understand this sentence. Considering the current character set of the connection, what does it mean? Can an expert explain it in detail? Thank you!

Reference:
http://php.net/manual/zh/function.mysql-real-escape-string.php
http://www.cnblogs.com/suihui/archive/2012/09/20/2694751. html
http://www.neatstudio.com/show-963-1.shtml

Additional questions: @佢佇俜
1. The added content will not be transferred to mysql, so if ' or 1=1;-- s is injected, the injected content here will not be transferred to mysql in the end. Has it been executed?
Reference: https://segmentfault.com/q/1010000005994443

2.And take into account the current character set of the connectionWhat does it mean? Change the character set of the current connection?

Reply content:

mysql_real_escape_string() is regarded as a good alternative to addslashes() and mysql_escape_string(), which can solve wide byte and injection problems, but the official description is unclear:

mysql_real_escape_string — Escape special characters in strings used in SQL statements, taking into account the current character set of the connection

I really don’t understand this sentence. Considering the current character set of the connection, what does it mean? Can an expert explain it in detail? Thank you!

Reference:
http://php.net/manual/zh/function.mysql-real-escape-string.php
http://www.cnblogs.com/suihui/archive/2012/09/20/2694751. html
http://www.neatstudio.com/show-963-1.shtml

Additional questions: @佢佇俜
1. The added content will not be transferred to mysql, so if ' or 1=1;-- s is injected, the injected content here will not be transferred to mysql in the end. Has it been executed?
Reference: https://segmentfault.com/q/1010000005994443

2.And take into account the current character set of the connectionWhat does it mean? Change the character set of the current connection?

This is about mysql gbk double-byte injection

For example, user login, assuming your sql statement:
$sql = "select * from user where user_name='$username' and password='$password'";

1 If there is an injection point and there is no escaping. Pass parameter username=' or 1=1;-- s then the sql statement at this time is

$sql = "select * from user where user_name='' or 1=1;-- s ' and password='$password'";

Because -- is a comment character, there is no need to judge whether it has been successfully bypassed later

2 If there is an injection point and special character escapes are used. Then the sql statement at this time is

$sql = "select * from user where user_name='' or 1=1;-- s ' and password='$password'";

Cannot be bypassed

3 At this time, the problem came. An awesome hacker discovered that there is wide byte injection in gbk encoding. At this time, pass username=%df%27 or 1=1;--

The sql statement executed at this time becomes:

$sql = "select * from user where user_name='luck' or 1=1;-- s ' and password='$password'"; Successfully bypassed

How to solve this problem

mysql_real_escape_string — Escape special characters in strings used in SQL statements, taking into account the current character set of the connection

The escaping mechanism of the

mysql_real_escape_string method for SQL statements changes with the change of the character set of the current database connection. For the same SQL statement, the escaped results may not be the same under different character sets.

Important:

mysql_real_escape_string method belongs to the mysql extension, which has been marked obsolete since PHP version 5.5.0 and removed since PHP version 7. Please use mysqli or PDO extension for database operations. Please try to use utf8 or utf8mb4 (better) as the database character set.