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
Thank you in advance!
Source: Tips4all
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.
ReplyDeleteIf 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.
In direct opposition to my other answer, this following function is probably safe, even with multi-byte characters.
ReplyDelete// 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 ...
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."
ReplyDeleteWith 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);
}
From further research, I've found:
ReplyDeletehttp://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 josh@postgresql.org and Tom Lane tgl@sss.pgh.pa.us 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.
http://ca2.php.net/manual/en/function.addslashes.php
ReplyDelete