Avoid the Original MySQL Extension, Part 2

In part one of this series we looked at what was wrong with the original MySQL API and how we can migrate to the newer, feature-rich MySQLi API. In this second part, we’ll be exploring the PDO extension to uncover some of the features it has to offer.

Alternative #2: PDO – PHP Data Objects

The PDO extension supports twelve drivers, enabling it to connect to a variety of relational databases without the developer having to learn a number of different APIs. It does this by abstracting the database interaction calls behind a common interface, enabling the developer to utilize a consistent interface for different databases. This of course gives it a major advantage over both the MySQL and MySQLi extensions who are limited to only one database.

PDO Basics

Connecting to a database through PDO is a quick and easy process where the required details are passed as arguments to PDO’s constructor. We then access the object’s public methods and properties to send queries and retrieve data.

<?php
$dsn = 'mysql:host=localhost;dbname=database_name';
$username = 'user';
$password = 'password';

$db = new PDO($dsn, $username, $password);

$numRows = $db->exec("INSERT INTO table VALUES ('val1', 'val2', 'val3')");

$result = $db->query('SELECT col1, col2, col3 FROM table', PDO::FETCH_ASSOC);
foreach ($result as $row) {
    echo "{$row['col1']} - {$row['col2']} - {$row['col3']}n";
}

$result->closeCursor();

unset($db);

A connection to the database is made first, where the DSN, username, and password are passed as arguments to the PDO class. A DSN is a connection string that will vary from database to database but usually contains the name of the database driver, host, database name, and sometimes the port number. Two basic queries are then performed in the example, the first using the exec() method and the second using the query() method. The important difference between these two methods is the successful return value; exec() should be used for non-selection statements (INSERT, UPDATE, or DELETE) and returns the number of affected rows, and query() should be used for selection statements and returns a result set object upon success. They both return false if there’s a failure. We can manipulate the manner in which the data is returned by passing a second argument to query(). The default is PDO::FETCH_BOTH which returns duplicated data in one array for each row; one will be associative data where the column name is the key, and column value as the value (PDO::FETCH_ASSOC), and another will be an integer indexed array (PDO::FETCH_NUM). Because this is not usually needed, it is suggested you specify an appropriate fetching mode to save resources. We then invoke the closeCursor() method to clean up the used resources stored inside of the $result object once they aren’t needed any more. Moreover, we unset our PDO object to free up any resources used when we know the script will not need to interact with the database any more.

Features of PDO

Like with the MySQLi API, a number of features have been introduced with the PDO extension that developers can take advantage of. Now that we’ve covered the absolute basics of using PDO, we can move on to looking at some of the features, including prepared statements, transactions, changing PDO’s default behaviour, and the ramifications of abstraction. Prepared Statements Like with MySQLi, PDO also supports prepared statements, whereby we bind parameters to our query to prevent injection attacks against the database. Prepared statements also enable client and server-side caching, which speeds up execution time when the same prepared query needs different values bound to it and executed. PDO does however have a couple advantages over MySQLi’s parametrized queries that are worth looking into. The first advantage is that PDO supports the usage of named parameters, giving us the ability to identify placeholders inside our queries by giving them meaningful names. This helps us keep track of parameters needing to be bound to a query when there are a large number involved, as opposed to having a sequence of unnamed (or positional) placeholders using question marks, which are dependent upon the order of binding. The second advantage is that we have the added flexibility of binding values to our prepared queries by using the bindValue() method. This enables us to bypass the limitation of only being able to bind variables and then being evaluated upon invoking the execute() method, as with MySQLi’s bind_param() method. Let’s take a closer look at how we can explicitly bind both variables and values to our prepared queries through an example.

<?php
$insQuery = $db->prepare('INSERT INTO table VALUES (:col1, :col2, :col3)');
$insQuery->bindParam('col1', $val1, PDO::PARAM_INT);
$insQuery->bindParam('col2', $val2, PDO::PARAM_STR);
$insQuery->bindParam('col3', $val3, PDO::PARAM_INT);
$insQuery->execute();

$selQuery = $db->prepare('SELECT col2, col3 FROM table WHERE col1 LIKE :val');
$selQuery->bindValue('val', "%{$val}%", PDO::PARAM_STR);
$result = $selQuery->execute();

