PDO Data Binding

Databinding and used in conjunction with SQL statements in PHP is not strictly just the domain of OOP PHP but given we are using PDO to connect to the DB which is the OOP method it makes sense to use best practice where ever possible. Hence why it is worth spending some time discussing the use of PDO Data Binding.

They are also a perfect companion to PHP’s try-catch protocol often used in conjunction with OOP methods.

What do PDO’s bindValue statements look like?

In PHP data binding is a way of telling the script how it expects the data to arrive in terms of the identifier and what columns the data is expected to fill.

$sql = 'SELECT support_queries.ID, support_queries.title, support_queries.content, support_queries.status FROM support_queries WHERE support_queries.ID = $id'; 

Here we are using a standard SQL statement where we are selecting a record with a standard PHP variable in the WHERE clause. This is ok if you trust all the data coming in – but who does! This particular example does not escape characters thus leading to a potential SQL injection…

$sql = 'SELECT support_queries.ID, support_queries.title, support_queries.content, support_queries.status	
FROM support_queries
WHERE support_queries.ID = :id';
$s = $pdo->prepare($sql);
$s->bindValue(':id', $reqRef);
$s->execute();

What’s happening?

In this example we are using PDO’s bindValue method. Within this all the escaping is done for you automatically thus leading to a much more secure query. Note how the WHERE parameter clause uses the bindValue variable – the same as example one but with a sanitized version.

Why do bindValue statements matter?

PDO bindValue statements matter because they are more secure and less vulnerable to SQL injection – they are a safer way of performing CRUD operations on a DB. It’s a bit like visiting a large building site. You’ll notice on the parameter fence lots of signs telling you to wear goggles, ear protectors, hard hat etc… Failure to wear any of those things will probably mean you will be refused entrance to the site and worse leave you dangerously exposed to injury!

In database terms bindValue statements perform something similar – they tell the script what’s needed to perform the query and if any of those requirements are not met the database operation will not be performed and the SQL query will be rejected.

So how can we use a prepared statement in our helpdesk app?

Well, let’s look at an example:

try
{
  $result = $pdo->query('SELECT ID, title FROM support_queries ORDER BY title ASC');
}
catch (PDOException $e)
{
  return false;
}

foreach ($result as $row)
{
  $requests[] = array('ID' => $row['ID'], 'title' => $row['title']);
}

if(isset($requests))
{
  return $requests;
}
return $requests;

What’s happening?

Here we are using a try-catch statement to insert a record into our helpdesk database. We TRY to insert the query and if we can’t we CATCH the exception thrown by the PDO object and handle the error.

We prepare our statement by using PDO’s binding method to sanitize our data and we also use MySQL’s built in CURRENT_TIMESTAMP method to record when the record was added.

What have we explored?

We have identified the difference between a normal raw SQL statement and a prepared statement. We have also discussed why we would use a prepared statement in favour of a raw one and demonstrated how this might work in our helpdesk application.