PHP Security-SQL Injection

SQL injection

SQL Injection is one of the most common vulnerabilities in PHP applications. In fact, it is amazing that a developer needs to make two mistakes at the same time to trigger a SQL injection vulnerability. One is not filtering the input data (filtering the input), and the other is not filtering the data sent to the database. Escape (escape output). These two important steps are indispensable and require special attention to reduce program errors.

For attackers, conducting SQL injection attacks requires thinking and experimentation. It is very necessary to conduct well-founded reasoning about the database solution (assuming of course that the attacker cannot see your source program and database solution). Consider the following simple login Form:

CODE:

<form action="/login.php" method="POST">
<p>Username: <input type="text"
name="username" /></p>
<p>Password: <input type="password"
name="password" /></p>
<p><input type="submit" value="Log In"
/></p>
</form>

Figure 3-1 shows the display of this form in the browser.

As an attacker, he would start by speculating on a query to verify the username and password. By looking at the source files, he can start guessing your habits.

Figure 3-1. Display of the login form in the browser

Naming convention. It is usually assumed that the field names in your form are the same as the field names in the data table. Of course, ensuring they are different is not necessarily a reliable security measure.

For the first guess, you will generally use the query in the following example:

CODE:

<?php
 
$password_hash = md5($_POST[&#39;password&#39;]);
 
$sql = "SELECT count(*)
      FROM   users
      WHERE  username = &#39;{$_POST[&#39;username&#39;]}&#39;
      AND    password = &#39;$password_hash&#39;";
 
?>

Using the MD5 value of the user password used to be a common practice, but now it is not particularly safe. . Recent research shows that the MD5 algorithm is flawed, and the large number of MD5 databases reduces the difficulty of MD5 reverse cracking. Please visit http://www.php.cn/ Check out the demo.

Translation annotation: This is the original text. Research by Wang Xiaoyun, a professor at Shandong University, shows that MD5 "collision" can be quickly found, that is, two different files and strings that can produce the same MD5 value. MD5 is an information digest algorithm, not an encryption algorithm, so reverse cracking is out of the question. However, according to this result, in the above special case, it is dangerous to use md5 directly.

The best protection method is to append a string you define to the password, for example:

CODE:

<?php
 
$salt = &#39;SHIFLETT&#39;;
$password_hash = md5($salt . md5($_POST[&#39;password&#39;]
. $salt));
 
?>

## Of course, attackers may not get it right the first time, and they often need to do some experimentation. A better way to experiment is to enter single quotes as the username, because this may expose some important information. Many developers call the function mysql_error() to report the error when an error occurs during Mysql statement execution. See the example below:

CODE:

<?php
 
mysql_query($sql) or exit(mysql_error());
 
?>

While this method is useful in development, it can expose important information to an attacker. If the attacker uses single quotes as the username and mypass as the password, the query statement will become:

CODE:

<?php
 
$sql = "SELECT *
      FROM   users
      WHERE  username = &#39;&#39;&#39;
      AND    password =
&#39;a029d0df84eb5549c641e04a9ef389e5&#39;";
 
?>

When the statement is sent to MySQL, the system will display the following error message:

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 'WHERE username = ''' AND
password = 'a029d0df84eb55

## Without any effort, the attacker already knows the two field names (username and password) and the order in which they appear in the query. In addition, the attacker also knows that the data is not filtered correctly (the program does not prompt illegal user names) and escaped (a database error occurs), and the format of the entire WHERE condition is also exposed, so that the attacker can try to manipulate There are records that match the query.

At this point, the attacker has many options. One is to try to fill in a special username so that the query can get a match regardless of whether the username and password match:

myuser' or 'foo' = 'foo' --

 

  假定将mypass作为密码，整个查询就会变成：

CODE:

 

<?php
 
$sql = "SELECT *
      FROM   users
      WHERE  username = &#39;myuser&#39; or &#39;foo&#39; = &#39;foo&#39;
--
      AND    password =
&#39;a029d0df84eb5549c641e04a9ef389e5&#39;";
 
?>

由于中间插入了一个SQL注释标记，所以查询语句会在此中断。这就允许了一个攻击者在不知道任何合法用户名和密码的情况下登录。

 

  如果知道合法的用户名，攻击者就可以该用户(如chris)身份登录：

 

chris' --

 

  只要chris是合法的用户名，攻击者就可以控制该帐号。原因是查询变成了下面的样子：

CODE:

 

<?php
$sql = "SELECT *
      FROM   users
      WHERE  username = &#39;chris&#39; --
      AND    password =
&#39;a029d0df84eb5549c641e04a9ef389e5&#39;";
?>

幸运的是，SQL注入是很容易避免的。正如第一章所提及的，你必须坚持过滤输入和转义输出。

  虽然两个步骤都不能省略，但只要实现其中的一个就能消除大多数的SQL注入风险。如果你只是过滤输入而没有转义输出，你很可能会遇到数据库错误（合法的数据也可能影响SQL查询的正确格式），但这也不可靠，合法的数据还可能改变SQL语句的行为。另一方面，如果你转义了输出，而没有过滤输入，就能保证数据不会影响SQL语句的格式，同时也防止了多种常见SQL注入攻击的方法。

  当然，还是要坚持同时使用这两个步骤。过滤输入的方式完全取决于输入数据的类型（见第一章的示例），但转义用于向数据库发送的输出数据只要使用同一个函数即可。对于MySQL用户，可以使用函数mysql_real_escape_string( )：

CODE:

 

<?php
 
$clean = array();
$mysql = array();
 
$clean[&#39;last_name&#39;] = "O&#39;Reilly";
$mysql[&#39;last_name&#39;] =
mysql_real_escape_string($clean[&#39;last_name&#39;]);
 
$sql = "INSERT
      INTO   user (last_name)
      VALUES (&#39;{$mysql[&#39;last_name&#39;]}&#39;)";
 
?>

尽量使用为你的数据库设计的转义函数。如果没有，使用函数addslashes( )是最终的比较好的方法。

 

  当所有用于建立一个SQL语句的数据被正确过滤和转义时，实际上也就避免了SQL注入的风险。

CODE:

 

如果你正在使用支持参数化查询语句和占位符的数据库操作类（如PEAR::DB, PDO等），你就会多得到一层保护。见下面的使用PEAR::DB的例子：

 

CODE:

 

<?php
$sql = &#39;INSERT
      INTO   user (last_name)
      VALUES (?)&#39;;
$dbh->query($sql,
array($clean[&#39;last_name&#39;]));
?>

CODE:

 

由于在上例中数据不能直接影响查询语句的格式，SQL注入的风险就降低了。PEAR::DB会自动根据你的数据库的要求进行转义，所以你只需要过滤输出即可。

如果你正在使用参数化查询语句，输入的内容就只会作为数据来处理。这样就没有必要进行转义了，尽管你可能认为这是必要的一步（如果你希望坚持转义输出习惯的话）。实际上，这时是否转义基本上不会产生影响，因为这时没有特殊字符需要转换。在防止SQL注入这一点上，参数化查询语句为你的程序提供了强大的保护。

 

  译注：关于SQL注入，不得不说的是现在大多虚拟主机都会把magic_quotes_gpc选项打开，在这种情况下所有的客户端GET和POST的数据都会自动进行addslashes处理，所以此时对字符串值的SQL注入是不可行的，但要防止对数字值的SQL注入，如用intval()等函数进行处理。但如果你编写的是通用软件，则需要读取服务器的magic_quotes_gpc后进行相应处理。

 以上就是PHP安全-SQL 注入的内容，更多相关内容请关注PHP中文网（www.php.cn）！