如何防止 PHP 中的 SQL 注入？

How can I prevent SQL injection in PHP?

提问by Andrew G. Johnson

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

如果用户输入未经修改就插入到 SQL 查询中，那么应用程序就容易受到SQL 注入的攻击，如下例所示：

$unsafe_variable = $_POST['user_input']; 

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

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

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

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

What can be done to prevent this from happening?

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

回答by Theo

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.

使用准备好的语句和参数化查询。这些是独立于任何参数发送到数据库服务器并由其解析的 SQL 语句。这样攻击者就不可能注入恶意 SQL。

You basically have two options to achieve this:

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

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
   }
   

2. 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
   }
   

1. 使用PDO（对于任何受支持的数据库驱动程序）：

   $stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');
   
   $stmt->execute([ 'name' => $name ]);
   
   foreach ($stmt as $row) {
       // Do something with $row
   }
   

2. 使用MySQLi（用于 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
   }
   

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.

如果你连接到MySQL之外的数据库，有一个特定的驱动程序，第二个选项，你可以参考一下（例如，pg_prepare()和pg_execute()PostgreSQL的）。PDO 是通用选项。

---

---

Correctly setting up the connection

正确设置连接

Note that when using PDOto access a MySQL database realprepared 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:

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

$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);

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 Errorwhen something goes wrong. And it gives the developer the chance to catchany error(s) which are thrown as PDOExceptions.

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

What is mandatory, however, is the first setAttribute()line, which tells PDO to disable emulated prepared statements and use realprepared 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).

然而，第一行是强制性的，setAttribute()它告诉 PDO 禁用模拟准备好的语句并使用真正的准备好的语句。这可以确保语句和值在将其发送到 MySQL 服务器之前不被 PHP 解析（使可能的攻击者没有机会注入恶意 SQL）。

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

尽管您可以charset在构造函数的选项中设置 ，但重要的是要注意“旧”版本的 PHP（5.3.6 之前）会默默地忽略DSN 中的 charset 参数。

---

---

Explanation

解释

The SQL statement you pass to prepareis parsed and compiled by the database server. By specifying parameters (either a ?or a named parameter like :namein 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 语句prepare由数据库服务器解析和编译。通过指定参数（如上例中的?参数或命名参数:name），您可以告诉数据库引擎您要过滤的位置。然后，当您调用 时execute，准备好的语句将与您指定的参数值组合在一起。

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.

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

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 $namevariable contains 'Sarah'; DELETE FROM employeesthe result would simply be a search for the string "'Sarah'; DELETE FROM employees", and you will not end up with an empty table.

您在使用准备好的语句时发送的任何参数都将被视为字符串（尽管数据库引擎可能会进行一些优化，因此参数当然也可能以数字结尾）。在上面的示例中，如果$name变量包含'Sarah'; DELETE FROM employees结果将只是搜索 string "'Sarah'; DELETE FROM employees"，并且您最终不会得到一个空 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.

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

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

哦，既然你问了如何为插入做这件事，这里有一个例子（使用 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';
}

回答by Matt Sheppard

Deprecated Warning:This answer's sample code (like the question's sample code) uses PHP's MySQLextension, which was deprecated in PHP 5.5.0 and removed entirely in PHP 7.0.0.

Security Warning: This answer is not in line with security best practices. Escaping is inadequate to prevent SQL injection, use prepared statementsinstead. Use the strategy outlined below at your own risk. (Also, mysql_real_escape_string()was removed in PHP 7.)

弃用警告：此答案的示例代码（如问题的示例代码）使用 PHP 的MySQL扩展，该扩展在 PHP 5.5.0 中已弃用，并在 PHP 7.0.0 中完全删除。

安全警告：此答案不符合安全最佳实践。转义不足以防止 SQL 注入，请改用准备好的语句。使用下面概述的策略需要您自担风险。（另外，mysql_real_escape_string()在 PHP 7 中被删除。）

