如何防止 PHP 中的 SQL 注入? [英] How can I prevent SQL injection in PHP?

问题描述

如果用户输入未经修改就插入到 SQL 查询中，那么应用程序很容易受到SQL 注入的攻击，就像在下面的例子:

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example:

$unsafe_variable = $_POST['user_input']; 

mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')");

那是因为用户可以输入类似value');DROP TABLE table;--，查询变成:

That's because the user can input something like value'); DROP TABLE table;--, and the query becomes:

INSERT INTO `table` (`column`) VALUES('value'); DROP TABLE table;--')

可以做些什么来防止这种情况发生?

What can be done to prevent this from happening?

推荐答案

避免 SQL 注入攻击的正确方法，无论您使用哪种数据库，都是将数据与 SQL 分离，以便数据保持数据并且永远不会被 SQL 解析器解释为命令.可以使用格式正确的数据部分创建 SQL 语句，但如果您不完全了解细节，则应始终使用准备好的语句和参数化查询.这些是与任何参数分开发送到数据库服务器并由其解析的 SQL 语句.这样攻击者就不可能注入恶意 SQL.

The correct way to avoid SQL injection attacks, no matter which database you use, is to separate the data from SQL, so that data stays data and will never be interpreted as commands by the SQL parser. It is possible to create SQL statement with correctly formatted data parts, but if you don't fully understand the details, you should always use prepared statements and parameterized queries. These are SQL statements that are sent to and parsed by the database server separately from any parameters. This way it is impossible for an attacker to inject malicious SQL.

您基本上有两种选择来实现这一目标:

You basically have two options to achieve this:

1. 使用 PDO(对于任何支持的数据库驱动程序):

1. Using PDO (for any supported database driver):

 $stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');

 $stmt->execute([ 'name' => $name ]);

 foreach ($stmt as $row) {
     // Do something with $row
 }

使用 MySQLi(对于MySQL):

Using MySQLi (for MySQL):

 $stmt = $dbConnection->prepare('SELECT * FROM employees WHERE name = ?');
 $stmt->bind_param('s', $name); // 's' specifies the variable type => 'string'

 $stmt->execute();

 $result = $stmt->get_result();
 while ($row = $result->fetch_assoc()) {
     // Do something with $row
 }

如果您要连接到 MySQL 以外的数据库，则可以参考特定于驱动程序的第二个选项(例如，pg_prepare() 和 pg_execute() 用于 PostgreSQL).PDO 是通用选项.

If you're connecting to a database other than MySQL, there is a driver-specific second option that you can refer to (for example, pg_prepare() and pg_execute() for PostgreSQL). PDO is the universal option.

请注意，当使用 PDO 访问 MySQL 数据库时，真实预准备语句默认情况下不使用.要解决此问题，您必须禁用预准备语句的模拟.使用 PDO 创建连接的示例是:

Note that when using PDO to access a MySQL database real prepared statements are not used by default. To fix this you have to disable the emulation of prepared statements. An example of creating a connection using PDO is:

$dbConnection = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8', 'user', 'password');

$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

在上面的例子中，错误模式不是绝对必要的，但建议添加它.这样脚本就不会在出现问题时以 Fatal Error 停止.它让开发人员有机会catch任何thrown为PDOExceptions的错误.

In the above example the error mode isn't strictly necessary, but it is advised to add it. This way the script will not stop with a Fatal Error when something goes wrong. And it gives the developer the chance to catch any error(s) which are thrown as PDOExceptions.

什么是强制，但是，第一行setAttribute()，它告诉PDO禁用模拟准备好的语句并使用real准备好的声明.这可确保语句和值在将其发送到 MySQL 服务器之前不会被 PHP 解析(使可能的攻击者没有机会注入恶意 SQL).

What is mandatory, however, is the first setAttribute() line, which tells PDO to disable emulated prepared statements and use real prepared statements. This makes sure the statement and the values aren't parsed by PHP before sending it to the MySQL server (giving a possible attacker no chance to inject malicious SQL).

尽管您可以在构造函数的选项中设置 charset，但重要的是要注意 PHP 的旧"版本(5.3.6 之前)默默地忽略了 DSN 中的字符集参数.

Although you can set the charset in the options of the constructor, it's important to note that 'older' versions of PHP (before 5.3.6) silently ignored the charset parameter in the DSN.

您传递给 prepare 的 SQL 语句由数据库服务器解析和编译.通过指定参数(? 或像上面示例中的 :name 这样的命名参数)，您可以告诉数据库引擎您要过滤的位置.然后当您调用 execute 时，准备好的语句与您指定的参数值组合在一起.

The SQL statement you pass to prepare is parsed and compiled by the database server. By specifying parameters (either a ? or a named parameter like :name in the example above) you tell the database engine where you want to filter on. Then when you call execute, the prepared statement is combined with the parameter values you specify.

这里重要的是参数值与编译语句组合，而不是SQL字符串.SQL 注入的工作原理是在脚本创建要发送到数据库的 SQL 时诱使脚本包含恶意字符串.因此，通过将实际 SQL 与参数分开发送，您可以限制以您不想要的结果结束的风险.

The important thing here is that the parameter values are combined with the compiled statement, not an SQL string. SQL injection works by tricking the script into including malicious strings when it creates SQL to send to the database. So by sending the actual SQL separately from the parameters, you limit the risk of ending up with something you didn't intend.

您在使用准备好的语句时发送的任何参数都将被视为字符串(尽管数据库引擎可能会进行一些优化，因此参数当然也可能以数字结尾).在上面的例子中，如果 $name 变量包含 'Sarah';从员工中删除 结果只是搜索字符串 "'Sarah';从员工中删除"，您将不会得到空表.

Any parameters you send when using a prepared statement will just be treated as strings (although the database engine may do some optimization so parameters may end up as numbers too, of course). In the example above, if the $name variable contains 'Sarah'; DELETE FROM employees the result would simply be a search for the string "'Sarah'; DELETE FROM employees", and you will not end up with an empty table.

使用准备好的语句的另一个好处是，如果你在同一个会话中多次执行同一个语句，它只会被解析和编译一次，从而给你一些速度提升.

Another benefit of using prepared statements is that if you execute the same statement many times in the same session it will only be parsed and compiled once, giving you some speed gains.

哦，既然你问了如何为插入做这件事，这里有一个例子(使用 PDO):

Oh, and since you asked about how to do it for an insert, here's an example (using PDO):

$preparedStatement = $db->prepare('INSERT INTO table (column) VALUES (:column)');

$preparedStatement->execute([ 'column' => $unsafeValue ]);

---

准备好的语句可以用于动态查询吗?

虽然您仍然可以对查询参数使用准备好的语句，但动态查询本身的结构不能被参数化，某些查询特征也不能被参数化.

---

Can prepared statements be used for dynamic queries?

While you can still use prepared statements for the query parameters, the structure of the dynamic query itself cannot be parametrized and certain query features cannot be parametrized.

对于这些特定场景，最好的办法是使用限制可能值的白名单过滤器.

For these specific scenarios, the best thing to do is use a whitelist filter that restricts the possible values.

// Value whitelist
// $dir can only be 'DESC', otherwise it will be 'ASC'
if (empty($dir) || $dir !== 'DESC') {
   $dir = 'ASC';
}

这篇关于如何防止 PHP 中的 SQL 注入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！