#1
  1. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,034
    Rep Power
    535

    PDO, protection against SQL Injection, and IN


    I almost exclusively use PDO. How would you recommend protection against SQL Injection for the following? Thanks
    PHP Code:
    $_GET = array('one','three','six'); //Provided by user
    $sql='SELECT a,b,c FROM t WHERE id IN ('
    foreach (
    $_GET as $id) { $sql.="'{$id}',";}
    $sql=substr($sql0, -1).')';
    echo(
    $sql); 
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    if you ever wondered what those stupid question mark parameters are for: They exist for cases like this.

    So simple fill the IN (...) with question marks and bind your array elements to them.
    Last edited by Jacques1; June 8th, 2013 at 11:11 AM.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,034
    Rep Power
    535
    I didn't think about dynamically creating the prepared statement query, but don't see why it wouldn't work:
    PHP Code:
    $_GET = array('one','three','six'); //Provided by user
    $sql='SELECT a,b,c FROM t WHERE id IN ('
    foreach (
    $_GET as $id) { $sql.="?,";}
    $sql=substr($sql0, -1).')';
    $stmt db::db()->prepare($sql);
    $stmt->execute($_GET); 
    You think doing so is better than the below?:
    PHP Code:
    $_GET = array('one','three','six'); //Provided by user
    $sql='SELECT a,b,c FROM t WHERE id IN ('
    foreach (
    $_GET as $id) { $sql.=db::db()->quote($id);}
    $sql=substr($sql0, -1).')';
    $stmt db::db()->query($sql); 
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Yes. There's simply no reason to fall back to manual escaping in this case. Prepared statements can be used in dynamically constructed queries just like they can be used in hard coded queries.

    The legitimate use cases for manual escaping are very, very few.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  8. #5
  9. No Profile Picture
    Dazed&Confused
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2002
    Location
    Tempe, AZ
    Posts
    506
    Rep Power
    128
    Originally Posted by NotionCommotion
    I didn't think about dynamically creating the prepared statement query, but don't see why it wouldn't work:
    PHP Code:
    $_GET = array('one','three','six'); //Provided by user
    $sql='SELECT a,b,c FROM t WHERE id IN ('
    foreach (
    $_GET as $id) { $sql.="?,";}
    $sql=substr($sql0, -1).')';
    $stmt db::db()->prepare($sql);
    $stmt->execute($_GET); 
    You may have done it just for example shorthand, but you probably wouldn't want to pass $_GET itself into the query. While it wouldn't be a security risk, per se, the user could technically put anything in, in any order, and it'd try to put in all variables. I'm not sure but it'd probably also break if they put in a different number than the number of columns you have.

IMN logo majestic logo threadwatch logo seochat tools logo