If you're using a recent version of PHP, the mysql_real_escape_stringoption outlined below will no longer be available (though mysqli::escape_stringis a modern equivalent). These days the mysql_real_escape_stringoption would only make sense for legacy code on an old version of PHP.

如果您使用的是最新版本的 PHP，则mysql_real_escape_string下面列出的选项将不再可用（尽管mysqli::escape_string是现代版本）。如今，该mysql_real_escape_string选项仅适用于旧版 PHP 上的遗留代码。

---

---

You've got two options - escaping the special characters in your unsafe_variable, or using a parameterized query. Both would protect you from SQL injection. The parameterized query is considered the better practice but will require changing to a newer MySQL extension in PHP before you can use it.

您有两个选择 - 转义 中的特殊字符unsafe_variable，或使用参数化查询。两者都可以保护您免受 SQL 注入。参数化查询被认为是更好的做法，但需要先更改为 PHP 中更新的 MySQL 扩展，然后才能使用它。

We'll cover the lower impact string escaping one first.

我们将首先介绍下冲击弦逃脱。

//Connect

$unsafe_variable = $_POST["user-input"];
$safe_variable = mysql_real_escape_string($unsafe_variable);

mysql_query("INSERT INTO table (column) VALUES ('" . $safe_variable . "')");

//Disconnect

See also, the details of the mysql_real_escape_stringfunction.

另请参阅mysql_real_escape_string函数的详细信息。

To use the parameterized query, you need to use MySQLirather than the MySQLfunctions. To rewrite your example, we would need something like the following.

要使用参数化查询，您需要使用MySQLi而不是MySQL函数。要重写您的示例，我们需要如下内容。

<?php
    $mysqli = new mysqli("server", "username", "password", "database_name");

    // TODO - Check that connection was successful.

    $unsafe_variable = $_POST["user-input"];

    $stmt = $mysqli->prepare("INSERT INTO table (column) VALUES (?)");

    // TODO check that $stmt creation succeeded

    // "s" means the database expects a string
    $stmt->bind_param("s", $unsafe_variable);

    $stmt->execute();

    $stmt->close();

    $mysqli->close();
?>

The key function you'll want to read up on there would be mysqli::prepare.

您要阅读的关键功能是mysqli::prepare.

Also, as others have suggested, you may find it useful/easier to step up a layer of abstraction with something like PDO.

此外，正如其他人所建议的那样，您可能会发现使用PDO 之类的东西来加强抽象层很有用/更容易。

Please note that the case you asked about is a fairly simple one and that more complex cases may require more complex approaches. In particular:

请注意，您询问的案例是一个相当简单的案例，更复杂的案例可能需要更复杂的方法。特别是：

• If you want to alter the structure of the SQL based on user input, parameterized queries are not going to help, and the escaping required is not covered by mysql_real_escape_string. In this kind of case, you would be better off passing the user's input through a whitelist to ensure only 'safe' values are allowed through.
• If you use integers from user input in a condition and take the mysql_real_escape_stringapproach, you will suffer from the problem described by Polynomialin the comments below. This case is trickier because integers would not be surrounded by quotes, so you could deal with by validating that the user input contains only digits.
• There are likely other cases I'm not aware of. You might find thisis a useful resource on some of the more subtle problems you can encounter.

• 如果您想根据用户输入更改 SQL 的结构，参数化查询将无济于事，并且所需的转义不在mysql_real_escape_string. 在这种情况下，最好通过白名单传递用户的输入，以确保只允许“安全”值通过。
• 如果您在条件中使用来自用户输入的整数并采用这种mysql_real_escape_string方法，您将遇到下面评论中多项式描述的问题。这种情况比较棘手，因为整数不会被引号包围，因此您可以通过验证用户输入仅包含数字来处理。
• 可能还有其他我不知道的情况。您可能会发现这是有关您可能遇到的一些更微妙问题的有用资源。

回答by Your Common Sense

Every answer here covers only part of the problem. In fact, there are fourdifferent query parts which we can add to SQL dynamically: -

这里的每个答案都只涵盖了问题的一部分。事实上，我们可以动态地将四个不同的查询部分添加到 SQL 中：-

• a string
• a number
• an identifier
• a syntax keyword

• 一个字符串
• 一个号码
• 标识符
• 语法关键字

And prepared statements cover only two of them.

准备好的声明只涵盖其中的两个。

But sometimes we have to make our query even more dynamic, adding operators or identifiers as well. So, we will need different protection techniques.

但有时我们必须使我们的查询更加动态，还要添加运算符或标识符。因此，我们将需要不同的保护技术。

In general, such a protection approach is based on whitelisting.

通常，这种保护方法基于白名单。

In this case, every dynamic parameter should be hardcoded in your script and chosen from that set. For example, to do dynamic ordering:

在这种情况下，每个动态参数都应该在您的脚本中硬编码并从该集中选择。例如，要进行动态排序：

$orders  = array("name", "price", "qty"); // Field names
$key = array_search($_GET['sort'], $orders)); // if we have such a name
$orderby = $orders[$key]; // If not, first one will be set automatically. 
$query = "SELECT * FROM `table` ORDER BY $orderby"; // Value is safe

To ease the process I wrote a whitelist helper functionthat does all the job in one line:

为了简化这个过程，我写了一个白名单辅助函数，在一行中完成所有工作：

$orderby = white_list($_GET['orderby'], "name", ["name","price","qty"], "Invalid field name");
$query  = "SELECT * FROM `table` ORDER BY `$orderby`"; // sound and safe

There is another way to secure identifiers - escaping but I rather stick to whitelisting as a more robust and explicit approach. Yet as long as you have an identifier quoted, you can escape the quote character to make it safe. For example, by default for mysql you have to double the quote character to escape it. For other other DBMS escaping rules would be different.

还有另一种保护标识符的方法 - 转义，但我宁愿坚持将白名单作为一种更强大和明确的方法。然而，只要您引用了标识符，就可以对引号字符进行转义以使其安全。例如，默认情况下，对于 mysql，您必须将引号字符加倍才能对其进行转义。对于其他其他 DBMS 转义规则会有所不同。

Still, there is an issue with SQL syntax keywords (such as AND, DESCand such), but white-listing seems the only approach in this case.

尽管如此，SQL 语法关键字（例如AND,DESC等）仍然存在问题，但在这种情况下，白名单似乎是唯一的方法。

So, a general recommendation may be phrased as

因此，一般性建议可以表述为

• Any variable that represents an SQL data literal, (or, to put it simply - an SQL string, or a number) must be added through a prepared statement. No Exceptions.
• Any other query part, such as an SQL keyword, a table or a field name, or an operator - must be filtered through a white list.

• 任何表示 SQL 数据文字的变量（或者，简单地说 - SQL 字符串或数字）都必须通过准备好的语句添加。没有例外。
• 任何其他查询部分，例如 SQL 关键字、表或字段名称或运算符 - 都必须通过白名单进行过滤。

Update

更新

Although there is a general agreement on the best practices regarding SQL injection protection, there are still many bad practices as well.And some of them too deeply rooted in the minds of PHP users. For instance, on this very page there are (although invisible to most visitors) more than 80 deleted answers- all removed by the community due to bad quality or promoting bad and outdated practices. Worse yet, some of the bad answers aren't deleted, but rather prospering.

尽管对于 SQL 注入保护的最佳实践已达成普遍共识，但仍然存在许多不良实践。其中一些在 PHP 用户的脑海中根深蒂固。例如，在这个页面上（尽管对大多数访问者不可见）有 80 多个已删除的答案- 由于质量差或宣传不良和过时的做法，所有答案都被社区删除。更糟糕的是，一些不好的答案并没有被删除，而是繁荣起来。

