PHP：mysql_real_escape_string 是否足以清理用户输入？

PHP: Is mysql_real_escape_string sufficient for cleaning user input?

提问by Thomas

Is mysql_real_escape_stringsufficient for cleaning user input in most situations?

是mysql_real_escape_string足够的清洁用户输入在大多数情况下？

::EDIT::

：：编辑：：

I'm thinking mostly in terms of preventing SQL injection but I ultimately want to know if I can trust user data after I apply mysql_real_escape_string or if I should take extra measures to clean the data before I pass it around the application and databases.

我主要考虑防止 SQL 注入，但我最终想知道在我应用 mysql_real_escape_string 之后我是否可以信任用户数据，或者我是否应该在将数据传递到应用程序和数据库之前采取额外措施来清理数据。

I see where cleaning for HTML chars is important but I wouldn't consider it necessary for trusting user input.

我看到清理 HTML 字符很重要，但我认为没有必要信任用户输入。

T

吨

采纳答案by Sarfraz

mysql_real_escape_stringis not sufficient in all situations but it is definitely very good friend. The bettersolution is using Prepared Statements

mysql_real_escape_string在所有情况下都不够，但它绝对是非常好的朋友。该更好的解决方案是使用预处理语句

//example from http://php.net/manual/en/pdo.prepared-statements.php

$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)");
$stmt->bindParam(1, $name);
$stmt->bindParam(2, $value);

// insert one row
$name = 'one';
$value = 1;
$stmt->execute();

Also, not to forget HTMLPurifierthat can be used to discard any invalid/suspicious characters.

另外，不要忘记可用于丢弃任何无效/可疑字符的HTMLPurifier。

...........

…………

Edit:Based on the comments below, I need to post this link (I should have done before sorry for creating confusion)

编辑：根据下面的评论，我需要发布此链接（我应该在抱歉造成混淆之前完成）

mysql_real_escape_string() versus Prepared Statements

mysql_real_escape_string() 与准备好的语句

Quoting:

引用：

mysql_real_escape_string() prone to the same kind of issues affecting addslashes().

mysql_real_escape_string() 容易出现影响addslashes() 的同类问题。

Chris Shiflett(Security Expert)

Chris Shiflett（安全专家）

回答by rook

The answer to your question is No. mysql_real_escape_string() is not suitable for all user input and mysql_real_escape_string() does not stop all sql injection. addslashes() is another popular function to use in php, and it has the same problem.

您的问题的答案是否定的。 mysql_real_escape_string() 并不适合所有用户输入，并且 mysql_real_escape_string() 不会停止所有 sql 注入。addlashes() 是另一个在 php 中使用的流行函数，它也有同样的问题。

vulnerable code:

易受攻击的代码：

mysql_query("select * from user where id=".mysql_real_escape_string($_GET[id]));

poc exploit:

poc漏洞利用：

http://localhost/sql_test.php?id=1 or sleep(500)

The patch is to use quote marks around id:

补丁是在 id 周围使用引号：

mysql_query("select * from user where id='".mysql_real_escape_string($_GET[id])."'");

Really the best approach is to use parametrized queries which a number of people ahve pointed out. Pdo works well, adodb is another popular library for php.

确实最好的方法是使用许多人指出的参数化查询。Pdo 运行良好，adodb 是另一个流行的 php 库。

If you do use mysql_real_escape_string is should only be used for sql injection, and nothing else. Vulnerabilities are highly dependent on how the data is being used. One should apply security measures on a function by function basis. And yes, XSS is a VERY SERIOUS PROBLEM. Not filtering for html is a serious mistake that a hacker will use to pw3n you. Please read the xss faq.

如果你确实使用 mysql_real_escape_string 应该只用于 sql 注入，而不是别的。漏洞高度依赖于数据的使用方式。人们应该逐个功能地应用安全措施。是的，XSS 是一个非常严重的问题。不过滤 html 是一个严重的错误，黑客会用它来窃取你。请阅读xss 常见问题。

回答by jasonbar

To the database, yes. You'll want to consider adequately escaping / encoding data for output as well.

到数据库，是的。您还需要考虑为输出充分转义/编码数据。

You should also consider validatingthe input against what you expect it to be.

您还应该考虑根据您的预期验证输入。

Have you considered using prepared statements? PHP offers numerous ways to interact with your database. Most of which are better than the mysql_* functions.

您是否考虑过使用准备好的语句？PHP 提供了多种与数据库交互的方式。其中大部分都比 mysql_* 函数好。

PDO, MDB2and the MySQL Improvedshould get you started.

PDO、MDB2和MySQL 改进应该让你开始。

