input sanitization help

[TheEvilMonkeyMan]

I thought I'd done this a hundred times before... but I am lost. Even after running mysql_real_escape_string, strip_tags and addslashes etc, I can still enter SQL into my input and it screws with the query. I can't simply use regex to check for valid characters since it's an input that lets the user format a post with BBcode and characters they want. What's the proper way to 'clean' the input, then? Thanks in advance

 

[Pikachu2000]

Post the relevant code, It sounds like you're trying to use too many functions, and they're just conflicting with each other.

[TheEvilMonkeyMan]

if(isset($_POST['submit_post']))
{
if(isset($_POST['post_content']) && !empty($_POST['post_content']))
{

	if(get_magic_quotes_gpc())
	{
		$content = stripslashes($_POST['post_content']);
	}
	else
	{
		$content = $_POST['post_content'];
	}

	$content = strip_tags($content);

	$bb_from = array(
		'/\[b\](.*?)\[\/b\]/',
		'/\[i\](.*?)\[\/i\]/',
		'/\[u\](.*?)\[\/u\]/'			
	);

	$bb_to = array(
		'<b>$1</b>',
		'<i>$1</i>',
		'<u>$1</u>'			
	);

	$content = preg_replace($bb_from, $bb_to, $content);

	$content = mysql_real_escape_string($content);

	echo stripslashes($content);
}
}

[TheEvilMonkeyMan]

Just some simple input cleaning example/s would be great... need to make sure they don't abuse my mysql_query() with their submitted post...

 

[Pikachu2000]

For use in a query, as long as string type data is escaped with mysql_real_escape_string(), it should be just fine. For numeric data types, validate the incoming data and typecast it appropriately. For select boxes, checkboxes and radio buttons, I usually validate the value against the array of acceptable values by using in_array().

 

Anyhow, why are you escaping the data, then echoing it applying stripslashes()?

[TheEvilMonkeyMan]

Oh, I was only echoing it to see the output... I would have thought mysql_real_escape_string is okay except that it let "SELECT * FROM users" and just about any other SQL through. I could effectively DROP tables that way, yeah?

[gizmola]

The purpose of the escape functions is to escape characters that are delimiters in SQL.  This is very clear if you read the page describing what it does.  It is not a 'SQL removal tool'.

 

With that said, let's assume that I have this query:

 

INSERT INTO mytbl (notes) VALUES ('$somevar');

 

If $somevar = 'DROP TABLE mytbl'  this does not matter in the least -- storing a string that contains SQL does not cause it to be executed.  SQL injections are either a batch character followed by some rogue SQL, or a partial string that gets interpolated into a string that is getting passed to a mysql_query() or similar function, and in the interpolation process changes the original intention of the developer.  The best solution to that is to use mysqli and prepared statements, which are impervious to these injections.  Just for the record, mysql_query cannot be used to batch multiple queries, so that's not something you have to worry about with mysql.  Other databases like mssql and oracle that do allow for batched queries, need to have the batch character removed as part of the process of protecting against SQL injections.

[TheEvilMonkeyMan]

Ah, that makes sense now Thanks Pikachu2000 and gizmola for your thorough answers.