php 替代 mysql_real_escape_string 而不连接到数据库

Alternative to mysql_real_escape_string without connecting to DB

提问by Viet

I'd like to have a function behaving as mysql_real_escape_string without connecting to database as at times I need to do dry testing without DB connection. mysql_escape_string is deprecated and therefore is undesirable. Some of my findings:

我想要一个函数作为 mysql_real_escape_string 而不连接到数据库，因为有时我需要在没有数据库连接的情况下进行干测试。mysql_escape_string 已被弃用，因此是不可取的。我的一些发现：

http://www.gamedev.net/community/forums/topic.asp?topic_id=448909

http://www.gamedev.net/community/forums/topic.asp?topic_id=448909

http://w3schools.invisionzone.com/index.php?showtopic=20064

http://w3schools.invisionzone.com/index.php?showtopic=20064

采纳答案by too much php

It is impossible to safely escape a string without a DB connection. mysql_real_escape_string()and prepared statements need a connection to the database so that they can escape the string using the appropriate character set - otherwise SQL injection attacks are still possible using multi-byte characters.

没有数据库连接就不可能安全地转义字符串。mysql_real_escape_string()和准备好的语句需要连接到数据库，以便它们可以使用适当的字符集对字符串进行转义 - 否则仍然可能使用多字节字符进行 SQL 注入攻击。

If you are only testing, then you may as well use mysql_escape_string(), it's not 100% guaranteed against SQL injection attacks, but it's impossible to build anything safer without a DB connection.

如果您只是在测试，那么您也可以使用mysql_escape_string()，它不能 100% 保证免受 SQL 注入攻击，但是如果没有数据库连接，就不可能构建任何更安全的东西。

回答by zombat

Well, according to the mysql_real_escape_stringfunction reference page: "mysql_real_escape_string() calls MySQL's library function mysql_real_escape_string, which escapes the following characters: \x00, \n, \r, \, ', " and \x1a."

好吧，根据mysql_real_escape_string函数参考页面：“mysql_real_escape_string()调用MySQL的库函数mysql_real_escape_string，它转义了以下字符：\x00、\n、\r、\、'、”和\x1a。”

With that in mind, then the function given in the second link you posted should do exactly what you need:

考虑到这一点，那么您发布的第二个链接中给出的功能应该完全符合您的需要：

