Oh no, another security question again!

Yes, I'm afraid it's true. It's been done to death and I can understand the archive posts about this, but just to clear something up in my own head related to my site:

I have a product website that uses variable in the url to display the related products. I have 2 examples I want to run through. The first would be...

www.mysite.co.uk/product_page.php?cat=10

From here, the sql script that runs with it would be something like...

"SELECT * FROM products WHERE cat='$_GET[cat]'";

Now, is it true that because the variable of "cat" will only ever be a integer as far as my database is concerned, by changing the above to the following I have made this secure against attack...

"SELECT * FROM products WHERE cat=".intval($_GET['cat']);

Second question is how to change it round, so that if someone is performing a SEARCH on my website and the variable is definitely not going to be an interger, how do I protect myself?

www.mysite.co.uk/product_page.php?search_term=chocolate+vanilla

How do I secure the following:

"SELECT * FROM products WHERE name LIKE '%$_GET[search_term]%'";

All help greatly appreciated, as just can't get my head round it to be honest - it's nothing as drastic as usernames and passwords, but still very important to me!

:)

I'm assuming you're using PHP right?
You use mysql_real_escape_string. Do it on each item is the best way. Something like this:

if (@get_magic_quotes_gpc())
{
$_GET['searchterms'] = stripslashes($_GET['searchterms']);
}

if (isset($_GET['searchterms']))
{
$terms = split(' ', $_GET['searchterms']);
$conditionField = 'name';
$andOr = 'OR';
$table = 'Products';
$terms = array_map('mysql_real_escape_string', $terms);
$condition = implode('%\' ' . $andOr . ' ' . $conditionField . ' LIKE \'%', $terms);
printf("SELECT * FROM %s WHERE %s LIKE '%%%s%%'", $table, $conditionField, $condition);
}


Hmm, I probably shouldn't have used a printf, looks confusing with all the % signs.

Wow... thought it was going to be a little less complicated and big. Thought there'd be a few backslashes somwhere in the code. Oh, naivity! lol. But if you want it done right I suppose...

Does it make a difference to the syntax by removing the printf line?

Wow... thought it was going to be a little less complicated and big. Thought there'd be a few backslashes somwhere in the code. Oh, naivity! lol. But if you want it done right I suppose...

Does it make a difference to the syntax by removing the printf line?

No, the printf was only used to show what was available in the $condition variable. Removing it would have no effect, but you'll want to make sure you build a proper query too.
The only other thing I should mention is that in PHP, you can only use the mysql_real_escape_string (or most of the mysql functions) with an open mysql connection. So you'll need to make sure that the connection is open first. MySql_real_escape_string will take care of doing all of you're escaping, and covers more characters than the addslashes does.

OK, wow this is great help but I've been trying to rejig the code to fit around the following but I keep going wrong on the implode line:


"SELECT * FROM products WHERE brand LIKE '%$_GET[search_term]%' AND forSale='yes' AND inStock='yes' OR name LIKE '%$_GET[search_term]%' AND forSale='yes' AND inStock='yes'";


Slightly more complicated. I have come up with the following which add the forSale and inStock for all but the last word in the search term:


if (isset($_GET['searchterms']))
{
$terms = split(' ', $_GET['searchterms']);
$conditionField = 'name';
$andOr = 'OR';
$table = 'Products';
$terms = array_map('mysql_real_escape_string', $terms);
$condition = implode('%\' AND forSale=yes AND inStock=yes '.$andOr.' '.$conditionField.' LIKE \'%', $terms);
printf("SELECT * FROM %s WHERE %s LIKE '%%%s%%'", $table, $conditionField, $condition);
}


Can't get the single quotes('') around the values 'yes' though. The % are confusing me a lot as you say. I don't get where or what these 's' stand for, although I assume it's just part of the code dynamics.

Again, all help is appreciated. this is really exciting me because I'm able to follow it as I go along, but sort of stuck at a crossroads to expand on the code.

:)

$condition = implode('%\' AND '.$andOr.' '.$conditionField.' LIKE \'%', $terms);

The forSale=\'yes\' AND inStock=\'yes\' doesn't go in the implode condition. Add that here:

printf("SELECT * FROM %s WHERE %s LIKE '%%%s%%' AND forSale = \'yes\' AND inStock = \'yes\'", $table, $conditionField, $condition);


%s = interpret variable as string
%% = literal % sign.