Using prepared statements

Whether you're reading from, or writing to a database, using prepared statements are easy, convenient, and secure. So what are they?

If you look at an HTML file, you'll see that it's carrying both the content and instructions on how to show it. Commonly SQL queries are written the same way. The problem with that is that if you're not careful, you (or more annoyingly, someone else) can write content that gets mistaken for commands. In HTML, that ends up with broken layouts or cross-site scripting attacks, but with databases it can hand over all your data to attackers.

With HTML the only answer is to always escape your content so it can never be interpreted as HTML tags when you don't want it to. You can do that with your SQL too, by using your database's escaping functions (like mysql_real_escape_string()), but there's a better way, which is to use a prepared statement. With this method, your command and the content are sent along separate channels to the database, which means you never need to worry about things like SQL injection attacks.

Prepared statements are also faster if you're running the same query often, since your database won't need to interpret the command every time it's sent.

Look at the SQL statement in the example below. You can see that instead of putting the $title we want directly into the query, we put a placeholder instead, called :title, and then we "bind" the value we want to the placeholder. PDO then passes the SQL and the data to the database separately.

Here's a full example:

Values vs Params

Notice that in the examples, I've used bindValue(). If you've looked at the PDO manual, you may have wondered what the difference is between bindValue() and bindParam(). The answer is that bindParam() connects the variable by reference instead of just taking the value from the variable. This means if you change the variable, you change the SQL statement. It's pretty useful in a foreach loop if you're adding a lot of rows to your database, but it can also cause some pretty confusing errors if you don't realise what it's doing. My personal recommendation is to avoid surprises and always use bindValue().

WHERE … IN

Sadly PDO doesn't support arrays in prepared statements, for example:

SELECT * FROM my_table WHERE id IN (1, 5, 7);

To do this in PDO, you'll have to escape the values manually using the quote() method. Here's an example:

In the example above, we use array_map() to run the same method on every item in the input array, and return the result as a new array. The method is the quote() method of the PDO database object, which does the appropriate form of backslashing for the current database connection. It's really important that you use quote() instead of addslashes() because not all databases use quotes and backslashes the same way (or at all).

Alternatively…

The Doctrine DBAL is a small wrapper around the PDO interface. All the PDO methods will still work, but you get a few more on top, including some hugely time-saving ways to build and run insert, update and delete statements.

Installing the DBAL

First you'll need to install the Composer PHP package manager.

Now fetch the DBAL:

composer require doctrine/dbal

That'll download the latest Doctrine DBAL to the vendor/ folder, and create an autoloader script. Composer's brilliant, by the way, and you can use its autoloader for your own code too, as well as the hundreds of packages on Packagist.

Getting a DBAL connection

The DBAL's connection object acts just like a PDO connection, but first you have to get one, and that's not quite the same:

<php
require __DIR__.'/vendor/autoload.php';

$config = new \Doctrine\DBAL\Configuration();
$connectionParams = [
    'dbname'   => 'mydb',
    'user'     => 'myusername',
    'password' => 'lovesexsecret',
    'host'     => 'localhost',
    'driver'   => 'pdo_mysql',
    'encoding' => 'utf8',
];
$conn = \Doctrine\DBAL\DriverManager::getConnection($connectionParams, $config);

Alternatively, if you already have a PDO connection you can just hand it to DBAL:

<php
require __DIR__.'/vendor/autoload.php';

$config = new \Doctrine\DBAL\Configuration();
$conn = \Doctrine\DBAL\DriverManager::getConnection(['pdo' => $db], $config);

Using the DBAL

As well as the usual PDO methods you're used to, the DBAL provides a few very handy shortcuts. For example, instead of calling ->prepare(), then ->bindValue() a bunch of times, and then ->execute(), you can use ->executeQuery(). This is equivalent to the full example above:

$query = 'SELECT * FROM my_table WHERE title = :title';
$stmt = $conn->executeQuery($query, ['title' => $myTitle]);
 
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  // …
}

An alternative to the WHERE…IN example above is also tidier:

$query = 'SELECT * FROM entries WHERE firstname IN (:names)';
$names = array('Rod', 'Jane', 'Freddy', 'Zippy', 'George', 'Bungle');

$stmt = $conn->executeQuery($query, ['names' => $names], ['names' => \Doctrine\DBAL\Connection::PARAM_STR_ARRAY]);

while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    // …
}

If your list items are all integers (e.g. you're retrieving results by id), you should use \Doctrine\DBAL\Connection::PARAM_INT_ARRAY instead.

Shortcut methods

// Equivalent to "DELETE FROM mytable WHERE id = 1"
$conn->delete('mytable', ['id' => 1]).

// Equivalent to "UPDATE mytable SET firstname = 'bob' WHERE id = 3"
$conn->update('mytable', ['firstname' => 'bob'], ['id' => 3]);

// Equivalent to "INSERT INTO mytable (firstname, lastname) VALUES ('bob', 'bobson')"
$conn->insert('mytable', ['firstname' => 'bob', 'lastname' => 'bobson']);

Transactions

Typical transaction code starts with you beginning a transaction, performing one or more queries, and then either committing the changes or rolling them back if there was an error. It's a common and tedious piece of code to write, so the DBAL can do it for you:

$conn->transactional(function($conn) use ($id) {
    $conn->delete('users', ['id' => $id]);
    $conn->insert('applog', [
        'created_at' => new \DateTime(),
        'message' => sprintf('Deleted user %d', $id),
    ]);
});

Disclaimer

It should go without saying, but any example code shown on this site is yours to use without obligation or warranty of any kind. As far as it's possible to do so, I release it into the public domain.