function mres($value)
{
    $search = array("\",  "\x00", "\n",  "\r",  "'",  '"', "\x1a");
    $replace = array("\\","\0","\n", "\r", "\'", '\"', "\Z");

    return str_replace($search, $replace, $value);
}

回答by too much php

In direct opposition to my other answer, this following function is probably safe, even with multi-byte characters.

与我的其他答案直接相反，即使使用多字节字符，以下函数也可能是安全的。

// replace any non-ascii character with its hex code.
function escape($value) {
    $return = '';
    for($i = 0; $i < strlen($value); ++$i) {
        $char = $value[$i];
        $ord = ord($char);
        if($char !== "'" && $char !== "\"" && $char !== '\' && $ord >= 32 && $ord <= 126)
            $return .= $char;
        else
            $return .= '\x' . dechex($ord);
    }
    return $return;
}

I'm hoping someone more knowledgeable than myself can tell me why the code above won't work ...

我希望比我更博学的人能告诉我为什么上面的代码不起作用......

回答by Viet

From further research, I've found:

通过进一步的研究，我发现：

http://dev.mysql.com/doc/refman/5.1/en/news-5-1-11.html

http://dev.mysql.com/doc/refman/5.1/en/news-5-1-11.html

Security Fix:

安全修复：

An SQL-injection security hole has been found in multi-byte encoding processing. The bug was in the server, incorrectly parsing the string escaped with the mysql_real_escape_string() C API function.

在多字节编码处理中发现了一个 SQL 注入安全漏洞。该错误在服务器中，错误地解析了使用 mysql_real_escape_string() C API 函数转义的字符串。

This vulnerability was discovered and reported by Josh Berkus and Tom Lane as part of the inter-project security collaboration of the OSDB consortium. For more information about SQL injection, please see the following text.

该漏洞是由 Josh Berkus 和 Tom Lane 作为 OSDB 联盟项目间安全协作的一部分发现并报告的。关于 SQL 注入的更多信息，请看下面的文字。

Discussion. An SQL injection security hole has been found in multi-byte encoding processing. An SQL injection security hole can include a situation whereby when a user supplied data to be inserted into a database, the user might inject SQL statements into the data that the server will execute. With regards to this vulnerability, when character set-unaware escaping is used (for example, addslashes() in PHP), it is possible to bypass the escaping in some multi-byte character sets (for example, SJIS, BIG5 and GBK). As a result, a function such as addslashes() is not able to prevent SQL-injection attacks. It is impossible to fix this on the server side. The best solution is for applications to use character set-aware escaping offered by a function such mysql_real_escape_string().

讨论。在多字节编码处理中发现了SQL注入安全漏洞。SQL 注入安全漏洞可能包括一种情况，即当用户提供要插入数据库的数据时，用户可能会将 SQL 语句注入服务器将执行的数据中。针对该漏洞，当使用不知道字符集的转义（例如PHP中的addslashes()）时，可以绕过某些多字节字符集（例如SJIS、BIG5和GBK）的转义。因此，诸如addslashes() 之类的函数无法防止SQL 注入攻击。在服务器端解决这个问题是不可能的。最好的解决方案是让应用程序使用由 mysql_real_escape_string() 等函数提供的字符集感知转义。

However, a bug was detected in how the MySQL server parses the output of mysql_real_escape_string(). As a result, even when the character set-aware function mysql_real_escape_string() was used, SQL injection was possible. This bug has been fixed.

但是，在 MySQL 服务器如何解析 mysql_real_escape_string() 的输出中检测到一个错误。结果，即使使用了字符集感知函数 mysql_real_escape_string() ，SQL 注入也是可能的。这个错误已被修复。

Workarounds. If you are unable to upgrade MySQL to a version that includes the fix for the bug in mysql_real_escape_string() parsing, but run MySQL 5.0.1 or higher, you can use the NO_BACKSLASH_ESCAPES SQL mode as a workaround. (This mode was introduced in MySQL 5.0.1.) NO_BACKSLASH_ESCAPES enables an SQL standard compatibility mode, where backslash is not considered a special character. The result will be that queries will fail.

解决方法。如果您无法将 MySQL 升级到包含 mysql_real_escape_string() 解析错误修复的版本，但运行 MySQL 5.0.1 或更高版本，则可以使用 NO_BACKSLASH_ESCAPES SQL 模式作为解决方法。（此模式在 MySQL 5.0.1 中引入。） NO_BACKSLASH_ESCAES 启用 SQL 标准兼容模式，其中反斜杠不被视为特殊字符。结果将是查询将失败。

To set this mode for the current connection, enter the following SQL statement:

要为当前连接设置此模式，请输入以下 SQL 语句：

SET sql_mode='NO_BACKSLASH_ESCAPES';

You can also set the mode globally for all clients:

您还可以为所有客户端全局设置模式：

SET GLOBAL sql_mode='NO_BACKSLASH_ESCAPES';

This SQL mode also can be enabled automatically when the server starts by using the command-line option --sql-mode=NO_BACKSLASH_ESCAPES or by setting sql-mode=NO_BACKSLASH_ESCAPES in the server option file (for example, my.cnf or my.ini, depending on your system). (Bug#8378, CVE-2006-2753)

通过使用命令行选项 --sql-mode=NO_BACKSLASH_ESCAPES 或通过在服务器选项文件中设置 sql-mode=NO_BACKSLASH_ESCAPES（例如 my.cnf 或 my.ini ，取决于您的系统）。（错误#8378，CVE-2006-2753）

See also Bug#8303.

另见错误#8303。