PHP Hacks

Hack 35. Create Bulletproof Database Access

Learn how to use PEAR's DB module to create bulletproof database access for your web applications.

I've read a number of books on PHP over the years, and almost all of them make the same mistakes when it comes to database access. Applications that use SQL improperly are susceptible to SQL injection attacks, which can literally hand your entire database (and its contents) over to hackers. What's even worse is that the proper way to do database access is actually easier than the improper way.

To illustrate, Example 5-7 shows proper SQL command construction.

Example 5-7. Proper SQL command construction

<?php
require_once("DB.php");

$dsn = 'mysql://root:password@localhost/books';
$db =& DB::Connect( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage()); }

$sth = $db->prepare( "INSERT INTO author VALUES ( null, ? )" );
$db->execute( $sth, array( $_POST['name'] ) );
?>

I use the PEAR DB module to prepare a statement, with the ? placed where arguments are to go. Then I execute the statement against the database and provide an array of arguments that will fill in the ? fields. The driver performs all of the quoting and escaping required to ensure that the command runs properly regardless of the input.

Along the same lines, Example 5-8 is an example of a query performed against the database in the proper manner.

Example 5-8. An SQL SELECT statement that isn't going to cause any problems

<?php
require_once("DB.php");

$dsn = 'mysql://root:password@localhost/books';
$db =& DB::Connect( $dsn, array( ) );
if (PEAR::isError($db)) { die($db->getMessage( )); }

$res = $db->query( "SELECT * FROM author WHERE id = ?", array( $id ) );
while( $res->fetchInto( $row ) )
{
	…
}
	?>

In this case, the query method is called with an SQL string where the arguments are indicated with the ? character. The arguments are supplied using the second argument, which is always an array (regardless of the number of arguments supplied).

You might be saying to yourself, "If this is right, what is wrong?" I'm not going to put the wrong version in the book because readers might use that sample without first reading that the code is incorrect!

Some in the PHP community suggest that PEAR DB is slower. I haven't experienced that; and even if that were the case, I would still use PEAR DB because it provides portability and security features that the direct database access functions do not.

A new alternative to PEAR DB is on the horizon, as well; it's the PHP Data Objects (PDO) library. It's currently experimental, but it's worth monitoring in the long term as an alternative to PEAR DB. It's interesting to note that if you use the code generators provided in this chapter, you will be able to migrate between PEAR DB and PDO without modifying the application that sits on top of your database access layer.

5.3.1. See Also

• "Design Better SQL Schemas" [Hack #34]

Origami Paper AirPlane