回答by Matchu

What situations?

什么情况？

For SQL queries, it's great. (Prepared statements are better - I vote PDOfor this - but the function escapes just fine.) For HTML and the like, it is not the tool for the job - try a generic htmlspecialcharsor a more precise tool like HTML Purifier.

对于 SQL 查询，它很棒。（准备好的语句更好 - 我为此投了PDO- 但函数转义就好了。）对于 HTML 等，它不是工作的工具 - 尝试使用通用htmlspecialchars或更精确的工具，如HTML Purifier。

To address the edit: The only other layer you could add is data valdation, e.g. confirm that if you are putting an integer into the database, and you are expecting a positive integer, you return an error to the user on attempting to put in a negative integer. As far as data integrity is concerned, mysql_real_escape_stringis the best you have for escaping (though, again, prepared statements are a cleaner system that avoids escaping entirely).

解决编辑问题：您可以添加的唯一其他层是数据验证，例如确认如果您将一个整数放入数据库，并且您期望一个正整数，则在尝试放入一个时向用户返回一个错误负整数。就数据完整性而言，这mysql_real_escape_string是最好的转义（不过，再次声明，准备好的语句是一个更干净的系统，可以完全避免转义）。

回答by Asaph

mysql_real_escape_string()is useful for preventing SQL injection attacks only. It won't help you with preventing cross site scripting attacks. For that, you should use htmlspecialchars()just before outputting data that was originally collected from user input.

mysql_real_escape_string()仅用于防止 SQL 注入攻击。它不会帮助您防止跨站点脚本攻击。为此，您应该htmlspecialchars()在输出最初从用户输入中收集的数据之前使用。

回答by Mike Trpcic

There are different types of "cleaning".

有不同类型的“清洁”。

mysql_real_escape_stringis sufficient for database data, but will still be evaluated by the browser upon display if it is HTML.

mysql_real_escape_string足以用于数据库数据，但如果是 HTML，浏览器仍会在显示时对其进行评估。

To remove HTML from user input, you can use strip_tags.

要从用户输入中删除 HTML，您可以使用strip_tags。

I would suggest you look into using PDOinstead of regular MySQL stuff, as it supports prepared statements right out of the box, and those handle the escaping of invalid data for you.

我建议您考虑使用PDO而不是常规 MySQL 的东西，因为它支持开箱即用的准备好的语句，并且这些语句会为您处理无效数据的转义。

回答by Marius

There are two ways, one is to use prepared statements (as mentioned in other answers), but that will slow down your app, because you now have to send two requests to the Database, instead of one. If you can live with the reduced performance, then go for it; Prepared Statements makes your code prettier and easier to deal with.

有两种方法，一种是使用准备好的语句（如其他答案中所述），但这会减慢您的应用程序的速度，因为您现在必须向数据库发送两个请求，而不是一个。如果你能忍受性能下降，那就去做吧；准备好的语句使您的代码更漂亮，更容易处理。

If you chose to use mysql_real_escape_string, then make sure that you escape all the strings that are untrusted. An (mysql_real_escape_string) escaped string is SQL Injection secure. If you don't escape all the strings, then you are not secure. You should really combine mysql_real_escape_string with input validation; checking that a variable you expect to hold a number really is a number and within the expected range. Remember, never trust the user.

如果您选择使用 mysql_real_escape_string，请确保转义所有不受信任的字符串。(mysql_real_escape_string) 转义字符串是 SQL Injection secure。如果您不转义所有字符串，那么您就不安全。您真的应该将 mysql_real_escape_string 与输入验证结合起来；检查您希望保存数字的变量是否确实是一个数字并且在预期范围内。请记住，永远不要相信用户。

回答by crafter

You can try both, as in

您可以同时尝试，例如

function clean_input($instr) {

     // Note that PHP performs addslashes() on GET/POST data.
     // Avoid double escaping by checking the setting before doing this.
    if(get_magic_quotes_gpc()) {
        $str = stripslashes($instr);
    }
    return mysql_real_escape_string(strip_tags(trim($instr)));
}

回答by Marek Karbarz

The best way to go would be to use Prepared Statements

最好的方法是使用准备好的语句

回答by Thomas

I thought I'd add that PHP 5.2+ has input filter functions that can sanitize user input in a variety of ways.

我想我会补充说 PHP 5.2+ 具有输入过滤器功能，可以以多种方式清理用户输入。

Here's the manual entryas well as a blog post[by Matt Butcher] about why they're great.

这是手册条目以及[由马特·布彻 (Matt Butcher) 撰写的关于为什么它们很棒的博客文章。