Adam Innes · Blog

Stop Gluing SQL Strings Together with PDO Prepared Statements

· 2 min · php, pdo, mysql, sql injection, security

Most PHP code I read that talks to MySQL still looks like this:

$id = $_GET['id'];
$result = mysql_query("SELECT id, title FROM posts WHERE author_id = $id");

That works right up until someone requests ?id=0 OR 1=1, or something far nastier. The traditional fix is to remember mysql_real_escape_string() on every value, remember to quote it, and remember that escaping does nothing for a number you forgot to put in quotes, because 0 OR 1=1 has no characters for it to escape. The PHP manual’s own page for that function includes an example of a login query beaten with ' OR ''='. Remembering is not a security strategy.

PDO has shipped with PHP since 5.1, so if you’re on 5.2 or 5.3 there’s a good chance it’s already there (look for pdo_mysql in phpinfo()). Prepared statements are the reason to use it.

The same query, done properly

try {
    $db = new PDO('mysql:host=localhost;dbname=blog', $user, $pass);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $stmt = $db->prepare('SELECT id, title FROM posts WHERE author_id = :author');
    $stmt->execute(array(':author' => $_GET['id']));
    $posts = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
    error_log($e->getMessage());
    // show a friendly error page here
}

The SQL and the data travel separately. You write the query once with a placeholder, and the value goes in through execute(). As the PDO manual on prepared statements puts it, parameters don’t need to be quoted because the driver handles it. You can use named placeholders like :author or plain ? markers; I prefer named ones once a query has more than two or three values.

The catch is that a placeholder has to stand in for a whole value. You can’t write LIKE '%?%'; you bind ? and pass "%$term%" as the value. You also can’t bind table names, column names or ORDER BY directions. If those come from user input, check them against a short whitelist in PHP before they go anywhere near the SQL string.

Turn on exceptions

By default PDO runs in PDO::ERRMODE_SILENT, which sets an error code and carries on. So a typo in your SQL gives you a false return value you probably didn’t check, an empty result, and no clue why. Setting PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION, as in the example above, makes every failed call throw a PDOException. The error handling page describes all three modes.

Wrap the connection in try as well. The constructor throws on a failed connection regardless of error mode, and the manual warns that if nobody catches it, the default backtrace can print your database username and password. Log the message, don’t echo it.

The charset gotcha

Here’s the one that bites people. The DSN parser in PDO_MYSQL accepts a charset option, so mysql:host=localhost;dbname=blog;charset=utf8 looks right and raises no error. But in PHP 5.3 the driver reads that value and then never uses it. There’s a feature request, bug #47802, open since March 2009 that points at exactly this in the driver source.

Until that changes, set the connection charset yourself when you connect:

$db = new PDO('mysql:host=localhost;dbname=blog', $user, $pass, array(
    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'
));

It’s also worth knowing that PDO_MYSQL emulates prepared statements by default. PDO fills in the placeholders on the PHP side and sends MySQL a finished query, still quoted by the driver, so you get the injection protection either way. If you’d rather have MySQL do real server side prepares, set PDO::ATTR_EMULATE_PREPARES to false.

Where to start

You don’t need to rewrite everything this week. Pick the queries that touch request data, starting with login, search and anything in an admin area, and move those first. New code gets PDO with exceptions on from day one. Once most of the old mysql_query calls are gone, grepping for the leftovers becomes a pretty good audit of where your remaining injection risk lives.

← all posts