...

View Full Version : Oh no, another security question again!



halifaxer
03-04-2009, 10:57 PM
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!

:)

Fou-Lu
03-04-2009, 11:33 PM
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.

halifaxer
03-05-2009, 11:28 AM
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?

Fou-Lu
03-05-2009, 12:27 PM
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.

halifaxer
03-05-2009, 08:16 PM
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.

:)

Fou-Lu
03-06-2009, 12:15 AM
$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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum