How to escape variables with PHP PEAR DB with bound placeholders

Posted February 1st, 2010 in PHP

A week ago I looked at how to escape a string for use with a SQL query using the PHP PEAR DB escapeSimple function. This is useful for embedding a variable directly into a SQL query string but it can be a lot easier to use bound placeholders instead and let PEAR DB do all the escaping.

Query string and data

The examples below use a query string and array of data as follows:

$query = "SELECT * FROM fruit WHERE name = ?";
$data = array('apple');

Note the ? at the end of the SQL query. This is the bound placeholder and will automatically be escaped by PEAR DB if required. It is therefore safe to pass it a string which has been passed from a web form or similar. In the above example if the word 'apple' contained any characters that required escaping (such as a single quote) then these will be escaped before the query is run.

Using prepare and execute

The first example uses a two step process to first prepare the query with the SQL string and then execute it by passing the data. This is useful if the same SQL query needs to be run multiple times.

$resource = $db->prepare($query);
$result = $db->execute($resource, $data);
while($row = $result->fetchRow()) {
    print_r($row);
}

The example above prepares the query and the resulting resource is then passed to the execute function. The data is retrieved with the fetchRow function and then echoed with print_r.

Cutting down the steps with query

The above can be simplified slightly with a single call to query instead of the two calls to prepare and execute:

$result = $db->query($query, $data);
while($row = $result->fetchRow()) {
    print_r($row);
}

getAll, getCol, getRow functions

There are several other functions which also execute SQL queries and can accept bound placeholders. Some of these are getAll, getCol and getRow:

$rows = $db->getAll($query, $data); // returns all rows
$col = $db->getCol($query, $data); // returns first column from first row only
$row = $db->getRow($query, $data); // returns first row only

Refer to the PEAR DB documentation for more examples and other functions.