PHP SQL injection attack prevention methods and injection analysis

1. The magic_quotes_gpc option in the php tutorial configuration file php.ini is not turned on and is set to off 2. The developer did not check and escape the data type

But in fact, the second point is the most important. I think that checking the data type entered by the user and submitting the correct data type to the mysql tutorial should be the most basic quality of a web programmer. But in reality, many novice web developers often forget this, leaving the backdoor wide open.

Why is the second point the most important? Because without the second guarantee, the magic_quotes_gpc option, whether it is on or off, may cause SQL injection attacks. Let’s take a look at the technical implementation:

1. Injection attack when magic_quotes_gpc = off

magic_quotes_gpc = off is a very unsafe option in php. The new version of php has changed the default value to on. But there are still quite a few servers with the option off. After all, no matter how antique the server is, there are still people using it.

When magic_quotes_gpc = on　, it will automatically add all '(single quotes), "(double signs), (backslashes), and whitespace characters in the submitted variables in front. The following is Official description of php:

view sourceprint?

magic_quotes_gpc boolean

sets the magic_quotes state for gpc (get/post/cookie) operations. when magic_quotes are on, all ' (single-quote), " (double quote), (backslash) and nul's are escaped with a backslash automatically

If there is no escape, that is, in the off case, it will give attackers an opportunity to take advantage. Take the following test script as an example:

1 2if ( isset($_post["f_login"] ) )
3{
4 // Tutorial on connecting to database...
5 // ...The code is abbreviated...
6
7 // Check if the user exists
8 $t_struname = $_post["f_uname"];
9 $t_strpwd = $_post["f_pwd"];
10 $t_strsql = "select * from tbl_users where username='$t_struname' and password = '$t_strpwd' limit 0,1";
11
12 if ( $t_hres = mysql_query($t_strsql) )
13 {
14 // Processing after successful query. Omitted...
15 }
16}
17?>
18
19sample test
20
21

In this script, when the user enters a normal username and password, assuming the values are zhang3 and abc123 respectively, the submitted sql statement is as follows:

1 select * from tbl_users
2 where username='zhang3' and password = 'abc123' limit 0,1

If the attacker enters: zhang3' or 1=1 # in the username field and abc123 in the password field, the submitted sql statement becomes as follows:

1 select * from tbl_users
2 where username='zhang3' or 1=1 #' and password = 'abc123' limit 0,1

Since # is a comment character in mysql, the statement after # will not be executed. To implement this line of statement, it becomes:

1 select * from tbl_users
2 where username='zhang3' or 1=1

This allows an attacker to bypass authentication. If the attacker knows the database structure, then it builds a union select, which is even more dangerous:

Suppose you enter in username: zhang3 ' or 1 =1 union select cola, colb,cold from tbl_b #

Enter password: abc123,

Then the submitted sql statement becomes:

1 select * from tbl_users
2 where username='zhang3 '
3 or 1 =1 union select cola, colb,cold from tbl_b #' and password = 'abc123' limit 0,1

This is quite dangerous. If the agic_quotes_gpc option is on and the quotes are escaped, the attack statement constructed by the attacker above will become like this, thus failing to achieve its purpose:

1 select * from tbl_users
2 where username='zhang3' or 1=1 #'
3 and password = 'abc123'
4 limit 0,1
5
6 select * from tbl_users
7 where username='zhang3 ' or 1 =1 union select cola, colb,cold from tbl_b #'
8 and password = 'abc123' limit 0,1

2. Injection attack when magic_quotes_gpc = on

When magic_quotes_gpc = on, attackers cannot perform sql injection on character fields. That doesn't mean it's safe. At this time, SQL injection can be performed through numeric fields.

In the latest version of mysql 5.x, the input of data types has been stricted, and automatic type conversion has been turned off by default. Numeric fields cannot be character types marked with quotation marks. In other words, assuming uid is numeric, in previous mysql versions, such a statement is legal:

1 insert into tbl_user set uid="1";
2 select * from tbl_user where uid="1";

In the latest mysql 5.x, the above statement is not legal and must be written like this:

1 insert into tbl_user set uid=1;
2 select * from tbl_user where uid=1;

This I think is correct. Because as a developer, submitting correct data types that comply with the rules to the database is the most basic requirement.

So how do attackers attack when magic_quotes_gpc = on? It's very simple, just perform sql injection on numeric fields. Take the following php script as an example:

1 2 if ( isset($_post["f_login"] ) )
3 {
4 // Connect to database...
5 // ...The code is abbreviated...
6
7 // Check if the user exists
8 $t_struid = $_post["f_uid"];
9 $t_strpwd = $_post["f_pwd"];
10 $t_strsql = "select * from tbl_users where uid=$t_struid and password = '$t_strpwd' limit 0,1";
11 if ( $t_hres = mysql_query($t_strsql) )
12 {
13 // Processing after successful query. Omitted...
14 }
15
16}
17 ?>
18 sample test
19
20

