Sql 简明教程

SQL - Injection

如果您通过网页获取用户输入并将其插入到 SQL 数据库中，那么很可能已经为自己留下了广泛的安全漏洞，称为 SQL Injection 。本章将指导您如何帮助防止发生这种情况，以及在服务器端脚本中（例如 PERL 脚本）保护脚本和 SQL 语句。

If you take a user input through a webpage and insert it into an SQL database, there is a chance that you have left yourself wide open for a security issue known as the SQL Injection. This chapter will teach you how to help prevent this from happening and help you secure your scripts and SQL statements in your server side scripts such as a PERL Script.

SQL Injection

SQL 注入是一种通过执行恶意查询来利用数据库漏洞的安全攻击。这将允许攻击者访问敏感数据，篡改数据，还可以永久删除数据。

SQL Injection is a type of security attack that exploits a vulnerability in a database by executing malicious queries. This will allow attackers to access sensitive data, tamper it and also delete it permanently.

通常当您要求用户输入姓名时，会出现注入，而用户不提供姓名，而是提供将在数据库中不知不觉运行的 SQL 语句。切勿信任用户提供的数据，只有在验证后才处理此数据；根据规则，这可以通过 Pattern Matching 完成。

Injection usually occurs when you ask a user for input, like their name and instead of a name they give you a SQL statement that you will unknowingly run on your database. Never trust user provided data, process this data only after validation; as a rule, this is done by Pattern Matching.

Example

在下面的示例中， name 仅限于字母数字字符和下划线，并且长度在 8 到 20 个字符之间（可以根据需要修改这些规则）。

In the example below, the name is restricted to the alphanumerical characters plus underscore and to a length between 8 and 20 characters (you can modify these rules as needed).

if (preg_match("/^\w{8,20}$/", $_GET['username'], $matches)) {
   $result = mysqli_query("SELECT * FROM CUSTOMERS
      WHERE name = $matches[0]");
} else {
   echo "user name not accepted";
}

为了说明问题，请考虑以下摘录 −

To demonstrate the problem, consider this excerpt −

// supposed input
$name = "Qadir'; DELETE FROM CUSTOMERS;";
mysqli_query("SELECT * FROM CUSTOMSRS WHERE name='{$name}'");

函数调用应该从 CUSTOMERS 表中检索一条记录，其中 name 列与用户指定的 name 匹配。在正常情况下， $name 仅包含字母数字字符和空格。但是，在这里，通过向 $name 追加一个全新查询，对数据库的调用变成了灾难；注入的 DELETE 查询将删除 CUSTOMERS 表中的所有记录。

The function call is supposed to retrieve a record from the CUSTOMERS table where the name column matches the name specified by the user. Under normal circumstances, $name would only contain alphanumeric characters and perhaps spaces. But here, by appending an entirely new query to $name, the call to the database turns into disaster; the injected DELETE query removes all records from the CUSTOMERS table.

幸运的是，如果您使用 MySQL， mysqli_query() 函数不允许查询堆积或在单个函数调用中执行多个 SQL 查询。如果您尝试堆积查询，则调用会失败。

Fortunately, if you use MySQL, the mysqli_query() function does not permit query stacking or executing multiple SQL queries in a single function call. If you try to stack queries, the call fails.

但是，其他 PHP 数据库扩展，例如 SQLite 和 PostgreSQL 会愉快地执行堆积的查询，执行在一个字符串中提供的查询并创建严重的安全问题。

However, other PHP database extensions, such as SQLite and PostgreSQL happily perform stacked queries, executing all the queries provided in one string and creating a serious security problem.

Preventing SQL Injection

您可以在 PERL 和 PHP 等脚本语言中巧妙地处理所有转义字符。PHP 的 MySQL 扩展提供了 mysql_real_escape_string() 函数来转义对 MySQL 来说特殊的输入字符。

You can handle all escape characters smartly in scripting languages like PERL and PHP. The MySQL extension for PHP provides the function mysql_real_escape_string() to escape input characters that are special to MySQL.

if (get_magic_quotes_gpc()) {
   $name = stripslashes($name);
}
$name = mysql_real_escape_string($name);
mysqli_query("SELECT * FROM CUSTOMERS WHERE name='{$name}'");

The LIKE Quandary

为了解决 LIKE 难题，自定义转义机制必须将用户提供的 '%' 和 '_' 字符转换为文本。使用 addcslashes() ，此函数允许您指定要转义的字符范围。

To address the LIKE quandary, a custom escaping mechanism must convert user-supplied '%' and '_' characters to literals. Use addcslashes(), a function that lets you specify a character range to escape.

$sub = addcslashes(mysql_real_escape_string("%str"), "%_");
// $sub == \%str\_
mysqli_query("SELECT * FROM messages
   WHERE subject LIKE '{$sub}%'");