For example, there(1)are(2)still(3)many(4)answers(5), including the second most upvoted answersuggesting you manual string escaping - an outdated approach that is proven to be insecure.

例如，there(1) are(2) still(3) many(4) answers(5)，包括建议您手动字符串转义的第二个最受好评的答案- 一种已被证明不安全的过时方法。

Or there is a slightly better answer that suggests just another method of string formattingand even boasts it as the ultimate panacea. While of course, it is not. This method is no better than regular string formatting, yet it keeps all its drawbacks: it is applicable to strings only and, like any other manual formatting, it's essentially optional, non-obligatory measure, prone to human error of any sort.

或者有一个稍微好一点的答案，它暗示了另一种字符串格式化方法，甚至将其吹嘘为终极灵丹妙药。当然，事实并非如此。这种方法并不比常规字符串格式化好，但它保留了所有缺点：它仅适用于字符串，并且与任何其他手动格式化一样，它本质上是可选的、非强制性的措施，容易出现任何类型的人为错误。

I think that all this because of one very old superstition, supported by such authorities like OWASPor the PHP manual, which proclaims equality between whatever "escaping" and protection from SQL injections.

我认为这一切都是因为一个非常古老的迷信，得到了像OWASP或PHP 手册这样的权威的支持，它宣称任何“转义”和防止 SQL 注入的保护之间是平等的。

Regardless of what PHP manual said for ages, *_escape_stringby no means makes data safeand never has been intended to. Besides being useless for any SQL part other than string, manual escaping is wrong, because it is manual as opposite to automated.

不管 PHP 手册说了什么，*_escape_string绝不能让数据安全，也从来没有打算这样做。除了对字符串以外的任何 SQL 部分都没用之外，手动转义是错误的，因为它是手动的，而不是自动的。

And OWASP makes it even worse, stressing on escaping user inputwhich is an utter nonsense: there should be no such words in the context of injection protection. Every variable is potentially dangerous - no matter the source! Or, in other words - every variable has to be properly formatted to be put into a query - no matter the source again. It's the destination that matters. The moment a developer starts to separate the sheep from the goats (thinking whether some particular variable is "safe" or not) he/she takes his/her first step towards disaster. Not to mention that even the wording suggests bulk escaping at the entry point, resembling the very magic quotes feature - already despised, deprecated and removed.

而 OWASP 更糟，强调逃避用户输入，这是完全无稽之谈：在注入保护的上下文中不应该有这样的词。每个变量都有潜在危险 - 无论来源如何！或者，换句话说 - 每个变量都必须正确格式化才能放入查询中 - 无论来源如何。重要的是目的地。当开发人员开始将绵羊与山羊分开时（考虑某个特定变量是否“安全”），他/她就迈出了走向灾难的第一步。更不用说即使措辞暗示在入口点批量转义，类似于非常神奇的引号功能 - 已经被鄙视，弃用和删除。

So, unlike whatever "escaping", prepared statements isthe measure that indeed protects from SQL injection (when applicable).

因此，与任何“转义”不同，准备好的语句是确实可以防止 SQL 注入（适用时）的措施。

回答by Kibbee

I'd recommend using PDO(PHP Data Objects) to run parameterized SQL queries.

我建议使用PDO（PHP 数据对象）来运行参数化 SQL 查询。

Not only does this protect against SQL injection, but it also speeds up queries.

这不仅可以防止 SQL 注入，还可以加快查询速度。

And by using PDO rather than mysql_, mysqli_, and pgsql_functions, you make your application a little more abstracted from the database, in the rare occurrence that you have to switch database providers.

并且通过使用 PDO 而不是mysql_, mysqli_, 和pgsql_函数，您可以使您的应用程序从数据库中更加抽象一些，在极少数情况下您必须切换数据库提供程序。

回答by Imran

Use PDOand prepared queries.

使用PDO和准备查询。

($connis a PDOobject)

