PDO Placeholders

To obtain the full benefit of prepared statements placeholders are used within the prepared statement.

 

Placeholders protect against SQL injection, since the data never gets inserted into the SQL query.

 

There are two types of placeholders: named and unnamed

 

The following list shows a comparison of 3 examples:

 

  • No placeholders - ripe for SQL Injection!
    • $statement = $dbh->("INSERT INTO riders (name, team, status) values ($name, $team, $status)");
  • Named placeholders
    • $statement = $dbh->("INSERT INTO riders (name, team, status) value (:name, :team, :status)");
    • the named placeholders are in the rightmost parentheses, and take the form of a colon : followed by the placeholder name without quotes
  • Unnamed placeholders
    • $statement = $dbh->("INSERT INTO riders (name, team, status) values (?, ?, ?);
    • the unnamed placeholders are in the rightmost parentheses, and take the form of a question mark ?

 

 

When using placeholders, the variables need to be bound (/associated) to the placeholders using the bindParam() method (binds a parameter to a variable) for named placeholders or the bindValue() method (binds a value to a parameter) for unnamed placeholders.

 

This example shows the named placeholders within the query on line 10, which is then used in the prepared statement on line 13 and bound to the variables on lines 16 & 17:

<?php
	ini_set('display_errors', '0');
	include_once('connect.php');
	include_once('error_handling.php');

	$team = 'Yamaha';
	$status = 'Factory';

	//Named placeholder, note colon before variable & no quotation marks
	$sqlQuery = "SELECT * from riders where team = :team AND status = :status";

	//now use the prepare method & assign to $statement to create a prepared statement
	$statement = $dbh->prepare($sqlQuery);

	//the variables now need to be bound (/associated) to the named placeholders
	$statement->bindParam(':team', $team, PDO::PARAM_STR);
	$statement->bindParam(':status', $status, PDO::PARAM_STR);

	try{
		$statement->execute();
	}
	catch(PDOException $error){
		echo errorHandling($error);
	}
	while($row = $statement->fetch(PDO::FETCH_ASSOC)) {
		echo $row['name'] . " rides a " . $row['team'] . " " . $row['status'] . " MotoGP bike!<br>";
	}
?>

Save & refresh browser:

Connected to the motogp database
Jorge Lorenzo rides a Yamaha Factory MotoGP bike!
Cal Crutchlow rides a Yamaha Factory MotoGP bike!
Valentino Rossi rides a Yamaha Factory MotoGP bike!

 

 

The same example this time showing unnamed placeholders within the query on line 10, which is then used in the prepared statement on line 13 and bound to the variables on lines 16 & 17:

<?php
	ini_set('display_errors', '0');
	include_once('connect.php');
	include_once('error_handling.php');

	$team = 'Yamaha';
	$status = 'Factory';

	//Named placeholder, note colon before variable & no quotation marks
	$sqlQuery = "SELECT * from riders where team = ? AND status = ?";

	//now use the prepare method & assign to $statement to create a prepared statement
	$statement = $dbh->prepare($sqlQuery);

	//the variables now need to be bound (/associated) to the unnamed placeholders
	$statement->bindValue('1', $team, PDO::PARAM_STR); //1st placeholder bound to $team
	$statement->bindValue('2', $status, PDO::PARAM_STR); //2nd placeholder bound to $status

	try{
		$statement->execute();
	}
	catch(PDOException $error){
		echo errorHandling($error);
	}
	while($row = $statement->fetch(PDO::FETCH_ASSOC)) {
		echo $row['name'] . " rides a " . $row['team'] . " " . $row['status'] . " MotoGP bike!<br>";
	}
?>

Save & refresh browser:

Connected to the motogp database
Jorge Lorenzo rides a Yamaha Factory MotoGP bike!
Cal Crutchlow rides a Yamaha Factory MotoGP bike!
Valentino Rossi rides a Yamaha Factory MotoGP bike!

Leave a Reply