The above script requires the user to enter userid and password to log in. A normal statement, the user inputs 1001 and abc123, and the submitted sql statement is as follows:

select * from tbl_users where userid=1001 and password = 'abc123' limit 0,1

If the attacker enters: 1001 or 1 =1 # at userid, the injected sql statement is as follows:

select * from tbl_users where userid=1001 or 1 =1 # and password = 'abc123' limit 0,1

The attacker achieved his goal.

3. How to prevent php sql injection attacks

How to prevent php sql injection attacks? I think the most important point is to check and escape data types. A few summarized rules are as follows:

The display_errors option in php.ini should be set to display_errors = off. In this way, after an error occurs in the php script, the error will not be output on the web page to prevent attackers from analyzing useful information.

When calling mysql functions such as mysql_query, @ should be added in front, that is, @mysql_query(...), so that mysql errors will not be output. The same is true to prevent attackers from analyzing useful information. In addition, some programmers are used to outputting errors and sql statements when mysql_query errors when developing, for example: 1 $t_strsql = "select a from b....";
2 if ( mysql_query($t_strsql) )
3 {
4 // Correct processing
5}
6 else
7 {
8 echo "Error! sql statement: $t_strsql rn error message".mysql_query();
9 exit;
10}

This approach is quite dangerous and stupid. If you must do this, it is best to set a global variable or define a macro in the website configuration file and set the debug flag:

1 //全局配置文件中：
2 define("debug_mode",0);    // 1: debug mode; 0: release mode
3
4 //调用脚本中：
5 $t_strsql = "select a from b....";
6 if ( mysql_query($t_strsql) )
7 {
8   // 正确的处理
9 }
10 else
11 {
12   if (debug_mode)
13     echo "错误! sql 语句：$t_strsql rn错误信息".mysql_query();
14   exit;
15 }

对提交的 sql 语句，进行转义和类型检查。

四. 我写的一个安全参数获取函数

为了防止用户的错误数据和 php + mysql 注入，我写了一个函数 papi_getsafeparam()，用来获取安全的参数值：

1 define("xh_param_int",0);
2 define("xh_param_txt",1);
3 function papi_getsafeparam($pi_strname, $pi_def = "", $pi_itype = xh_param_txt)
4 {
5   if ( isset($_get[$pi_strname]) )
6     $t_val = trim($_get[$pi_strname]);
7   else if ( isset($_post[$pi_strname]))
8     $t_val = trim($_post[$pi_strname]);
9   else
10     return $pi_def;
11
12   // int
13   if ( xh_param_int == $pi_itype)
14   {
15     if (is_numeric($t_val))
16       return $t_val;
17     else
18       return $pi_def;
19   }
20
21   // string
22   $t_val = str_replace("&", "&",$t_val);
23   $t_val = str_replace("<", "<",$t_val);
24 $t_val = str_replace(">", ">",$t_val);
25   if ( get_magic_quotes_gpc() )
26   {
27     $t_val = str_replace(""", """,$t_val);
28     $t_val = str_replace("''", "'",$t_val);
29   }
30   else
31   {
32     $t_val = str_replace(""", """,$t_val);
33     $t_val = str_replace("'", "'",$t_val);
34   }
35   return $t_val;
36 }

在这个函数中，有三个参数：

$pi_strname: 变量名
$pi_def: 默认值
$pi_itype: 数据类型。取值为 xh_param_int, xh_param_txt, 分别表示数值型和文本型。

如果请求是数值型，那么调用 is_numeric() 判断是否为数值。如果不是，则返回程序指定的默认值。

简单起见，对于文本串，我将用户输入的所有危险字符（包括html代码），全部转义。由于 php 函数 addslashes()存在漏洞，我用 str_replace()直接替换。get_magic_quotes_gpc() 函数是 php 的函数，用来判断 magic_quotes_gpc 选项是否打开。

刚才第二节的示例，代码可以这样调用：

1 2 if ( isset($_post["f_login"] ) )
3 {
4 // 连接数据库...
5 // ...代码略...
6
7 // 检查用户是否存在
8 $t_struid = papi_getsafeparam("f_uid", 0, xh_param_int);
9 $t_strpwd = papi_getsafeparam("f_pwd", "", xh_param_txt);
10 $t_strsql = "select * from tbl_users where uid=$t_struid and password = '$t_strpwd' limit 0,1";
11 if ( $t_hres = mysql_query($t_strsql) )
12 {
13 // 成功查询之后的处理. 略...
14 }
15 }
16 ?>