while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    echo "{$row['col2']} - {$row['col3']}n";
}

Named placeholders begin with a colon, and then use the same naming conventions as variables in PHP. When binding our parameters to the prepared query, we must provide the bindParam() and bindValue() methods with at least two arguments, along with an optional third argument. The first argument is the name of the placeholder (which is case-sensitive), the second argument is the variable or value we want to bind to the query, and the optional third argument is the type to bind the variable/value as (the default is PDO::PARAM_STR, however I always specify the type for clarity). The bindParam() method also enables us to specify an optional fourth and fifth parameter, the data type length and any additional driver options respectively. The bindParam() and bindValue() methods are orthogonal, and so either or both can be used when binding values to a query. This is not the case with named and unnamed placeholders however, where only one or the other may be used on a single prepared query. Implicit binding is where we forgo using the bindParam() and bindValue() methods, and just pass the parameters to bind in an array format to the execute() method. If named placeholders are being used, then an associative array will need to be passed to execute(); if positional placeholders are used, then an indexed array can be passed to execute(). Here’s how we can use unnamed placeholders in the short-hand format of parametrized queries:

<?php
$updQuery = $db->prepare('UPDATE table SET col1 = ?, col2 = ? WHERE col3 = ?');
$updQuery->execute(array('val1', 'val2', 'val3'));

if ($updQuery->rowCount() !== 0) {
    echo 'Success';
}

We begin by preparing our query and putting the unnamed placeholders in position, and then invoke the execute() method with an array containing the values to be bound to our prepared query. This array being passed can contain either (or both) variables and strings. The downside to this short-hand method is that we aren’t able to specify the type of parameters being bound to our prepared query. Next we question if any rows were updated by using the return value from the rowCount() method, which will contain the number of rows affected from the previous operation. Provided the number of rows does not equal zero, then we consider it a success. The last type of binding we’ll look at with prepared queries is not for security purposes, but instead for data fetching. This is where we bind column names to variables using the bindColumn() method. The bindColumn() and bindParam()/bindValue() methods can all be used upon one prepared query, giving us flexibility in fetching data through assigning results directly to variables all while being immune to injection attacks.

<?php
$preQuery = $db->prepare('SELECT col2, col3 FROM table WHERE col1 = :val');
$preQuery->bindParam('val', $value, PDO::PARAM_STR);
$preQuery->execute();

$preQuery->bindColumn('col2', $OUTcol2);
$preQuery->bindColumn('col3', $OUTcol3);

while ($result = $preQuery->fetch(PDO::FETCH_BOUND)) {
    echo "{$OUTcol2} - {$OUTcol3}n";
}

We first prepare and bind a value to our query. This is then executed, and we invoke the bindColumn() method; the first parameter is the column name (which can be specified numerically), and the second parameter is the variable to bind the column’s value to. For this, I have used my own naming convention to help distinguish the variables I create (with known, safe values), from those containing values from the database. This helps me to know which ones may contain tainted data, and so will need to be escaped upon output to prevent XSS attacks. We then loop through the fetched data row-by-row, where the method of fetching (PDO::FETCH_BOUND) is assigning the values of the columns in our result set to the variables they were bound to above ($OUTcol2 and $OUTcol3). While the value of $result remains true (there are rows to loop through still), then the loop will continue to execute. The MySQLi API also provides the same functionality (with a similar syntax to the above) using the bind_result() method. Transactions PDO also supports transactions, however they’re created in a slightly different fashion to those in the MySQLi API. As stated in the previous article, transactions work upon ACID properties and are used to maintain data integrity (ACID) across multiple tables inside a relational database. If one of the statement’s fails during the execution, we are able to roll back all of the statements effects before the changes are permanently committed (ACID); this is due to the isolated nature of each transaction (ACID) before being considered successful. This is particularly important when statements are reliant upon one-another’s success, and so they must either all succeed or all fail (ACID). We can once again see transactions in form by performing a repeated insertion upon a table with a unique constraint key set upon one of the columns:

<?php
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

try
{
    $db->beginTransaction();

    $db->exec("INSERT INTO table VALUES (NULL, 'col1', 'col2')");
    $db->exec("INSERT INTO table VALUES (NULL, 'col1', 'col2')");

    $db->commit();
}
catch(PDOException $e) {
    $db->rollBack();
}