（$conn是一个PDO对象）

$stmt = $conn->prepare("INSERT INTO tbl VALUES(:id, :name)");
$stmt->bindValue(':id', $id);
$stmt->bindValue(':name', $name);
$stmt->execute();

回答by Zaffy

As you can see, people suggest you use prepared statements at the most. It's not wrong, but when your query is executed just onceper process, there would be a slight performance penalty.

如您所见，人们建议您最多使用准备好的语句。这并没有错，但是当您的查询每个进程只执行一次时，性能会有所下降。

I was facing this issue, but I think I solved it in verysophisticated way - the way hackers use to avoid using quotes. I used this in conjunction with emulated prepared statements. I use it to prevent allkinds of possible SQL injection attacks.

我遇到了这个问题，但我认为我以非常复杂的方式解决了它- 黑客用来避免使用引号的方式。我将它与模拟的准备语句结合使用。我用它来预防所有种类的可能的SQL注入攻击。

My approach:

我的做法：

• If you expect input to be integer make sure it's reallyinteger. In a variable-type language like PHP it is this veryimportant. You can use for example this very simple but powerful solution: sprintf("SELECT 1,2,3 FROM table WHERE 4 = %u", $input);

• If you expect anything else from integer hex it. If you hex it, you will perfectly escape all input. In C/C++ there's a function called mysql_hex_string(), in PHP you can use bin2hex().

  Don't worry about that the escaped string will have a 2x size of its original length because even if you use mysql_real_escape_string, PHP has to allocate same capacity ((2*input_length)+1), which is the same.

• This hex method is often used when you transfer binary data, but I see no reason why not use it on all data to prevent SQL injection attacks. Note that you have to prepend data with 0xor use the MySQL function UNHEXinstead.

• 如果您希望输入是整数，请确保它确实是整数。在像 PHP 这样的可变类型语言中，这一点非常重要。例如，您可以使用这个非常简单但功能强大的解决方案：sprintf("SELECT 1,2,3 FROM table WHERE 4 = %u", $input);

• 如果你期望从整数十六进制它。如果你使用十六进制，你将完美地逃避所有输入。在 C/C++ 中有一个名为 的函数mysql_hex_string()，在 PHP 中你可以使用bin2hex().

  不要担心转义字符串的大小是其原始长度的 2 倍，因为即使您使用mysql_real_escape_string，PHP 也必须分配相同的容量((2*input_length)+1)，这是相同的。

• 传输二进制数据时经常使用这种十六进制方法，但我认为没有理由不对所有数据使用它来防止 SQL 注入攻击。请注意，您必须在数据之前添加0x或使用 MySQL 函数UNHEX。

So, for example, the query:

因此，例如，查询：

SELECT password FROM users WHERE name = 'root'

Will become:

会变成：

SELECT password FROM users WHERE name = 0x726f6f74

or

或者

SELECT password FROM users WHERE name = UNHEX('726f6f74')

Hex is the perfect escape. No way to inject.

十六进制是完美的逃避。没办法注入。

Difference between UNHEX function and 0x prefix

UNHEX 函数和 0x 前缀的区别

There was some discussion in comments, so I finally want to make it clear. These two approaches are very similar, but they are a little different in some ways:

评论里有一些讨论，所以我最后想说清楚。这两种方法非常相似，但它们在某些方面略有不同：

The ** 0x** prefix can only be used for data columns such as char, varchar, text, block, binary, etc.
Also, its use is a little complicated if you are about to insert an empty string. You'll have to entirely replace it with '', or you'll get an error.

** 0x** 前缀只能用于char、varchar、text、block、binary 等数据列。
此外，如果您要插入一个空字符串，它的使用有点复杂。您必须将其完全替换为''，否则会出现错误。

UNHEX()works on anycolumn; you do not have to worry about the empty string.

UNHEX()适用于任何列；您不必担心空字符串。

---

---

Hex methods are often used as attacks

