jump to navigation

PDO… Use It October 25, 2010

Posted by Tournas Dimitrios in PHP.

My previous article introduced the concept of accessing databases with an OOP-approach . Lets overview again this important subject .

The majority of PHP code I see, whether it be posted by a beginner on a forum, or built into a large application, seems to suggest that the current standard when accessing a database is to make use of whatever database specific commands PHP provides for your particular database (ie mysql_query, mssqli_query, etc). Often times, calls to these function are coupled with various attempts to prevent sql injections (such as calls to mysql_real_escape_string). Altogether, however, to me it seems clumsy and insecure.

PHP5 provies a built in (partial) database abstraction layer that can not only simplify the process of querying your database not only protecting against sql injections, but optimize your queries and make your application more portable: PDO (PHP Data Objects). A typical database transaction with PHP might look something like this:

mysql_connect('localhost', 'user', 'password');

$data = mysql_real_escape_string($_POST['data']);
$query = 'SELECT column FROM table WHERE data = \'' . $data . '\'';

$result = mysql_query($query);
while($row = mysql_fetch_array($result, FETCH_NUM))
     echo $row[0];

//Now with PDO

$dsn = 'mysql:dbname=myDB;host=';
try {
     $db = new PDO($dsn , 'user' , 'password');
catch(PDOException $e) {
     echo $e->getMessage();

$query = 'SELECT column FROM table WHERE data = ?';
$statement = $db->prepare($query);
$statement->bindParam(1 , $_POST['data']);

$rows = $statement->fetchAll(PDO::FETCH_NUM);

foreach($rows as $row)
     echo $row[0];

Outwardly, the second example might not seem like an improvement on the first. What is important, however, is what is going on underneath. From PHP.net:

Calling PDO::prepare() and PDOStatement::execute() for statements that will be issued multiple times with different parameter values optimizes the performance of your application by allowing the driver to negotiate client and/or server side caching of the query plan and meta information, and helps to prevent SQL injection attacks by eliminating the need to manually quote the parameters.

In addition to this, it simplifies the process of making your application compatible with another database. If you were to use database specific function throughout your application, you would need to change each to allow it to work with another database.

PDO, however, is not a complete database abstraction layer. While it does aid you in making your application portable, it will not emulate features of a particular database. If your queries are incompatible with your database PDO will not help you. Rarely, however, will it be the case that your queries are not compatible with most sql databases.

There are actually a few cases where where PDO will not be able to execute your queries (example: http://dev.mysql.com/doc/refman/5.0/en/c-api-prepared-statement-problems.html) but these cases are few and far between. Really it is rarely the case that you should NOT be using PDO.

Using PDO does not guarantee that user data is safe. You should always validate and sanitize user input, but PDO provides a decent extra line of defense.




No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s