More on the Adapter Pattern - PHP

The following are a few things to note about this implementation:

• It avoids having to manually call connect() and mysql_select_db().

• It throws exceptions on error. Exceptions are a new feature in PHP5. We won't discuss them much here, so you can safely ignore them for now, but the second half of Chapter 3, "Error Handling," is dedicated to that topic.

• It has not bought much convenience. You still have to escape all your data, which is annoying, and there is no way to easily reuse queries.

To address this third issue, you can augment the interface to allow for the wrapper to automatically escape any data you pass it. The easiest way to accomplish this is by providing an emulation of a prepared query. When you execute a query against a database, the raw SQL you pass in must be parsed into a form that the database understands internally. This step involves a certain amount of overhead, so many database systems attempt to cache these results. A user can prepare a query, which causes the database to parse the query and return some sort of resource that is tied to the parsed query representation. A feature that often goes hand-in-hand with this is bind SQL. Bind SQL allows you to parse a query with placeholders for where your variable data will go. Then you can bind parameters to the parsed version of the query prior to execution. On many database systems (notably Oracle), there is a significant performance benefit to using bind SQL.

Versions of MySQL prior to 4.1 do not provide a separate interface for users to prepare queries prior to execution or allow bind SQL. For us, though, passing all the variable data into the process separately provides a convenient place to intercept the variables and escape them before they are inserted into the query. An interface to the new MySQL 4.1 functionality is provided through Georg Richter's mysqli extension.

To accomplish this, you need to modify DB_Mysql to include a prepare method and DB_MysqlStatement to include bind and execute methods:

class DB_Mysql {
/* ... */
public function prepare($query) {
if(!$this->dbh) {
$this->connect();
}
return new DB_MysqlStatement($this->dbh, $query);
}  
}  
class DB_MysqlStatement {
public $result;
public $binds;
public $query;
public $dbh;
/* ... */
public function execute() {
$binds = func_get_args();
foreach($binds as $index => $name) {
$this->binds[$index + 1] = $name;
}
$cnt = count($binds);
$query = $this->query;
foreach ($this->binds as $ph => $pv) {
$query = str_replace(":$ph",

"'".mysql_escape_string($pv)."'", $query);
}
$this->result = mysql_query($query, $this->dbh);
if(!$this->result) {
throw new MysqlException;
}
return $this;
}
/* ... */
}

In this case, prepare()actually does almost nothing; it simply instantiates a new DB_MysqlStatement object with the query specified. The real work all happens in DB_MysqlStatement. If you have no bind parameters, you can just call this:

$dbh = new DB_Mysql("testuser", "testpass",

"localhost", "testdb");
$stmt = $dbh->prepare("SELECT * 
FROM users 
WHERE name =

'".mysql_escape_string($name)."'");
$stmt->execute();

The real benefit of using this wrapper class rather than using the native procedural calls comes when you want to bind parameters into your query. To do this, you can embed placeholders in your query, starting with :, which you can bind into at execution time:

$dbh = new DB_Mysql("testuser", "testpass",

"localhost", "testdb");
$stmt = $dbh->prepare("SELECT * FROM users WHERE

name = :1");
$stmt->execute($name);

The :1 in the query says that this is the location of the first bind variable. When you call the execute() method of $stmt, execute() parses its argument, assigns its first passed argument ($name) to be the first bind variable's value, escapes and quotes it, and then substitutes it for the first bind placeholder :1 in the query.

Even though this bind interface doesn't have the traditional performance benefits of a bind interface, it provides a convenient way to automatically escape all input to a query.