PHP

Using PHP with PDO

Introduction

Often times during the development process of a dynamic web site, it will become necessary to pull information stored in a local or off-site database. Whether it is MySQL, SQLite, MSSQL, or some other variation of database technology, it is crucial to perform the necessary transactions in a safe and sanitized manner. In the past, it was acceptable to simply use PHP’s native database connection functionality to connect to a database and perform operations as needed- however, this method leaves many things to be desired, with security being at the top of the list.

What is PDO, and why should I use it?

The acronym “PDO” stands for PHP Data Objects, which is described as “a lean, consistent way to access databases”- more than that, it is a powerful, important tool in any PHP developer’s skillset. Using PDO comes with a number of advantages, including ease of query paramater binding, increased protection from SQL injection, and support for multiple database technologies right off the bat. Aside from all those important benefits, it’s simply easier to write! Read below to find out more.

 

Security

Consider the following PHP script, a simple connection to a database followed by a record insertion:

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('Theodore', 'Bellas', 'ted@webair.com')";
$conn->query($sql);

This method of querying your database is extremely insecure! Using simple SQL injection techniques, even an inexperienced attacker could potentially run an unauthorized query to manipulate your database in any way he/she sees fit- this could include finding administrator credentials, showing the database’s table structure, or even deleting all of the tables in the database. Obviously, this is a big problem- one solved by paramaterized or prepared query statements, a feature PDO provides. Using PDO, we can take our insecure, vulnerable query and turn it into something injection-proof:

//connect to MySQL database using PDO
$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
//prepare query statement
$stmt = $dbh->prepare("INSERT INTO MyGuests (firstname, lastname, email) 
                       VALUES (:firstname, :lastname, :email)");

//bind paramaters and execute statement
$stmt->execute(array("firstname"=>"Theodore", 
                     "lastname" => "Bellas", 
                     "email" => "ted@webair.com")
);

This query makes use of prepared statements, which send the database query and the paramaters used in the query separately, protecting your database from injection.

Versatility

With many different database technologies to choose from, the exact code required to make use of a specific DB type will often vary, sometimes wildly. PDO solves this problem by supporting many different database types, such as MS SQL Server, PostgreSQL, Firebird, Oracle, and of course MySQL/SQLite (and a full list of supported databases can be found here). The ease of use is demonstrated below:

//MS SQL Server and Sybase with PDO
 $DBH = new PDO("mssql:host=$host;dbname=$dbname, $user, $pass");
 $DBH = new PDO("sybase:host=$host;dbname=$dbname, $user, $pass");
 
//MySQL with PDO_MYSQL
 $DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
//SQLite Database
 $DBH = new PDO("sqlite:my/database/path/database.db");

 

Final Remarks

As you can see, the benefits of PDO make it the clear choice for making use of database applications. It is worth noting that while in this article I do not check to see if the database connections or queries are successful, it is imperative that you do in any production-level code- it could (and probably will) save you hours of debugging later.

Related Articles

  • How to List Compiled PHP Modules from the Command Line

    The general command is php -m; this command will give you the full list of extensions: php -m This command will give you an output like: bz2 calendar Core ctype...
  • Laravel 4 – CDN Token Authentication Helper

    For those of you using Laravel 4 and the Webair CDN with Token Authentication, I’ve written a handy little helper so that you can insert the tokenized CDN URL right...
  • LAMP STACK

    What is a LAMP Stack? “LAMP stack is a popular open source web platform commonly used to run dynamic web sites and servers. It includes Linux, Apache, MySQL, and PHP/Python/Perl...