如何防止在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.

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以外的数据库，则可以引用第二个特定于驱动程序的选项(例如，对于PostgreSQL，为pg_prepare()和pg_execute()). 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数据库 real 时，默认情况下不使用准备好的语句.要解决此问题，您必须禁用对准备好的语句的仿真.使用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任何throw n为PDOException s的错误.

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之前)

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'; DELETE FROM employees，则结果将只是搜索字符串"'Sarah'; DELETE FROM employees"，而不会以

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屋！