Page 1 of 1

Data to mysql that doesnt use mysql_real_escape_string

Posted: Thu Mar 30, 2006 9:55 pm
by danf_1979
This code is from e107 CMS. It is used everytime a data must go to mysql. I got some questions about it:
1) Why they use htmlspecialchars and not mysql_real_escape_string?
2) Is this code worthy?
$search = array('$', '"', "'", '\\', '<?');
$replace = array('$','"',''', '\', '&lt?');
$ret = str_replace($search, $replace, $data);

3) And this one?
$data = htmlspecialchars($data, ENT_QUOTES, CHARSET);
$data = str_replace('\\', '\', $data);
$ret = preg_replace("/&#(\d*?);/", "&#\\1;", $data);

I ask because I use mysql_real_escape_string, and I have been adviced to do so, by several people I think they know what they're talking about. So please.. could you comment on this? Does this code have any advantage over mysql_real_escape_string?

Code: Select all

function toDB($data, $nostrip = false, $no_encode = false, $mod = false)
	{
		global $pref;
		if (is_array($data)) {
			// recursively run toDB (for arrays)
			foreach ($data as $key => $var) {
				$ret[$key] = $this -> toDB($var, $nostrip, $no_encode);
			}
		} else {
			if (MAGIC_QUOTES_GPC == TRUE && $nostrip == false) {
				$data = stripslashes($data);
			}
			if(isset($pref['post_html']) && check_class($pref['post_html']))
			{
				$no_encode = TRUE;
			}
			if (getperms("0") || $no_encode === TRUE)
			{
				$search = array('$', '"', "'", '\\', '<?');
				$replace = array('$','"',''', '\', '&lt?');
				$ret = str_replace($search, $replace, $data);
			} else {
				$data = htmlspecialchars($data, ENT_QUOTES, CHARSET);
				$data = str_replace('\\', '\', $data);
				$ret = preg_replace("/&#(\d*?);/", "&#\\1;", $data);
			}
		}

Posted: Thu Mar 30, 2006 10:31 pm
by John Cartwright
mysql_real_escape() should be used for a couple reasons, although it basically boils down to the fact this function escapes more than just quotes. It also escapes

Code: Select all

NULL, \x00, \n, \r, \, ', " og \x1a
htmlspecialchars() will not, therefor the possibility of SQL injection is technically still possible.

It is my preference to never input any formatted information, considering how I handle the data can be easily changed without having to ever reformat the information into the database. It is also best to use htmlentities()/htmlspecialchars() outputting the dataset to prevent XSS attacks.

Posted: Sun Apr 02, 2006 7:47 am
by Ollie Saunders
don't use regular expressions where standard functions will do. standard functions will always work and they better describe the action being taken.

Posted: Sun Apr 02, 2006 6:24 pm
by baggypants303
what is this: \x00, og, and \x1a ????

Posted: Sun Apr 02, 2006 6:38 pm
by Ambush Commander
\x00 = null byte

I'm not sure what the other two are. Why don't you get an ASCII character chart and look them up?

Posted: Sun Apr 02, 2006 7:41 pm
by AKA Panama Jack
They are using that because versions of PHP before 4.3.0 do not support mysql_real_escape_string.

Instead of actually checking the version of PHP the program is running on and uses the appropriate code they just using the old coding method. This way their program will work on older versions of PHP.

They could use something like this that would work on older and newer versions of PHP.

Code: Select all

if (strnatcmp(PHP_VERSION, '4.3.0') >= 0) {
	return "'" . mysql_real_escape_string($string) . "'";
}
else
{
	$string = str_replace("'", "\\'" , str_replace('\\', '\\\\', str_replace("\0", "\\\0", $string)));
	return  "'" . $string . "'"; 
}

Posted: Mon Apr 03, 2006 2:39 am
by Ollie Saunders
btw AKA Panama Jack you might be interested to learn about version_compare()

Posted: Mon Apr 03, 2006 3:55 am
by AKA Panama Jack
Yep, I knew about that one but it is also specific to PHP 4.1.0 and higher. The one I posted is generic and should work on any version of PHP.

Posted: Mon Apr 03, 2006 8:43 am
by Maugrim_The_Reaper
It's careless - report it as a security bug and tell them their escaping is a half measure that's open to exploitation (might be - depends on the workflow to that point...).

ADOdb suffered from the same fatal mistake up to a short time ago. It used a custom escape function for strings in PostgreSQL, completely ignoring pg_escape_string(). It was ignored for months (no kidding) after I reported it. I doubt I was the only one to. Eventually public highlighting forced some action (it was reported openly on several major security listings).

If applications ceased considering these half measures "secure", it would solve a lot of problems...

Posted: Tue Apr 04, 2006 3:17 am
by Ollie Saunders
It's careless -
i'm sorry i'm lost, what's careless exactly?

Posted: Tue Apr 04, 2006 5:25 am
by Maugrim_The_Reaper
It's careless relying on a custom escaping function,when more modern PHP versions have native functions like mysql_real_escape_string() which escape more accurately and completely.

Posted: Tue Apr 04, 2006 5:51 am
by Ollie Saunders
oh right. well then i'm in total agreement.