What is PDO and why should I use it?

PDO is the latest and greatest from PHP used for accessing and querying databases.  It effectively replaces the older mysql_{} functions in an object oriented way.  So you might be wondering, “why should I care?”  Read on to find out…

Why should I care about PDO?

PDO is an object oriented class which allows users to connect and execute functions on various databases in an object oriented way.  The older and less secure mysql_ functions worked, but with consequences.  Using the function mysql_query to execute queries at the database level lead to major security flaws commonly referred to as SQL injection.  Using PDO, we can get around SQL injection by using “Prepared Statements”. 

Using Prepared Statements for Security and Brevity

Prepared statements are a way of letting the database know what we are going to request of it before we give actual parameters.  Imagine that we have a table of users and we want to fetch each user with the first name of Evan.  Using PDO, we would write something like this:

<?php
$preparedStmt = $db->prepare("SELECT * FROM users WHERE firstName = :name");
 ?>

Notice how we didn’t actually tell the database what name we will be looking for, but rather gave it a variable.  SQL injection relies on the database not understanding where a variable starts and ends… With a prepared statement, we tell the database what the query is, and where the variables are going to go.  So, how do we actually give the database the variables?

<?php
$name = "Evan";
$preparedStmt->bindParam(':name', $name, PDO::PARAM_STR);

Using bindParam(), we are able to set the :name variable in our prepared statement to reference the variable $name.  Keep in mind that it is referencing $name and not taking the value, meaning that the value will only be passed to the database once you execute the query.  If you would rather attach a value to the prepared statement than a reference, you can use a similar function called bindValue().

Executing the Query and Fetching Results

To execute the query, call execute() on your connection object.

<?php
$success = $preparedStmt->execute();

$success will be true on success and false on fail.  If for whatever reason the database balked at your query, you can call errorInfo() on your connection object ($preparedStatement) which will return an array of information about what went wrong.

<?php
If (!$success) {
     print_r($preparedStmt->errorInfo());
}

If, however, everything went well, how do we get the information that we queried for from the database?  PDO has two different methods, one which delivers everything at once, fetchAll(), and one that delivers information record by record, fetch().  Let’s take a look at how we might grab each record one by one from the database:

<?php
While ($record = $preparedStmt->fetch()) {
     Var_dump($record);
}

This will output everything the database knows about each user with the first name Evan. 

Take Away

Hopefully this article will push you to convert to the new PDO class for executing queries on a database.  For further information and other examples, refer to PHP’s online documentation: http://us.php.net/manual/en/book.pdo.php

Other Considerations

Keep in mind that SQL injection is not the only form of attack on a database that is worth combatting.  XSS, or cross site scripting is an equally disturbing attack.  If users are allowed to insert information into the database which will later be presented, then you are at risk.  If a user were to include some form of javascript, like

<script type="text/javascript">
     window.location = "http://www.myevilsite.tld/";
</script>

then users would be redirected to whatever site the user has specified.  Even worse, the attacker may embed some form of malware or keylogging application.  The moral of the story is don’t trust user input.  Ever. Always verify and sanitize input.