We must first set the error-handling behavior to throw exceptions when they’re caused. This is because PDO’s default error-handling settings do not trigger exceptions, which would mean that our catch block would never execute. To start a transaction, we invoke the beginTransaction() method, and then attempt to execute two destructive queries (where the data inside our database will be permanently modified using INSERT/UPDATE/DELETE statements). We then call upon the commit() method to attempt to commit the transaction and then to return the query processing back to auto-committing. The above would of course violate the data integrity rules set upon the table, causing the catch block to execute, and the transaction to be rolled back. This means no new data would be inserted into the database. Manipulating Default Behaviour Changing PDO’s default behavior can be done through the constructor method during class instantiation, or upon the pre-existing object created after class instantiation. By using PDO’s constructor method, we can change any number of settings by passing them in an array format as the fourth (optional) parameter. When wanting to manipulate the behavior of a pre-existing object, we can use the setAttribute() method.

<?php
$dsn = 'mysql:host=localhost;dbname=database_name';
$username = 'user';
$password = 'password';
$options = array(PDO::ATTR_PERSISTENT => TRUE,
                 PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC);
$pdo = new PDO($dsn, $username, $password, $options);

The above will enable persistent connections and change the default fetching mode from FETCH_BOTH to FETCH_ASSOC. The connection persistence setting is however among a minority of settings that must be changed before object creation in order to take affect. The default fetching behavior on the other hand is changeable upon a pre-existing object, enabling its settings to be changed at any time during the execution of a script. Changeable settings upon an object are made with the setAttribute() method, where the object’s default behavior is updated for the rest of its usage. We are also able to temporarily change some settings during method calls for some operations; one such example is with the prepare() method, which enables us to specify additional driver options when preparing a query (as an optional second argument). These together provide us with the extra flexibility in freely adjusting the behavior of our PDO object throughout the script. Changing the error handling settings is a common occurrence for wanting our PDO object to react to errors during script execution. This can be done either through the constructor or setAttribute() method, and comes in three modes: SILENT (default), WARNING, and EXCEPTION. While we are always able to view error information using the errorCode() and errorInfo() methods, the error reporting settings enable us to choose if errors encountered are either: completely silenced (PDO::ERRMODE_SILENT), raised if it’s a warning (PDO::ERRMODE_WARNING), or always thrown (PDO::ERRMODE_EXCEPTION).

Ramifications of Abstraction

There are however a few downsides to the abstraction layer that PDO provides. One of these is compatibility issues between each of the databases, where PDO must attempt to only use features that are available to all databases. One example of this can be seen with the PDO_MYSQL driver, where the default setting for queries is set to unbuffered due to it not being supported by other drivers. This means that for optimal usage of this abstraction layer, individual settings may need to be changed when switching from database to database. We must also be careful about what methods we choose to implement when using PDO. While much of the functionality PDO offers through its methods has been normalized to work on all of the supported databases, there are still methods that aren’t fully ported to all databases because they simply aren’t supported. One of these is the quote() method, which does not work for the PDO_ODBC driver. Generic methods should always be used in order to achieve portability. A further caveat to watch out for it writing incompatible SQL code. This is because PDO, although it has the ability to work with multiple databases, is not a query abstraction layer. One classic example of writing database-dependent SQL code is when using the backtick character, whereby it may be supported by your MySQL database, but not other databases that PDO can interact with. Other databases will have their own definitions for escaping invalid table and column names, such as PostgreSQL and Oracle which use double quotes, or Microsoft SQL Server which uses square brackets.

Closing Comments

In this article, we covered the basics of PDO and manipulating its default behaviour, along with exploring prepared statements for sanitizing input (with explicit and implicit binding), while demonstrating another use for them. We also looked at the creation of transactions and their respective behavior (described through ACID properties). There is however still much additional functionality to PDO that we didn’t have the chance to discuss in this article, so be sure to head over to the PHP.net manual for more information about PDO. That concludes our two part series of why we should avoid the MySQL API, and how we can avoid it by introducing two alternatives. I hope we can now better rid ourselves of the original MySQL API in favor of either MySQLi, or the more prominent PDO extension! Image via Fotolia

Frequently Asked Questions (FAQs) about MySQL Extensions