Thursday, April 12, 2012

Alternative to mysql_real_escape_string without connecting to DB

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:

Thank you in advance!

Source: Tips4all


  1. 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.

    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.

  2. 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;
    $return .= '\\x' . dechex($ord);
    return $return;

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

  3. Well, according to the mysql_real_escape_string function reference page: "mysql_real_escape_string() calls MySQL's library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \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("\\x00", "\\n", "\\r", "\\\\" ,"\'", "\\\"", "\\\x1a");

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

  4. From further research, I've found:

    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.

    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.

    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().

    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.

    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.

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


    You can also set the mode globally for all clients:


    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)

    See also Bug#8303.