Mysql 简明教程

MySQL - SQL Injection

MySQL 中的 SQL 注入是一种有害的方法，攻击者在其中将有害的 SQL 代码插入或“注入”到数据库查询中。这可以通过用户输入（例如表单、URL 参数或 Cookie）完成。攻击者利用软件的弱点从数据库中窃取信息。

The SQL Injection in MySQL is a harmful approach where an attacker inserts or "injects" harmful SQL code into a database query. This can be done through user inputs such as forms, URL parameters, or cookies. The attacker takes advantage of weaknesses in the software to steal information from the database.

How SQL Injection Works

想象一下，您有一个带有登录页面的 Web 应用程序。当用户输入他们的用户名和密码时，应用程序会根据 MySQL 数据库检查这些凭据。SQL 查询可能如下所示 −

Imagine you have a web application with a login page. When a user enters their username and password, the application checks these credentials against a MySQL database. The SQL query might look like as given below −

SELECT * FROM users
WHERE username = 'user' AND password = 'password';

在一个安全的应用程序中，“用户”和“密码”将是用户输入的实际值。然而，在 SQL 注入攻击中，攻击者可以操纵输入字段来注入恶意 SQL 代码。

In a secure application, the 'user' and 'password' would be the actual values entered by the user. However, in an SQL Injection attack, an attacker can manipulate the input fields to inject malicious SQL code.

例如，他们可能会输入以下内容作为用户名 −

For example, they might enter the following as the username −

' OR '1' = '1

现在，SQL 查询变为 −

Now, the SQL query becomes −

SELECT * FROM users
WHERE username = '' OR '1' = '1' AND password = 'password';

由于“1”总是等于“1”，因此此条件始终为真，攻击者可以未经授权访问应用程序。通过这种方式，他们诱骗应用程序在没有有效密码的情况下授予访问权限。

Because '1' always equals '1', this condition is always true, and the attacker gains unauthorized access to the application. In this way, they trick the application into granting access without a valid password.

Preventing SQL Injection

为了防止 SQL 注入，在使用 PERL 和 PHP 等脚本语言时，正确处理转义字符非常重要。在使用 PHP 和 MySQL 时，可以使用 mysql_real_escape_string() 函数转义在 MySQL 中具有特殊含义的输入字符。以下是如何执行此操作的示例 −

To prevent SQL injection, it is important to handle escape characters properly when using scripting languages like PERL and PHP. When working with PHP and MySQL, you can use the mysql_real_escape_string() function to escape input characters that have special meaning in MySQL. Following is an example of how to do this −

if (get_magic_quotes_gpc()) {
   $name = stripslashes($name);
}
// escape input characters
$name = mysql_real_escape_string($name);

// Perform the MySQL query with the escaped 'name'
mysqli_query("SELECT * FROM CUSTOMERS WHERE name='{$name}'");

The LIKE Quandary

现在，让我们解决 LIKE 子句的问题。在处理可能包含“%”和“_”字符的用户提供的数据时，创建一个自定义转义机制非常重要，以便将它们视为字面意思。您可以通过将“mysql_real_escape_string()”函数与“addcslashes()”函数结合使用来实现此目的，后者允许您指定要转义的字符范围。以下是执行此操作的方法示例 −

Now, let us address the issue with the LIKE clause. When dealing with user-provided data that may include '%' and '_' characters, it is important to create a custom escaping mechanism to treat them as literals. You can achieve this by combining "mysql_real_escape_string()" function with "addcslashes()" function, which allows you to specify a character range to escape. Following is an example of how you can do it −

// Escape and convert '%' and '_' in the user-provided string
$sub = addcslashes(mysql_real_escape_string("%str"), "%_");

// $sub will be equal to \%str\_

// Use the escaped string in the LIKE query
mysqli_query("SELECT * FROM messages
   WHERE subject LIKE '{$sub}%'");

通过这种方式，您可以确保用户输入中的“%”和“_”字符在 SQL 查询中被视为字面字符，从而防止 SQL 注入并维护数据库操作的完整性。

In this way, you ensure that the '%' and '_' characters in the user input are treated as literal characters in the SQL query, preventing SQL injection and maintaining the integrity of your database operations.