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:

$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, $param1, PDO::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;
	}

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.

public function query($sql, $params) { 

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

} 

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.

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

$sql = "SELECT * FROM lang_homepage WHERE homepage_lang_id = ?";
$statement = $database->prepare($sql);
$statement->execute(array(5));

Using named parameters it would become

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

Thanks! I’ll leave them as they are in that case.

And another question :slight_smile:

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!

You’re doing it right. :slight_smile:

For UPDATE, you would likely substitute the VALUES and the WHERE clause params, same for DELETE. INSERT you would primarily only substitute the VALUES.

Good. Thanks!

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!

What value does $execute_array[$pos] contain for both instances?

It contains a string “en-GB”.

Did you check the return result of $stmt->bindParam()?

example:

$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);

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.


$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?

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.