php SQL 注入如何工作以及如何防范它

How does SQL-injection work and how do I protect against it

提问by Johan

Possible Duplicate:
What is SQL injection?

可能的重复：
什么是 SQL 注入？

I see a lot of php code floating around on stackoverflow and (too) little escaping of strings.

我看到很多 php 代码漂浮在 stackoverflow 上，并且（太）很少转义字符串。

Can anyone

任何人都可以

1. Explain what SQL injection is;
2. Explain what it can do to your server, data and code;
3. Give an example how to perform an SQL-injection
4. Give php sample code how to protect against SQL-injection

1. 解释什么是 SQL 注入；
2. 解释它可以对你的服务器、数据和代码做什么；
3. 举例说明如何执行 SQL 注入
4. 给出php示例代码如何防止SQL注入

回答by shmeeps

An SQL injection is a maliciously formed SQL query used to "confuse" an SQL database into giving something it shouldn't. For instance, consider the following query

SQL 注入是一种恶意形成的 SQL 查询，用于“混淆”SQL 数据库以提供不应该提供的信息。例如，考虑以下查询

"SELECT * FROM `users` WHERE `username` = '$name'";

In a normal case, this will work. If we submit 'Hyman' to this, it will return all users named Hyman. However, if a user enters, say "' OR 1=1", the resulting query would be

在正常情况下，这将起作用。如果我们向此提交“Hyman”，它将返回所有名为 Hyman 的用户。但是，如果用户输入，说“' OR 1=1”，结果查询将是

"SELECT * FROM `users` WHERE `username` = '' OR 1=1";

Since 1 always equals 1, and the combinating clause is OR, this will return true on every row, which will in turn display EVERY row to the malicious user. Using this technique, someone can view your entire database. Also consider if someone submits something like "'; DROP TABLE users";--, which results in

由于 1 总是等于 1，并且组合子句是 OR，这将在每一行上返回 true，这将依次向恶意用户显示每一行。使用这种技术，有人可以查看您的整个数据库。还要考虑是否有人提交了类似 "'; DROP TABLE users";-- 的内容，这会导致

"SELECT * FROM `users` WHERE `username` = ''; DROP TABLE `users`";--";

Which is two queries, one which will do nothing, the second which will delete the ENTIRE users database, resulting in the loss of your data.

这是两个查询，一个什么都不做，第二个将删除整个用户数据库，导致您的数据丢失。

The best method to prevent SQL injections is to use prepared statements. With these, you send a query to the SQL database that says something like

防止 SQL 注入的最佳方法是使用准备好的语句。使用这些，您可以向 SQL 数据库发送一个查询，内容类似于

"SELECT * FROM `users` WHERE `username` = '?'";

This lets the database know the format of the query (WHERE username equals some value), so there is no confusion when given a plain text query. Then the database knows to expect one value, and where to put it. Then you pass that value to the database which it can use to search. This is also better as the database can optimize the query for faster searching.

这让数据库知道查询的格式（WHERE username 等于某个值），因此在给出纯文本查询时不会混淆。然后数据库知道期望一个值，以及把它放在哪里。然后将该值传递给可用于搜索的数据库。这也更好，因为数据库可以优化查询以加快搜索速度。

Read up on prepared statements, which will explain this in more detail.

阅读准备好的语句，它将更详细地解释这一点。

回答by uncaught_exceptions

I cannot resist posting this.

我无法抗拒发布这个。

1- Sql Injection is explained better in one cartoon, than most other documents.

1-与大多数其他文档相比，在一部卡通片中对Sql 注入的解释更好。

2- Mostly it does not do much to the server, but only to the underlying data. Consequence include delete, insert , select records, drop, create tables. (based on permissions etc..)

2- 大多数情况下，它对服务器没有多大作用，而只是对底层数据。结果包括删除、插入、选择记录、删除、创建表。（基于权限等。）

3- Examples.

3-例子。

4- Sorry I do not know PHP. But as long as you can abstract your DB layer from your View, you should be fine.

4- 对不起，我不懂 PHP。但是只要你能从你的视图中抽象出你的数据库层，你应该没问题。

回答by Shoe

I cannot resist aswell.

我也无法抗拒。

SQL Injection is "a code injection technique that exploits a security vulnerability occurring in the database layer of an application". In other words it's SQL code injectedin as user input inside a query.

SQL 注入是“一种利用发生在应用程序数据库层的安全漏洞的代码注入技术”。换句话说，它是作为用户输入在查询中注入的 SQL 代码。

