Form Input Filtering

[NoSalt]

Hello All

 

    I have a question for everybody out there. What is the best way to filter form inputs for insertion into a MySQL database? That's not really the best way to ask that question but it's the best I can come up with. What I am talking about is between the "htmlspecialchars", "strip_tags", "stripslashes", "mysql_real_escape_string", etc.; it seems like a never ending parade of things you need to check for when putting data in to and taking data out of a database. Where does a person start and where do they stop? It seems like you can have 3 or 4 layers of filtering before data is safe to insert into the database and then another 3 or 4 layers to make sure it is formatted well enough to display on a page when exporting it from the database. I hope I am making sense here and not just rambling. I am working on a site and I am a little overwhelmed as to the best way to do this.

 

    Thanks for reading and any advice you can give would be very much appreciated. 

[JonnoTheDev]

All you really need is

mysql_real_escape_string()

when inserting data.

You may also want to use

strip_tags()

to remove any HTML that a user may try to include in a field.

 

I tend to stay away from addslashes() and stripslashes().

 

Really your filtering should be dependent on what data you expect from the user input. i.e. Should it be a number, should it be a date, should it be an email address, etc

If you properly validate your data you wont end up with crap in the database.

 

Garbage in = garbage out

[ionik]

Here is a nice code snippet that will FILTER data for you

 

and in my own personal opinion addslashes, htmlentities.......

 

if( !get_magic_quotes_gpc() )
{
if( is_array($_GET) )
{
	foreach($_GET as $key1 => $val1)
	{
		if( is_array($val1) )
		{
			foreach($val1 as $key2 => $val2)
			{
				$val1[$key2] = htmlentities(addslashes($val2));
			}
		}
		else
		{
			$_GET[$key1] = htmlentities(addslashes($val1));
		}
	}
}

if( is_array($_POST) )
{
	foreach($_POST as $key1 => $val1)
	{
		if( is_array($val1) )
		{
			foreach($val1 as $key2 => $val2)
			{
				$val1[$key2] = htmlentities(addslashes($val2));
			}
		}
		else
		{
			$_POST[$key1] = htmlentities(addslashes($val1));
		}
	}
}

}
else
{
if( is_array($_GET) )
{
	foreach($_GET as $key1 => $val1)
	{
		if( is_array($val1) )
		{
			foreach($val1 as $key2 => $val2)
			{
				$val1[$key2] = stripslashes(htmlentities(addslashes($val2)));
			}
		}
		else
		{
			$_GET[$key1] = stripslashes(htmlentities(addslashes($val1)));
		}
	}
}

if( is_array($_POST) )
{
	foreach($_POST as $key1 => $val1)
	{
		if( is_array($val1) )
		{
			foreach($val1 as $key2 => $val2)
			{
				$val1[$key2] = stripslashes(htmlentities(addslashes($val2)));
			}
		}
		else
		{
			$_POST[$key1] = stripslashes(htmlentities(addslashes($val1)));
		}
	}
}
}

 

Validating data is what needs to be done on a per data type field basis, filtering data should be the same process for all information recieved

[NoSalt]

What about "mysql_real_escape_string", would you recommend putting that in the loop as well?

[flyhoney]

I agree with neil.johnson, try to avoid addslashes and the like.  When inserting into a database all you really need is mysql_real_escape_string.  Everything else needs to be validated, and there are regular expressions and functions all over the internet for accomplishing those tasks.  To take it a step farther, you might want to consider using Zend or someother framework or collection of classes that is made to do form validation.

[PFMaBiSmAd]

addslashes does not escape all the special characters that can break a query (or that a hacker can put in to cause queries to fail in order to get information contained in any error messages.)

 

String data should use mysql_real_escape_string(). Numeric data won't be helped by using mysql_real_escape_string(). You either need to validate that numeric data is numeric or force it to be numeric. See this thread where SQL was injected on the end of a URL as part of a numeric id and was executed as part of the query because no validation was performed on the external data - http://www.codingforums.com/showthread.php?t=151150

[KevinM1]

Hello All

 

    I have a question for everybody out there. What is the best way to filter form inputs for insertion into a MySQL database? That's not really the best way to ask that question but it's the best I can come up with. What I am talking about is between the "htmlspecialchars", "strip_tags", "stripslashes", "mysql_real_escape_string", etc.; it seems like a never ending parade of things you need to check for when putting data in to and taking data out of a database. Where does a person start and where do they stop? It seems like you can have 3 or 4 layers of filtering before data is safe to insert into the database and then another 3 or 4 layers to make sure it is formatted well enough to display on a page when exporting it from the database. I hope I am making sense here and not just rambling. I am working on a site and I am a little overwhelmed as to the best way to do this.

 

    Thanks for reading and any advice you can give would be very much appreciated. 

 

Well, each function addresses a different need.

 

To guard against SQL injection, you need to escape potential dangerous characters.  Unfortunately, PHP adds a wrinkle to this with its Magic Quotes capabilities.  Magic Quotes, when turned on, automatically escapes some characters.  Unfortunately, it doesn't escape all of the dangerous ones, and it also causes headaches when you want to output strings to the screen.  Thankfully, Magic Quotes is turned off by default in PHP 5, and won't even be available in PHP 6, but there are still many scripts that rely on it, and many PHP installations that have it turned on.  So, typically the first order of business when scrubbing input is to strip the slashes that Magic Quotes automatically adds to all input:

 

$myInput = get_magic_quotes_gpc($_REQUEST['input']) ? stripslashes($_REQUEST['input']) : $_REQUEST['input'];

 

If you're not used to the ternary operator, that's the same as writing:

 

if(get_magic_quotes_gpc($_REQUEST['input'])
{
   $myInput = stripslashes($_REQUEST['input']);
}
else
{
   $myInput = $_REQUEST['input'];
}

 

So, now that the damage caused by Magic Quotes is taken care of, you now need to properly escape the incoming data, which is where mysql_real_escape_string comes in.

 

$myInput = get_magic_quotes_gpc($_REQUEST['input']) ? mysql_real_escape_string(stripslashes($_REQUEST['input'])) : mysql_real_escape_string($_REQUEST['input']);

 

You'll still need to validate the incoming data, but most string-based attacks are no longer a grave concern.

 

The other functions deal with exactly what you want to store.  You probably don't want a user to submit pure HTML or, even worse, JavaScript to your system, so, to be safe, you can either remove all tags from incoming input (the striptags function) or transform the tags to their HTML entity counterparts (htmlentities function, which I like more than htmlspecialchars).

 

So, like I originally said, all of these functions have a distinct use.