The PHP elephant

PHP Data Objects

27 August 2020

Back in the bad old days of PHP we needed to interact with our databases with raw SQL queries through the crusty old mysql extension.

We would build queries out of conctenated strings, attempt to escape user input, then execute these queries in our database. And then we'd pray our code wouldn't allow the user input to be assessed as SQL. What could possibly go wrong?

Things, of course, did go wrong. SQL injection. Data breaches all around. Companies going under from the fallout from usernames and passwords getting into the hands of attackers.

PHP data objects to the rescue!

PHP data objects (PDO) allow us to:

Let's look at the code. You will need to know your database credentials (username password, hostname). It's preferable not to include these in your source code, and instead grab them from a .env environment file. It's out of the scope of this post to explain how to do this. Imagine we had grabbed our credentials and stored them in variables. Now let's establish a connection and store a reference to it in a variable:

$connection = new PDO("mysql:host=" . $myhostname . ";dbname=" . $mydbname, $mydbusername,$mydbpassword);

Nice. Now imagine that we're building a blog application and we have a "posts" table with three columns: an "id" primary key column, a"title" column, and a "content" column.

Let's create a prepared statement to get this data. This is will allow us to write a hardcoded query and specify parameters. These parameters will not be assessed as SQL:

$query = $connection->prepare("SELECT * FROM 'posts' WHERE id = :id;");

Now we haven't run the query yet, or passed it any VARIABLES. We've just prepared it. Let's give the statement some parameters, run the query, and grab some data!

$query->bindParam(':id', $id, PDO::PARAM_INT);

You'll notice the third argument in the function I passed in PARAM_INT. This makes sure the parameter sent to SQL is assessed as an integer (as IDs usually are). Otherwise it would add quote marks and build the query as a string. You can also make a prepared statement without having to name the parameters, by writing question marks in parts you want to be dynamic. You just need to pass it an array with a count matching the number of parameters.

$query = $connection->prepare("SELECT * FROM 'posts' WHERE id = ?;");
$query->bindParam([$id], PDO::PARAM_INT);
return $query->fetchAll();

What does this return to us? The default return is an array with both associative keys and integer keys:

echo JSON_ENCODE($query->fetchAll());
//{ "id":4,"title":"My title", "content":"My content","0":4,"1":"My title", "2":"My content"}

This is pretty weird. I'd probably usually prefer either an associative array or an std class object. Here's how we can get each of these instead:

return $query->fetchAll(PDO::FETCH_ASSOC);
return $query->fetchAll(PDO::FETCH_OBJ);

What if we want to write dynamically to different columns of the database by passing a key value pair to a function? Let's say we have a function with a $key argument. We can't use variables in the prepared statement, as these will be escaped into values. So we will have to go back to string concatenation:

$query = $connection->prepare("SELECT * FROM 'posts' WHERE {$key} = :value");
       $query->execute([':value' => $value]);
       return $query->fetchAll(PDO::FETCH_OBJ);

How do you keep this secure though? You could run a check to see whether the key matches a column name:

$query = "SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_NAME = 'posts';";
       $keys = [];
       foreach ($connection->query($query, PDO::FETCH_ASSOC) as $result) {
           array_push($keys, $result["COLUMN_NAME"]);
//This allows us to check for the existence of the key passed in
if (!in_array($key, $keys)) {
           throw new Exception("Keys do not exist in table");

So there you have it. A very quick intro to the PDO prepared statement. Go forth and make queries!

Back to blog