SQL Injections can manipulate data (delete, update, add ecc...) and corrupt or delete tables of the database. I'm not aware of SQL Injections manipulating scripts though.

SQL 注入可以操作数据（删除、更新、添加 ecc...）并损坏或删除数据库的表。虽然我不知道 SQL 注入操作脚本。

Let's say in your PHP script you are expecting (as user input) a username and a password from the login form that are later used inside a query such as:

假设在您的 PHP 脚本中，您期望（作为用户输入）来自登录表单的用户名和密码，这些用户名和密码稍后将在查询中使用，例如：

SELECT Id FROM Users WHERE Name = $name AND Password = $password;

The user can insert inside $nameand as $passwordwhatever he likes (for example trough an <input>). Let's imagine he adds a name such as "1 OR 1 = 1; --", the query will now look like:

用户可以在里面插入$name和$password任何他喜欢的（例如槽的<input>）。假设他添加了一个名称，例如“ 1 OR 1 = 1; --”，查询现在将如下所示：

SELECT Id FROM Users WHERE Name = 1 OR 1 = 1; -- AND Password = $password;

and then, after the ;I could add another query or make the script think that the username and the password actually exists.

然后，在;我可以添加另一个查询或让脚本认为用户名和密码确实存在之后。

Notice that -- AND Password = $password;is a SQL comment and will therefore be ignored.

请注意，这-- AND Password = $password;是一条 SQL 注释，因此将被忽略。

If you are using PHP < 5 then you should look for mysql_real_escape_string()and use it to escape user inputs before embedding it inside a query.

如果您使用的是 PHP < 5，那么您应该mysql_real_escape_string()在将其嵌入查询之前查找并使用它来转义用户输入。

If you are using PHP5+ you should use PDOor the mysqliextension which can prevent this problem via prepared statements.

如果您使用 PHP5+，您应该使用PDO或mysqli扩展，它可以通过准备好的语句来防止这个问题。

回答by David

There's a lotof information out there (and elsewhere in here) about this subject, so do not take this answer as a complete list by any means and continue to research on your own...

有很多关于这个主题的信息（和这里的其他地方），所以无论如何不要把这个答案作为一个完整的列表，继续自己研究......

1. Explain what SQL injection is;
2. Explain what it can do to your server, data and code;
3. Give an example how to perform an SQL-injection
4. Give php sample code how to protect against SQL-injection

1. 解释什么是 SQL 注入；
2. 解释它可以对你的服务器、数据和代码做什么；
3. 举例说明如何执行 SQL 注入
4. 给出php示例代码如何防止SQL注入

1. SQL injection is where an attacker discovers that an input value supplied to your application is being sent directly to a database and realizes that they can craft that input to be a custom SQL command. It could be something as simple as entering a special character (such as %) into a text field and receiving a strange response.

2. It can do anything your database allows that command to do. For example, if your web application has DB owner permissions for the application's database then an attack can potentially drop tables or even drop the whole database. Or, with even normal application permissions, the attack can over-write data or read sensitive data (such as plain text passwords if you have those).

3. For example, if an application has a text field where you enter a username. If that field is open to SQL injection, an attacker can enter something like: MyName';DROP TABLE Users;--In this example, the attack manually finishes the query with the closing single quote and semi-colon, then adds another query, then comments out anything afterward. If not protected against this, the database may run both queries.

4. This one I don't know updated enough information, but there's lotsout there :)

1. SQL 注入是攻击者发现提供给您的应用程序的输入值被直接发送到数据库并意识到他们可以将该输入制作为自定义 SQL 命令的地方。它可以像%在文本字段中输入特殊字符（例如）并收到奇怪的响应一样简单。

2. 它可以执行您的数据库允许该命令执行的任何操作。例如，如果您的 Web 应用程序具有应用程序数据库的 DB 所有者权限，则攻击可能会删除表甚至删除整个数据库。或者，即使使用正常的应用程序权限，攻击也可以覆盖数据或读取敏感数据（例如，如果您拥有纯文本密码）。

3. 例如，如果应用程序有一个文本字段，您可以在其中输入用户名。如果该字段对 SQL 注入开放，则攻击者可以输入如下内容：MyName';DROP TABLE Users;--在此示例中，攻击手动使用结束单引号和分号完成查询，然后添加另一个查询，然后注释掉任何内容。如果没有对此进行保护，数据库可能会同时运行这两个查询。

4. 这个我不知道更新了足够的信息，但是那里有很多:)