Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New Coder
    Join Date
    Feb 2008
    Posts
    37
    Thanks
    4
    Thanked 0 Times in 0 Posts

    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...

    Code:
    www.mysite.co.uk/product_page.php?cat=10
    From here, the sql script that runs with it would be something like...

    Code:
    "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...

    Code:
    "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?

    Code:
    www.mysite.co.uk/product_page.php?search_term=chocolate+vanilla
    How do I secure the following:

    Code:
    "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!


  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    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:
    PHP Code:
    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.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #3
    New Coder
    Join Date
    Feb 2008
    Posts
    37
    Thanks
    4
    Thanked 0 Times in 0 Posts
    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?

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Quote Originally Posted by halifaxer View Post
    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.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #5
    New Coder
    Join Date
    Feb 2008
    Posts
    37
    Thanks
    4
    Thanked 0 Times in 0 Posts
    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:

    Code:
    "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:

    Code:
    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.


  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Code:
        $condition = implode('%\' AND '.$andOr.' '.$conditionField.' LIKE \'%', $terms);
    The forSale=\'yes\' AND inStock=\'yes\' doesn't go in the implode condition. Add that here:
    Code:
    printf("SELECT * FROM %s WHERE %s LIKE '%%%s%%' AND forSale = \'yes\' AND inStock = \'yes\'", $table,  $conditionField, $condition);
    %s = interpret variable as string
    %% = literal % sign.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •