mysql_real_escape _string problem

[phyconinja]

I have a form the user is able to input some info into. (comment box)
To be safe from sql injection I used mysql_real_escape_string on the input, and to be safe from people using html or javascrip I used htmlspecialchars after taking it back out from the database.

The problem is that when I user types ' or ", it becomes /' and /".
Should I go back to just using htmlspecialchars on the input, an forget about escaping it? Or is there a better way of doing it?

Thanks

[1337er-than-you]

Just use stripslashes() on output. That's what I do.

[elbekko]

You have to strip out the quotes put there by magic_quotes_gpc first.
Make a wrapper function like this:

function escape($str)
{
    return mysql_real_escape_string(stripslashes($str));
}

[phyconinja]

Thanks a lot

[henke37]

Do note that magic quotes might not be enabled on all hosts, check if it is enabled and then cleanup it's damage.
And do it way early, like in a common include, before any use at all of the indata.

[Sir-Davey]

Here's a nifty function you can put in your header (or at least before you use any _SESSION, _COOKIE, _POST, etc variables) that will reverse the atrocious effect of magic_quotes

	if (get_magic_quotes_gpc()) {
		function stripslashes_array($array) {
			return is_array($array) ? array_map('stripslashes_array', $array) : stripslashes($array);
		}

		$_COOKIE = stripslashes_array($_COOKIE);
		$_FILES = stripslashes_array($_FILES);
		$_GET = stripslashes_array($_GET);
		$_POST = stripslashes_array($_POST);
		$_REQUEST = stripslashes_array($_REQUEST);
	}

It fixes EVERYTHING (unescapes all quotes, including arrays in arrays), so you have to be really careful not to allow SQL injections or otherwise dangerous security holes that come with unsanitized user input.

[phyconinja]

At 3/3/08 07:09 AM, Sir-Davey wrote: Here's a nifty function you can put in your header (or at least before you use any _SESSION, _COOKIE, _POST, etc variables) that will reverse the atrocious effect of magic_quotes

Wouldn't it be safer to just use stripslashes() on the things you need to? Instead of using it on everything?

[Sir-Davey]

At 3/3/08 09:31 AM, phyconinja wrote: Wouldn't it be safer to just use stripslashes() on the things you need to? Instead of using it on everything?

You obviously could, but before you use stripslashes you must check whether magic quotes are on or not, otherwise a perfectly legit user trying to use \' for some reason will end up not seeing his backslash. That adds another line of code to your script and can get very bloated after a while. I prefer my way of escaping everything and making sure your code is secure from the ground-up (that is using intval for id, mysql_real_escape_string for everything else). If you're like me, though, you've got a Database abstraction layer combined with a security layer that takes care of everything.

For example I don't need to run queries, I do

$posts = $db->fetchAll('topic_id',$_GET['topic_id']);

and I've got all the posts from the topic i need. The Security layer checks the datatype for the "topic_id" field in the database, and if it's of type INT I run intval() on the parameter. You have no idea how simple it makes coding websites, especially with a flexible template system:

//IN TEMPLATE
{{ foreach post }}
Date: {{ post.date_posted }}
User: <a href="profile.php?id={{ post.user_id }}">{{ post.user }}</a>
{{ end post }}

I love my custom framework :)

[BoneIdol]

Why not just do a general function for inserting values into a database? Sorry to repost these, but:

function dbin($string, $like = false)
{
	if (get_magic_quotes_gpc()){
		$string = stripslashes($string);
	}
	$string = mysql_escape_string($string);
	if ($like){
		$string = addcslashes($string, '%_');
	}
	return $string;
}
function dbout($string, $newline = false, $entities = true)
{
	if ($newline){
		if ($entities){
			return nl2br(htmlentities(stripslashes($string)));
		}else{
			return nl2br(stripslashes($string));
		}
	}else{
		if ($entities){
			return htmlentities(stripslashes($string));
		}else{
			return stripslashes($string);
		}
	}
}

The $like arguement in dbin escapes characters that are special in like queries. While they're not especially dangerous, they can be used to ddos your mysql database. mysql_escape_string is used instead of mysql_real_escape_string because it doesn't require an open database connection.

The dbout function deals with taking content back out of the database. It will convert new lines to <br />s and convert special html characters like < into their entity codes.

For me, using these is a ton easier than sanitising every single superglobal. Each to his own I suppose.

[henke37]

I must be geting sloppy, I have forgotten to rant about how parameterized queries is better.

[Afro-Ninja]

At 3/3/08 09:46 AM, Sir-Davey wrote: For example I don't need to run queries, I do

$posts = $db->fetchAll('topic_id',$_GET['topic_id ']);

and I've got all the posts from the topic i need. The Security layer checks the datatype for the "topic_id" field in the database, and if it's of type INT I run intval() on the parameter. You have no idea how simple it makes coding websites, especially with a flexible template system:

I noticed your fetchAll() method shares the same name as PDO's 'fetchAll' method. How does it know what table to select from? And what if you want specific values, and not *?

[Sir-Davey]

At 3/3/08 02:44 PM, Afro-Ninja wrote: I noticed your fetchAll() method shares the same name as PDO's 'fetchAll' method. How does it know what table to select from? And what if you want specific values, and not *?

The last table used is by default, otherwise the function accepts other parameters, example:

$posts = $db->fetchAll('topic_id',$_GET['topic_id'],'posts','LIMIT 5,20 ORDER BY date ASC');

Obviously there's still a little SQL involved for more complicated queries.

[Afro-Ninja]

At 3/3/08 04:34 PM, Sir-Davey wrote: Obviously there's still a little SQL involved for more complicated queries.

right, gotcha. just making sure there wasn't a magical sql practice I was missing out on, heh.