Samuel Sjöberg's weblog

Skip to navigation

Faking prepared statements

I played around with my database class today and rewrote my query method to use placeholder characters and a list of variables that pretty much mimics prepared statements. Even though the queries aren't prepared statements for real, the approach I'm taking is still useful because:

  1. It is garantueed that all values are properly escaped.
  2. It removes the need to care about quoting string values.
  3. It makes the queries (and code) easier to read

I always strip slashes on incoming data before doing anything with it. I disable magic_quotes_runtime and, depending on settings strip every possible array that could have been tampered with by the user (including $_SERVER). The consequence of this is that I need to properly escape all values that are used in database queries manually (or persisted somewhere). This approach is OK and safe, just as long as you remember doing it.

$sql = "INSERT INTO persons (birthday, name, city) VALUES ('".
        DB::escape($birthday) ."', '". DB::escape($name) ."',
        '". DB::escape($city) ."')";
$db->query($sql)

The snipped above demonstrates the hazzle... It is not enough to remember using the DB::escape method, I also need to enclose the value within the single quotes. Tedious work... this should be automated. What I want to do, is this:

$db->query("INSERT INTO persons (birthday, name, city) VALUES (?, ?, ?)",
        $birthday, $name, $city);

To implement this, I used variable arguments to avoid the need to wrap all values in arrays. The intersting parts of DB::query looks like this:

// If more than one argument, assume this is a "prepared statement".
if (func_num_args() > 1) {
    for ($i = 1, $l = func_num_args(); $i < $l; $i++) {
        $value = func_get_arg($i);
        $sql = $this->_prepare($sql, $value);
    }
}

And, not very suprisingly, the DB::_prepare method looks like this:

function _prepare($statement, $value) {
    $value = $this->escape($value);
    if (!is_numeric($value)) {
        $value = "'$value'";
    }
    return substr_replace($statement, $value, strpos($statement, '?'), 1);
}

That is the essentials of what I've been doing to ease my database interaction. I haven't had the chance to use it other than in a quick mockup, but I like the idea of having my data being secured in an automated way. Also, I can't help it, but I really like reading statements with placeholder characters that separates the SQL logic from the actual data being passed. Perhaphs I've been reading queries too much at work...

Pages linking to this entry

Pingback is enabled on all archived entries. Read more about pingback in the Pingback 1.0 Specification.

About this post

Created 12th February 2007 00:31 CET. Filed under PHP.

0 Comments
0 Pingbacks