What is PDO and why should I use it?

Written by Evan Petersen on Sunday, 04 March 2012. Posted in Programming

Learn how to use the PDO object to execute queries

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. 

5.0/5 rating (1 votes)

About the Author

Evan Petersen

My name is Evan Petersen and I work as Chief Technology Officer at Dotcomjungle in Ashland, Oregon. You can visit the home page of my blog at: www.EvanPetersen.com.

I enjoy reserarching new methods to solve age old problems. Hopefully you'll find something of use!

Follow me on G+

Comments (3)

  • Alex

    Alex

    24 November 2012 at 15:36 |
    Hello from Barcelona (Spain), and escuse my pooor English

    I was usind AdoDB and PearDB for years, to get some abstraction layer over the database. And today I read about PDO. My question is: wich to use? Is it Adodb and Peardb obsolete after PDO? Or are Adodb and Peardb a higher abstraction layer?

    Thank you
  • Ceyda

    Ceyda

    04 May 2012 at 14:27 |
    on August 7, 2006of course, it isn't *that* weird that PDO is swleor. PDO is, as far as I can see it, more of a complete database abstraction layer than just an interface to mysql. mysql and mysqli are just focussing on mysql connectivity.Thus, when writing an application that is 100% guaranteed to always use mysql, using one of those interfaces will work better than PDO. But when you need the flexibility of a database abstraction layer, PDO will make your code much more solid and portable.
  • Alena

    Alena

    07 March 2012 at 22:54 |
    Cool blog!

Leave a comment

You are commenting as guest. Optional login below.