SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    PDO Prepared Statement

    Hi,

    I'm working with PDO database connection and prepared statements for the first time. I have it all up and running now through OOP but i have a question about how best to pass the parameters to my PDO query() method. I am currently passing the parameter as the second value in my query() function. Is this a secure way to pass the parameters into the prepared statement or does this method defeat the purpose security wise? Here is my code:

    PHP Code:
    $param1 $local->currentLangID;
        
    $stmt $database->query("SELECT * FROM lang_homepage WHERE homepage_lang_id = ?"$local->currentLangID);
        
    $row $database->fetch_array($stmt);
        
    extract ($row);

    // Then in my PDO Class:

    public function query($sql$param1) {

    $stmt $this->pdo->prepare($sql);
        
        
    /* Binding Values:
        Here is a list of the PDOStatement class's. As a rule of thumb you should use bindValue() when you have to pass data only once and bindParam() if you have to pass multiple data (e.g. all values of an array):
        
            PDO::PARAM_BOOL (for booleans)
            PDO::PARAM_NULL (for SQL NULL)
            PDO::PARAM_INT (for SQL INTEGER)
            PDO::PARAM_STR (for string types)
            PDO::PARAM_LOB (for Large OBject types)
            PDO::PARAM_STMT (for a recordset type, currently not supported)
            PDO::PARAM_INPUT_OUTPUT (for an INOUT parameter of a Stored Procedure)
        */
        
        // bind the values
        
    $stmt->bindValue(1$param1PDO::PARAM_STR); // (which parameter is to be replaced,  the value to bind, the PDO data type)
        // add additional parameters if needed
        // $stmt->bindValue(2, $param2, PDO::PARAM_STR);
        
        
    $stmt->execute();
        
        
    /* This is the list of fetch options available in PDO:
            PDO::FETCH_LAZY
            PDO::FETCH_ASSOC
            PDO::FETCH_NAMED
            PDO::FETCH_NUM
            PDO::FETCH_BOTH
            PDO::FETCH_OBJ
            PDO::FETCH_BOUND
            PDO::FETCH_COLUMN
            PDO::FETCH_CLASS
            PDO::FETCH_INTO
            PDO::FETCH_FUNC
            PDO::FETCH_GROUP
            PDO::FETCH_UNIQUE
            PDO::FETCH_KEY_PAIR
            PDO::FETCH_CLASSTYPE
            PDO::FETCH_SERIALIZE
            PDO::FETCH_PROPS_LATE
        */
        
        
    return $stmt;
    }

    // query the database
        
    public function fetch_array($stmt) {
            
            
    // set the PDO fetch mode
            
    $stmt->setFetchMode(PDO::FETCH_ASSOC);
            
            
    // loop through the array
            
    $row $stmt->fetch();
            
            return 
    $row;
        } 

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    No, doing this does not defeat the purpose. You're fine.

    What if your query has more than one parameter, though? This function is only usable for those with exactly 1 and that 1 is a string.

    You can pass in an array and PDOStatement::execute can take an array of parameters.

    PHP Code:
    public function query($sql$params) { 

        
    $stmt $this->pdo->prepare($sql);         
        
    $stmt->execute($params); 
        return 
    $stmt;



  3. #3
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks Dan!

    I thought you had to use bindValue or bindParam though? I have just tested it as you described by just passing an array to execute() and it seems to work fine. In what situation would you need to use bindValue or bindParam then? If one of the values is an integer for example do i need to specify that for PDO?

  4. #4
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,069
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Banana Man View Post
    Thanks Dan!

    I thought you had to use bindValue or bindParam though? I have just tested it as you described by just passing an array to execute() and it seems to work fine. In what situation would you need to use bindValue or bindParam then? If one of the values is an integer for example do i need to specify that for PDO?
    That is a good question, per the manual, when passing an array to execute(), all parameters are treated as PDO::PARAM_STR, so it won't sanitize numeric fields or validate them, it will still try to execute the query passing them as a string.

    Personally, I like the solution that contact[at]maximeelomari.com 17-Jul-2011 05:19 came up with on the bindValue manual page. It permits you to still pass in an array of values, plus you can optionally pass in an array defining the type of each value so you get all of the benefits of PDO.

  5. #5
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Also, can someone explain what the is difference between positional i.e. ? , or named parameters? I'm using positional parameters but just read in another some saying that named parameters should be used.

  6. #6
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,069
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Banana Man View Post
    Also, can someone explain what the is difference between positional i.e. ? , or named parameters? I'm using positional parameters but just read in another some saying that named parameters should be used.
    Okay, so let's say this is your existing code
    PHP Code:
    $sql "SELECT * FROM lang_homepage WHERE homepage_lang_id = ?";
    $statement $database->prepare($sql);
    $statement->execute(array(5)); 
    Using named parameters it would become
    PHP Code:
    $sql "SELECT * FROM lang_homepage WHERE homepage_lang_id = :lang_id";
    $statement $database->prepare($sql);
    $statement->execute(array(':lang_id' => 5)); 
    The thought behind this, is the latter is more readable. I personally don't have an opinion over which I like best. If you have a large SQL command to run, then using named parameters should help ensure you send the write value to the write position. If you have only 1-3 parameters, named parameters don't contribute anything (in my opinion).

  7. #7
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks! I'll leave them as they are in that case.

  8. #8
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    And another question

    What values should be converted to parameters? Should i be using ? for the database fields i want to access, the database name, INSERT VALUES, UPDATE VALUES and the WHERE clause parameters? At the moment it seems to be a bit random which ones i substitute.

    In a SELECT query i and substituting the WHERE clause params but in INSERT and UPDATE queries i am substituting the VALUES.

    Thanks!

  9. #9
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,069
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Banana Man View Post
    In a SELECT query i and substituting the WHERE clause params but in INSERT and UPDATE queries i am substituting the VALUES.
    You're doing it right.

    For UPDATE, you would likely substitute the VALUES and the WHERE clause params, same for DELETE. INSERT you would primarily only substitute the VALUES.
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  10. #10
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Good. Thanks!

  11. #11
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Just doing a bit more work on my database class. I have been testing out bindParam. I am now specifying the data type of each parameter sent. If the param is a String i am using:

    $stmt->bindParam($i, $execute_array[$pos], PDO::PARAM_STR); // (parameter to be bound, value to be bound, parameter type)

    and if it as Integer i am using:

    $stmt->bindParam($i, $execute_array[$pos], PDO::PARAM_INT); // (parameter to be bound, value to be bound, parameter type)

    However, it doesn't seem to matter if the parameter is a string or integer if i define the param to be a String when binding or vice versa.

    Should i not get an error here if i am sending a String and try to bind it as an Integer? I thought the idea with this was so that parameter data type could be validated?

    Thanks!

  12. #12
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,069
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    What value does $execute_array[$pos] contain for both instances?
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  13. #13
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cpradio View Post
    What value does $execute_array[$pos] contain for both instances?
    It contains a string "en-GB".

  14. #14
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,069
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Did you check the return result of $stmt->bindParam()?

    example:
    PHP Code:
    $resultOfBindParam $stmt->bindParam($i$execute_array[$pos], PDO::PARAM_STR);
    var_dump($resultOfBindParam);
    $resultofBindParam $stmt->bindParam($i$execute_array[$pos], PDO::PARAM_INT);
    var_dump($resultOfBindParam); 
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  15. #15
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    If i run the following (notice that i have set it to PDO::PARAM_STR) even when it see's that the sent parameter is an integer it seems to run fine.

    Code:
    $execute_array[$pos] = 1;
    				
    echo "Parameter = ".$execute_array[$pos]."<br>";
    				
    if(is_int($execute_array[$pos])) {
            $param_type = PDO::PARAM_STR;
    	echo "int";
    }
    elseif(is_bool($execute_array[$pos])) {
            $param_type = PDO::PARAM_BOOL;
    	echo "bool";
    }
    elseif(is_null($execute_array[$pos])) {
            $param_type = PDO::PARAM_NULL;
    	echo "null";
    }
    elseif(is_string($execute_array[$pos])) {
            $param_type = PDO::PARAM_STR;
    	echo "string";
    	}
    else {
            $param_type = FALSE;
    	echo "false";
    }
    				
    $resultOfBindParam = $stmt->bindParam($i, $execute_array[$pos], $param_type); // (parameter to be bound, value to be bound, parameter type)
    var_dump($resultOfBindParam);
    I get the following output:

    Parameter = 1
    int

    boolean true






    Should i be getting "boolean true" here?

  16. #16
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,069
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Keep in mind, that PHP "1" is equivalent to 1, it type casts values, if you had a value of "G" and told it to use PARAM_INT, it would likely return FALSE instead of TRUE.

    I don't think you will ever get a FALSE when using PARAM_STR and providing a true integer value.
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes


Bookmarks

Posting Permissions

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