David and Katherine Goodwin

Intro:

This article shows how to use the PEAR DB package for database abstraction and querying when using PHP

What is PEAR::DB?

PEAR::DB, provides a uniform, cross platform, cross database method for connecting to databases, when writing PHP applications/scripts. Extensive documentation can be found online here

This article aims to show briefly, how queries and updates can be performed when using PEAR DB.

What's wrong with the traditional PHP method(s)?

• Inconsistent API - mysql_connect, pg_connect etc
• Different APIs have different methods of connecting to a database
• Inconsistent methods for retrieving data and/or performing updates (pg_exec, pg_query, mysql_query etc)
• Difficult to port code between databases
• Not all APIs support all features (E.g. prepared statements)
• Inconsistent error handling (mysql_error(), pg_last_error())
• Secure/safe queries require effort (pg_escape_string(), mysql_real_escape_string(), addslashes(), magic_quotes_gpc())

Connecting to a database

PEAR DB provides a uniform consistent way of connecting to a database, through a JDBC like URL, e.g.

mysql://user:password@host/database, pgsql://user:password@host/database, sqlite:///path/to/file?mode=0600 etc.

<?php
// import PEAR DB libraries.
require_once("DB.php");
/**
 * Connects to database, returns PEAR DB Object.
 */
function connect() {
    $db =& DB::connect("pgsql://user:password@host/database");
    if(DB::isError($db)) {
        die($db->getMessage());
    }
    return $db;
}

The following page PEAR - introduction - connect covers this in more detail if required

Querying a database

In order to query a database, we could do the following :

/**
 * Executes given SQL on the database, returning a list of arrays
 * with the data within. e.g.
 * Array(
 *   Array('id' => 1, 'name' => 'john'),
 *   Array('id' => 2, 'name' => 'bob'),
 *   ....
 * );
 */
function execute_query($sql) {
    $conn = connect();
    $resultset = $conn->query($sql);
    if(PEAR::isError($resultset)) {
        die("Error: " . $resultset->getError());
    }
    $results = Array();

    while($row =& $resultset->fetchRow(DB_FETCHMODE_ASSOC)) {
        $results[] = $row;
    }
    $conn->disconnect();
    return $results;
}

And to use the above, you could undertake something like :

$results = execute_query("SELECT * FROM table WHERE x = '$y'");

The above is all very good, however, the callee is required to sanitise any strings they insert into the SQL statement (in our example '$y') before calling the function. This places an unnecessary complication on the code, and is one reason why there are continual SQL Injection attacks on PHP programs - namely the programmer has to remember to do something.

The PHP addslashes function can be used to stop SQL injection attacks, however it's apparently not infallible. For further information on SQL Injection attacks see this article at LWN.net

The best way to solve the above problems, is to use prepared statements, as shown below:

SQL Injection... Prepared Statements save the day

A prepared statement, is ideally compiled and stored in the database itself. Upon each call, placeholders are replaced with given parameter strings. This should lead to a performance improvement, as the database can avoid parsing the SQL on each execution.

Not all databases support prepared statements (notably older versions of MySQL. The PEAR DB library is thankfully intelligent enough to perform client side parsing, so we as a developer do not need to care about such limitations.

The below is an example of prepared statement usage

/**
 * Prepared statement SQL query function.
 * Expects $sql to have 0 or more placeholders ('?') 
 * which will be replaced by values given in the $value_array. Order is important.
 * @return a list of arrays (see above). Where each array represents a database row in an associative array.
 */
function execute_query($sql, $value_array = Array()){
    $conn = connect();
    if(sizeof($value_array) >0) {
        $stmt = $conn->prepare($sql);
        $res = $conn->execute($stmt, $value_array);
    }
    else {
        $res = $conn->query($sql);
    }

    if(PEAR::isError($res)) {
        // handle error ($res->getMessage())
    }
    $results = array();
    while ($row =& $res->fetchRow(DB_FETCHMODE_ASSOC)) {
        $results[] = $row;
    }
    disconnect($conn);
    return $results;
}

And a query might look like :

$results = execute_query("SELECT * FROM users WHERE name = ?", Array("david"));

Updating a database using prepared statements

Updating a database, is relatively similar to querying it, except that no results are returned (i.e. we're performing UPDATE, INSERT or DELETE).

function execute_update($sql,$value_array=Array()) {
    $conn = connect();
    if(sizeof($value_array) > 0) {
        $stmt = $conn->prepare($sql);
        $res = $conn->execute($stmt, $value_array);
    }
    else {
        $res = $conn->query($sql);
    }
    if(PEAR::isError($res)) {
        // handle error.
    }
    disconnect($conn);
}

And usage is just like :

execute_update("UPDATE users SET name = ? WHERE id = ?", Array("david", 2));

I use the above with PHP5 and exceptions, and wrap the code in try / catch blocks to detect error situations.

Technorati Tags: Documentation PHP SQL