sql injection question

kaliok

I just wanted to get some feedback on some code I am planning on using to stop SQL injection (if it is at all possible with the following code). The scenario would be the user would input some search criteria. The search criteria would be somewhat Googlesk in nature, for example: pet +dog -cat -"golden retriever" So I need to allow: backslashes,stars, and plus signs so that the user can use some of the capabilities of the boolean mode search. Is the following secure enough to stop a sql injection, I have done some tests but perhaps someone could have a look and point out any flaws and fixes please. I have also used the stripslashes to allow quotes!

....
$thesearch=trim(stripslashes(mysql_real_escape_string(@$_POST['ud_mysearch'])));
$thesearch=strtr($thesearch,',/&()$%^@~`?;','');
$queryGC="SELECT *,MATCH(keywords) AGAINST ('$thesearch' IN BOOLEAN MODE) AS score FROM images WHERE MATCH(keywords) AGAINST ('$search' IN BOOLEAN MODE)";
....

Thanks in advance for any help.

leatherback

quick peak:
performing a stripslashes on the mysql_escape_real result will not give you the effect you are looking for. If the magic quotes are on, first stripslashes, then mysql_escape_real_string

NogDog

As LB points out, stripslashes(mysql_real_escape_string($value)) is a zero-sum solution, simply removing the back-slashes that the other function inserts. Also, only use stripslashes() at all if you know the magic_quotes_gpc is turned on, otherwise you may be removing valid slashes. If you cannot control the state of magic_quotes_gpc, then use [man]get_magic_quotes_gpc/man to check its status before deciding whether or not to apply stripslashes() (and do that before you apply mysql_real_escape_string()).

kaliok

Thanks for the advice.

So I have modified the code (assuming that the magic quotes are ON):

$search=trim(mysql_real_escape_string(stripslashes(@$_POST['ud_search'])));
$badchac=array(","=>"","?"=>"","`"=>"","@"=>"","^"=>"","~"=>"","%"=>"","$"=>"","{"=>"","}"=>"","("=>"",")"=>"","&"=>"","/"=>"",","=>"",";"=>"");
$search=strtr($search,$badchac);

$queryGC="SELECT *,MATCH(keywords) AGAINST ('$search' IN BOOLEAN MODE) AS score FROM images WHERE MATCH(keywords) AGAINST ('$search' IN BOOLEAN MODE)";

Does that now seem to be OK. Will that stop SQL Injections?

Thanks again.

NogDog

I would do the trim() first:

$search = mysql_real_escape_string(stripslashes(trim(@$_POST['ud_search'])));

As far as the $badchac array and the strstr() call, perhaps what you want there is a [man]str_replace/man, instead?

Anyway, in terms of SQL injection, mysql_real_escape_string() is all that matters. Everything else is specific to your desired functionality and not relevant to injection problems.

kaliok

thanks.