十六进制方法经常被用作攻击

Note that this hex method is often used as an SQL injection attack where integers are just like strings and escaped just with mysql_real_escape_string. Then you can avoid the use of quotes.

请注意，此十六进制方法通常用作 SQL 注入攻击，其中整数就像字符串一样，仅使用mysql_real_escape_string. 然后你可以避免使用引号。

For example, if you just do something like this:

例如，如果你只是做这样的事情：

"SELECT title FROM article WHERE id = " . mysql_real_escape_string($_GET["id"])

an attack can inject you very easily. Consider the following injected code returned from your script:

攻击可以很容易地注入你。考虑从您的脚本返回的以下注入代码：

SELECT ... WHERE id = -1 union all select table_name from information_schema.tables

SELECT ... WHERE id = -1 union all select table_name from information_schema.tables

and now just extract table structure:

现在只需提取表结构：

SELECT ... WHERE id = -1 union all select column_name from information_schema.column where table_name = 0x61727469636c65

SELECT ... WHERE id = -1 union all select column_name from information_schema.column where table_name = 0x61727469636c65

And then just select whatever data ones want. Isn't it cool?

然后只需选择所需的任何数据。是不是很酷？

But if the coder of an injectable site would hex it, no injection would be possible because the query would look like this: SELECT ... WHERE id = UNHEX('2d312075...3635')

但是，如果可注入站点的编码器对其进行十六进制处理，则不可能进行注入，因为查询将如下所示： SELECT ... WHERE id = UNHEX('2d312075...3635')

回答by rahularyansharma

Deprecated Warning:This answer's sample code (like the question's sample code) uses PHP's MySQLextension, which was deprecated in PHP 5.5.0 and removed entirely in PHP 7.0.0.

Security Warning: This answer is not in line with security best practices. Escaping is inadequate to prevent SQL injection, use prepared statementsinstead. Use the strategy outlined below at your own risk. (Also, mysql_real_escape_string()was removed in PHP 7.)

IMPORTANT

The best way to prevent SQL Injection is to use Prepared Statementsinstead of escaping, as the accepted answerdemonstrates.

There are libraries such as Aura.Sqland EasyDBthat allow developers to use prepared statements easier. To learn more about why prepared statements are better at stopping SQL injection, refer to this mysql_real_escape_string()bypassand recently fixed Unicode SQL Injection vulnerabilities in WordPress.

弃用警告：此答案的示例代码（如问题的示例代码）使用 PHP 的MySQL扩展，该扩展在 PHP 5.5.0 中已弃用，并在 PHP 7.0.0 中完全删除。

安全警告：此答案不符合安全最佳实践。转义不足以防止 SQL 注入，请改用准备好的语句。使用下面概述的策略需要您自担风险。（另外，mysql_real_escape_string()在 PHP 7 中被删除。）

重要的

防止 SQL 注入的最佳方法是使用Prepared Statements而不是 escaping，如已接受的答案所示。

有Aura.Sql和EasyDB等库可以让开发人员更轻松地使用准备好的语句。要详细了解为什么准备好的语句更能阻止 SQL 注入，请参阅此mysql_real_escape_string()绕过和最近修复的 WordPress 中的 Unicode SQL 注入漏洞。

Injection prevention - mysql_real_escape_string()

注入预防 - mysql_real_escape_string()

PHP has a specially-made function to prevent these attacks. All you need to do is use the mouthful of a function, mysql_real_escape_string.

PHP 有一个特制的功能来防止这些攻击。您需要做的就是使用满口的函数mysql_real_escape_string.

mysql_real_escape_stringtakes a string that is going to be used in a MySQL query and return the same string with all SQL injection attempts safely escaped. Basically, it will replace those troublesome quotes(') a user might enter with a MySQL-safe substitute, an escaped quote \'.

mysql_real_escape_string接受一个将在 MySQL 查询中使用的字符串，并返回与所有 SQL 注入尝试安全转义的相同字符串。基本上，它将用一个 MySQL 安全的替代品，一个转义的引号 \' 替换用户可能输入的那些麻烦的引号 (')。

