SQL Injection Prevention for search boxes

[kaliok]

Hi All

 

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.

....
$thesearch=trim(stripslashes(strip_tags(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.

 

[RMcLeod]

When nesting PHP functions PHP performs them from the inside out, therefore stripslashes will remove any slashes added by mysql_real_escape_string.

[kaliok]

Ok. Thanks.

 

So I'll remove one or tother of those.

 

Is the code still and/or now vulnerable?

 

....
$thesearch=trim(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)";
....

[kaliok]

Actually it appears I do need to use both stripslashes and mysql_real_escape_string to allow me to use quotes (as in the example in the original question). From what I can see on the examples given on the php.net site this shouldnt be a problem. At any rate I am still confused as to whether the code I have used is sufficient to stop an attack.

 

[RMcLeod]

Is magic quotes switched on? If so you don't need mysql_real_escape_string as magic quotes will automagically add slashes for you. If it's turned off then as I said stripslashes will undo the work of mysql_real_escape_string.

[conker87]

Is magic quotes switched on? If so you don't need mysql_real_escape_string as magic quotes will automagically add slashes for you. If it's turned off then as I said stripslashes will undo the work of mysql_real_escape_string.

If they're switched on, then remove the slashes they do and use mysql_real_escape_string.. MAGIC_QUOTES are awful.

[RMcLeod]

If they're switched on, then remove the slashes they do and use mysql_real_escape_string.. MAGIC_QUOTES are awful.

 

I totally agree with you, but it's not always possible for people to turn them off, it depends on their hosting provider.

[conker87]

There's a bit of code to remove slashes:

 

<?php
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);
}
?>

And you're all set to do your own stripping. If you have an include file, then stick that at the top of the page.