Using Parameterized Prepared Statements to Retrieve Data

Using prepared statements is good, but calling prepare() over and over in a loop is bad. Here's how to avoid it.


In previous articles, 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);

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 variables 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.

The Code

Now that we've created our prepared statement (in the code above), we can use it over and over as shown in the code below. Assume that we have the user data in an array of arrays called $user, 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 the "variables" in the prepared statement (:password, :username, :id) are replaced with the 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.

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.



Comments (0)


Please login to comment.

  (Login)