NOTE:you must be connected to the database to use this function!

注意：您必须连接到数据库才能使用此功能！

// Connect to MySQL

// 连接到 MySQL

$name_bad = "' OR 1'"; 

$name_bad = mysql_real_escape_string($name_bad);

$query_bad = "SELECT * FROM customers WHERE username = '$name_bad'";
echo "Escaped Bad Injection: <br />" . $query_bad . "<br />";


$name_evil = "'; DELETE FROM customers WHERE 1 or username = '"; 

$name_evil = mysql_real_escape_string($name_evil);

$query_evil = "SELECT * FROM customers WHERE username = '$name_evil'";
echo "Escaped Evil Injection: <br />" . $query_evil;

You can find more details in MySQL - SQL Injection Prevention.

您可以在MySQL-SQL 注入预防中找到更多详细信息。

回答by Tanerax

You could do something basic like this:

你可以做一些基本的事情：

$safe_variable = mysqli_real_escape_string($_POST["user-input"], $dbConnection);
mysqli_query($dbConnection, "INSERT INTO table (column) VALUES ('" . $safe_variable . "')");

This won't solve every problem, but it's a very good stepping stone. I left out obvious items such as checking the variable's existence, format (numbers, letters, etc.).

这不会解决所有问题，但它是一个很好的垫脚石。我遗漏了一些明显的项目，例如检查变量的存在、格式（数字、字母等）。

回答by Rob

Whatever you do end up using, make sure that you check your input hasn't already been mangled by magic_quotesor some other well-meaning rubbish, and if necessary, run it through stripslashesor whatever to sanitize it.

无论你最终使用什么，确保你检查你的输入没有被magic_quotes或其他一些善意的垃圾破坏，如果有必要，运行它stripslashes或以任何方式对其进行消毒。

回答by Cedric

Deprecated Warning:This answer's sample code (like the question's sample code) uses PHP's MySQLextension, which was deprecated in PHP 5.5.0 and removed entirely in PHP 7.0.0.

Security Warning: This answer is not in line with security best practices. Escaping is inadequate to prevent SQL injection, use prepared statementsinstead. Use the strategy outlined below at your own risk. (Also, mysql_real_escape_string()was removed in PHP 7.)

弃用警告：此答案的示例代码（如问题的示例代码）使用 PHP 的MySQL扩展，该扩展在 PHP 5.5.0 中已弃用，并在 PHP 7.0.0 中完全删除。

安全警告：此答案不符合安全最佳实践。转义不足以防止 SQL 注入，请改用准备好的语句。使用下面概述的策略需要您自担风险。（另外，mysql_real_escape_string()在 PHP 7 中被删除。）

Parameterized query AND input validation is the way to go. There are many scenarios under which SQL injection may occur, even though mysql_real_escape_string()has been used.

参数化查询和输入验证是要走的路。有很多场景可能会发生 SQL 注入，即使mysql_real_escape_string()已经使用过。

Those examples are vulnerable to SQL injection:

这些示例容易受到 SQL 注入的攻击：

$offset = isset($_GET['o']) ? $_GET['o'] : 0;
$offset = mysql_real_escape_string($offset);
RunQuery("SELECT userid, username FROM sql_injection_test LIMIT $offset, 10");

or

或者

$order = isset($_GET['o']) ? $_GET['o'] : 'userid';
$order = mysql_real_escape_string($order);
RunQuery("SELECT userid, username FROM sql_injection_test ORDER BY `$order`");

In both cases, you can't use 'to protect the encapsulation.

在这两种情况下，您都不能使用'来保护封装。

Source: The Unexpected SQL Injection (When Escaping Is Not Enough)

来源：意外的 SQL 注入（当转义还不够时）