You may have heard that the old MySQL extension for PHP is going to eventually be deprecated in favor of the newer (and potentially more secure) MySQLi and PDO extensions. You’re going to need to update your old code sooner or later, so why not make it sooner?
I’m going to recommend PDO over MySQLi simply because it’s available on more systems, and it’s syntax may be a little bit easier to learn for newbies. PDO has been bundled with the main PHP distribution since PHP 5.1, and has been in PECL even longer, while MySQLi has only been included since 5.3. Whichever you use is up to personal preference and project requirements of course, but I will be sticking with PDO for the duration of this tutorial.
Suppose you have a simple bit of PHP that executes a MySQL query and ouputs a list of items to screen. It might look something like this:
$db = mysql_connect("localhost", "username", "password");
mysql_select_db("database", $db);
$string = mysql_real_escape_string($string, $db);
$query = "SELECT * FROM my_table WHERE item_cat='".$string."' ORDER BY item_date DESC LIMIT 5";
$result = mysql_query($query);
if ( mysql_num_rows($result) > 0 ) {
while ( $row = mysql_fetch_assoc($result) ) {
echo $row['item_title'] . '<br />';
}
}
You’re escaping any input from a third party with mysql_real_escape_string() to prevent injection attacks, I hope. SQL injection is one of the most common ways data is stolen or destroyed by attackers, and it’s also fairly easy to discourage. Escaping input, while not necessarily 100% effective, should prevent most injection attacks. (And it’s pretty much your only choice with the old MySQL extension.) A better solution is parameterized statements, which we’ll get to later.
If your server has PDO installed (which is probably the case if you have PHP 5.1 or greater), migrating is fairly easy. The basic principles are the same, though the syntax differs just a bit. It’s actually a bit cleaner and more object-oriented.
The example above would look something like this:
$db = new pdo("mysql:host=localhost;dbname=database_name", "username", "password");
$string = $db->quote($string);
$query = "SELECT * FROM my_table WHERE item_cat=$string ORDER BY item_date DESC LIMIT 5";
$result = $db->query($query);
if ($result != false) {
while ( $row = $result->fetch(PDO::FETCH_ASSOC) ) {
echo $row['item_title'] . '<br />';
}
}
$result = null;
Doesn’t that look nicer? Feel free to take a moment to appreciate the object-oriented goodness.
The biggest difference is probably the connection line. The “DSN” syntax used to connect to the database might look a bit strange at first, but it’s an important part of PDO. Since PDO can connect to other types of databases besides MySQL (e.g. PostgreSQL and SQLite), it uses a fairly standard connection string that specifies the server type besides the database name.
Escaping strings works essentially the same, but the syntax is slightly different. You need to remember to not put quotes around the variable in your SQL string, as the PDO::quote() method will do it for you. If you compare the SQL statements in the two examples, you’ll see the lack of quotations in the PDO example.
One gotcha to be aware of with PDO is that you need to set your $result variable to null if you intend to reuse it later on in the same script. You can end up with some weird results if you don’t. So just get in the habit of setting it to null or using unset() on it.
Now how about those parameterized statement things? They’re a way of ensuring that your code will be immune to SQL injection. Instead of mashing PHP strings together and passing the resulting query to the database engine, you keep the query and the potentially dangerous data separate. Placeholders are put in the query, and the data assigned to those placeholders is sent along with it.
$db = new pdo("mysql:host=localhost;dbname=database_name", "username", "password");
$sql = "SELECT * FROM my_table WHERE item_cat= :mystring ORDER BY item_date DESC LIMIT 5";
$statement = $db->prepare($sql);
$statement->execute(array(
':mystring' => $my_string
));
$result = $statement->fetchAll();
if ($result != false) {
while ( $row = $result->fetch(PDO::FETCH_ASSOC) ) {
echo $row['item_title'] . '<br />';
}
}
$result = null;
Parameterized queries may have some performance issues on MySQL versions prior to 5.1, but they shouldn’t have any significant disadvantages on more modern systems. Security-wise, they’re considered to be better than simply escaping strings.
I hope this little guide has been sufficient to get you started with PDO. You will probably want to check out the documentation to see what else it can do. While you don’t necessarily need to go out and update your old code right away—the PHP devs aren’t going to drop support for the old ext/mysql extension for quite a long time, if they ever get rid of it completely—but you should definitely familiarize yourself with the newer techniques and use them in new projects.
