Alternative to mysql_real_escape_string without connecting to DB

Question

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:

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

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

Cicily

ca2.php.net/manual/en/function.addslashes.php – Bede

+1 I'm writing MySQL class myself and use mysql_real_escape_string() for parameter binding purpose. I avoid using mysqli as not all hosting support it. I also avoid multi-file & multi-class library. What I need is just a neat & clean single Class. Thank you! – Cicily

+1 for me too. My use-case is a SugarCRM APi where I need to push fragments of SQL to the remote SugarCRM instance through the API. Nasty though that is, it is what I have to work with (go knock some SugarCRM dev heads together). I need to escape strings in SQL in the application that uses the API and that is totally separate from the database behind the SugarCRM instance. – Lombardy

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

Bever

+1 Thanks for the note. I'm not very sure how to test against SQL injection attacks using multi-byte characters. – Cicily

I've been given some old code to update. It uses mysql_escape_string without a connection (still trying to figure out why). Since that function is deprecated, I'm just wondering how I can replace it. It certainly seems reasonable that one could specify the "appropriate character set" in whatever function replaces it without opening a connection. – Quartersaw

@JohnK, it is "correct" as far as PHP could ever be called that. :-) About the $connection parameter: "Optional. Specifies the MySQL connection. If not specified, the last connection opened by mysql_connect() or mysql_pconnect() is used." – Fragrance

sorry but.. what ;) "impossible to build anything safer without a DB connection" – Tablecloth

Impossible? What does mysql_real_escape_string get from the database connection that is not configuration data that could be passed in by hand to an equivalent function? Edit: just read below, it calls up a library function within MySQL, so there is some processing that happens outside of PHP. It may still be portable, but keeping it up-to-date would be a project in itself. – Lombardy

What if the DB character set is known ahead of time? – Rybinsk

Yeah if you know the character set, what prevents doing the escaping without a connection? – Electrobiology

I know this is an old question but we still had an older version of php [5.3.3 I believe] and it didn't seem to need a connection to MySQL to run mysql_real_escape_string(). No, we didn't have the mysql credentials in the ini file. However 5.5.36 seemed to need a connection..Did something change?? So far I haven't found documentation for the change. – Overstuffed

I also sorry for the necropost.What about php 7+? There is no mysql_escape_string – Didi

what is alternate to mysql_real_escape_string in PHP 7x ? – Griffey

> It is impossible to safely escape a string without a DB connection...using the appropriate character set This assertion is incorrect. If you control your databases and servers then you just set them to use the correct character set. What you actually mean is that people tend to mess that up. – Gromwell

mysql_escape_string function was deprecated in PHP 4.3.0, and it and the entire original MySQL extension was removed in PHP 7.0.0. php.net/manual/en/function.mysql-escape-string.php – Incogitant

Answer 2

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 escapes 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("\\\\","\\0","\\n", "\\r", "\'", '\"', "\\Z");

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

Unceremonious

Thanks. I'd suggest something else: function escape($aQuery) { return strtr($aQuery, array( "\x00" => '\x00', "\n" => '\n', "\r" => '\r', '\\' => '\\\\', "'" => "\'", '"' => '\"', "\x1a" => '\x1a' )); } – Cicily

why \x1a gets replaced with \\\x1a rather than \\x1a? Is this a typo? – Jilljillana

-1 This is extremely dangerous. If the database connection is using a multibyte character set, simple bytewise replacements like this could lead to data corruption (including of escaping/quoting characters)--this can be deliberately exploited by a malicious attacker to inject arbitrary SQL. – Loram

If you get the character sets wrong on this it can be dangerous. For multibyte character sets, if mysql is using that someone can just put in a byte to cancel out the \\. Multiebyte charactersets can often take anything as the second byte including \\ for mysql wont see it as a standalone \\ but part of the multibyte character. I have no idea what happens with UTF8. I would hope that with [0xB0, '\\'] that on hitting an invalid byte \\ that is just stops and starts again with that byte rather than swallowing it. – Gromwell

If I know that the charset is utf8, is it safe to use mb_strpos() (and mb_substr() to create behaviour similar to substr_replace()) to do this? – Aeroembolism

Note that mb_substr() is slower than a pure-PHP implementation of it for UTF-8 specifically. – Rybinsk

if you are using a framework such as Yii, you can tap into their CJSON libraries with a trick like this: $saveJsonFieldToDb = CJSON::encode(CJSON::decode($json)); This will first decode the json, then encode it with proper escaping - which is then safe to save to the DB. I tested this and it works with Yii 1.1.x – Platitudinous

Answer 3

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

Bever

+1 Thanks for the extra effort. I'm going around to find out more about multi-byte related SQL injections. – Cicily

I guess it should be $return .= '\x' . dechex($ord); instead – Cicily

As a general rule, I prefer to use '\\' even in single-quoted strings, just because a single '\' can affect the next character if you're not careful. I'm probably just being OCD again. – Bever

This function doesn't follow mysql escaping rules and will result in loss of data integrity. "MySQL recognizes the escape sequences shown in Table 9.1, “Special Character Escape Sequences”. For all other escape sequences, backslash is ignored. That is, the escaped character is interpreted as if it was not escaped. For example, “\x” is just “x”." - dev.mysql.com/doc/refman/5.6/en/… – Neuberger

Does \xAA actually mean anything more than the text "xAA" in a MySQL string literal? The documentation seems to say that \x does not have any special meaning, so the \ will be ignored. dev.mysql.com/doc/refman/5.0/en/string-literals.html – Lombardy

the regex from zombat is 5-6 tim es faster. – Carleton

Answer 4

From further research, I've found:

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.

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:

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)

See also Bug#8303.

Cicily

This has been fixed long time ago. – De

Also beware that NO_BACKSLASH_ESCAPES introduces other vulnerabilities. – Loram

Fixed in 5.1.11 - The link was broken, here is the archive: web.archive.org/web/20120501203047/http://dev.mysql.com:80/doc/… – Overstuffed