Data Safe - SQL Injection

[cancer10]

Hi,

This is an interesting question (in my humble opinion).

I am trying to create a forum in PHP. So there is a "Post a new Thread" link, clicking on which would open a textarea field for posting a message and a textbox for the "title".

Now, I dont want ppl to sql inject queries through the textarea or the textbox field.

So I have created this function for the same.

function dbsafe($data){
$data = str_replace('select','',$data);
$data = str_replace('alter','',$data);
$data = str_replace('delete','',$data);
$data = str_replace('replace','',$data);
return $data;
}

Now, though the above function would secure my db upto some extent but the only limitation I can see is my members/visitors will not be able to use the words "select","alter","delete","replace" in their threads even though they want use them (not for sql injection purpose).

So, I had to change my dbsafe function to the following:

function dbsafe($data){
$data = str_replace('tbl_members','',$data);
$data = str_replace('tbl_login','',$data);
$data = str_replace('tbl_details','',$data);
$data = str_replace('tbl_orders','',$data);
return $data;
}

ok, so now my function would now replace my database tables if the attacker intends to destroy or misuse them using any sql statements.

Now my question is that:

1) Which one of the above 2 functions is better you think?
2) Also, in my second function, is there a way the attacker would hamper my database without using my table names?

Please guide and help.

Need your opinion

Thanx so much in advance

[ryan_vietnow]

why not try to use mysql_real_escape_string function?

here I have made one:

function antiinject($var)
{
	if (get_magic_quotes_gpc())
		{
			$var1=stripslashes($var);
		}
	else
		{
			$var1=$var;
		}
	return mysql_real_escape_string($var1);
}

Hope this helps.

[cancer10]

what does your function do?

Plz explain

[nav33n]

See this , Example 2.

[ryan_vietnow]

About your 2 functions,I think the 2nd function is better,but the attacker can destroy not just your tables but the whole database itself:

drop database [database name];

stripslashes Un-quotes a quoted string that is used in SQl statements therefore escaping all quotes making the injection useless.

the function checks if get_magic_quotes_gpc is on to prevent double escaping of quotes.

[cancer10]

Thanx ryan for the detailed explanation. Appreciate it.

So you think both the functions "get_magic_quotes_gpc" and "mysql_real_escape_string" are being used in all professional forums to prevent injections?

[ryan_vietnow]

there are may ways to prevent SQL injection attacks friend.

try to check this link.very helpful to me and I will think will be helpful to you also.

http://www.jonlee.ca/how-to-prevent-sql-injection-attacks/

[MattEvans]

The way to look at injection protection is not to "take the bad things out" but to "only let the good things in". There is, funnily enough, a big difference.

Properly quoting the input text to make it impotent is a reasonable strategy in this case, since you're accepting a wide range of legal input characters; but for a web forum also consider sanitising HTML markup in any uploaded data: since you don't want anonymous people posting working Javscript onto your hosted pages. It's not a risk to your server, or even user's machines -- but it can be used to steal online identities.

As for the professional forum software, most are open source so you could have a look at how they do it. It's a good bet they use either the builtin PHP string escape methods as ryan suggested; or regular expression based sanitisers; or state-based parsers as sanitisers.

Sanitising using stringwide-replace operations as you suggested in the original two examples is not even marginally safe. Consider this input ( to your first sanitiser ): del[b]delete[/b]ete * from * Your code will replace the highlighted instance of delete, creating a new delete as it does so, which will be subsequently un-noticed ( since that's how replace works ). Yay!

[cancer10]

why not try to use mysql_real_escape_string function?

here I have made one:

function antiinject($var)
{
	if (get_magic_quotes_gpc())
		{
			$var1=stripslashes($var);
		}
	else
		{
			$var1=$var;
		}
	return mysql_real_escape_string($var1);
}

Hope this helps.

Will this function prevent my users to insert php tags into the db?

Like <?php phpingo();?> etc?

[ryan_vietnow]

mysql_real_escape_string is used for SQL statements only.

Here,I have made a function to delete these tags.

function deletephptags($var)
{
//$b get the text after the <?php tag..
list($a,$b)=explode("<?php",$var);
//$c get the text after <?php and before  the ending tag..
list($c,$d)=explode("?>",$b);
//deletes the php tags and functions inside them..
$data=str_replace($c,'',$var);
//return the new string....
return $data;
}

Enjoy!