Safety and Speed—Using Parameterized Prepared Statements

In previous articles (1 and 2), we discussed using PDO to do fast writes to the database. If you’re going to write a bunch of objects to the database, though, it’s wasteful to call prepare() for each one.

In this article, we’ll look at how to use parameterized statements to write a series of objects to the database and only call prepare() once.

Parameterized?

In the previous article, we put the values of the variables directly in the SQL of the prepare() statement. Here’s the code we used:

$password = 'SomePassword';
$userId = 12;

$table = $modx->getTableName('modUser');
$stmt = $modx->prepare("UPDATE " . $table . " SET `password`='" .
$password . "', `hash_class`='hashing.modMD5', `cachepwd`='',
`salt`='' WHERE id=" . $userId);
if (is_object($stmt) && $stmt instanceof PDOStatement) {
     if (! $stmt->execute()) {
        $modx->log(modX::LOG_LEVEL_ERROR, "Error executing statement: " . $stmt->queryString);
     }
} else {
    $modx->log(modX::LOG_LEVEL_ERROR, "Invalid PDOStatement");
}

Prepared statements are faster and safer. The values are escaped to make sure they will cause no harm. This prevents a number of possible hacking attacks when you use user-supplied data from the $_POST, $_REQUEST, or $_GET array. MySQL also pre-processes prepared statements to optimize them for efficient communication with the database.

It’s a shame, though, to call prepare() for every row you want to store in the database. Luckily, you don’t have to. You can use a “parameterized” statement, which is essentially a statement with variables in it that you can reset for each row you save. The prepare() method is called only once. It is escaped and optimized, and only the variables are updated for each write. Here’s an example using the user password code above:

$table = $modx->getTableName('modUser');
$updateStatement = $modx->prepare("UPDATE " . $table . "
    SET `password`=:password,
        `username`=:username,
        `hash_class`='hashing.modMD5',
        `cachepwd`='',
        `salt`=''
    WHERE id=:id");

$updateStatement->bindParam(':password', $password);
$updateStatement->bindParam(':username', $username);
$updateStatement->bindParam(':id', $id, PDO::PARAM_INT);

How It Works

The parts of the statement with colons in front of them are the parameters. Think of the colon like the dollar sign used for PHP variables. It tells the database code (e.g., MySQL) that they are variables (aka parameters). The three bindParam statements tell the database engine which PHP variable should be tied to each parameter. Notice that for the :id parameter, we’ve identified the variable as an integer. The default type is PDO::PARAM_STR—a string variable, so we don’t have to do this for strings, but we do for other kinds of variables. The most commonly used options are: PDO::PARAM_STR, PDO::PARAM_INT, PDO::PARAM_BOOL, and PDO::PARAM_NULL. The full list is here.

Also, notice that we’re not updating anyone’s id field. The id field only appears in the WHERE statement.

The Code

If we were just storing one user’s data, there would be less reason to bind the parameters. Now that we’ve created our prepared statement (in the code above), though, we can use it over and over by adding the following code below it. Assume that we have the user data in an array of arrays called $users, where each member is an array like this:

$users = array(
    array(
        'username' => 'SomeUserName',
        'password' => 'SomePassword',
        'id' => somenumber,
    ),
    /* etc. */
);

foreach ($user as $user) {
    $username = $user['username'],
    $password = $user['password'],
    $id = $user['id'];
    $updateStatement->execute();
}

Each time through the loop, the “variables” in the prepared statement (:password, :username, :id) are replaced with the values of the three bound variables. We used an array for the source of the data, but it could just as well have been a CSV file.

We didn’t use any of the sanity checks or the prefix we saw in my previous article. We don’t need the prefix because we’re not creating any users. There are no sanity checks because during development, you’d fix any problems with the MySQL statement and would be confident that it would always work. It would be a good idea, though to check the return value of $updateStatement->execute() in case a user didn’t exist or something else went wrong.

In the next article, we’ll look at how to do the same thing in a class, where the execution of the prepared statement might be in a different method than the prepared statement itself.

---

Bob Ray is the author of the MODX: The Official Guide and dozens of MODX Extras including QuickEmail, NewsPublisher, SiteCheck, GoRevo, Personalize, EZfaq, MyComponent and many more. His website is Bob’s Guides. It not only includes a plethora of MODX tutorials but there are some really great bread recipes there, as well.