Securing PHP and MySQL From SQL Injection - Part 2

In the first article in this series I talked about how to protect PHP applications that used the MySQL API directly from SQL injection attacks. In this blog post I’ll show you how to migrate your MySQL API code to a database abstraction library like PDO. This move will give you both portability as well as some performance boost as you move to prepared statements.

Let’s start with some simple database access code using the MySQL API directly.

<?php
$sql = "SELECT * FROM user WHERE ";
$sql .= "user_id='"+$_GET['user']+"' ";
$sql .= "AND password='"+$_GET['password']+"'";
$resp = mysql_query( $sql );
?>

Ugh, ok, so the first thing to do here is to move it over to using the PDO library:

<?php
$stmt = $dbh->prepare("SELECT * FROM user WHERE user_id=? AND password=?");
$stmt->bindParam(1, $_GET['user']);
$stmt->bindParam(2, $_GET['password']);
$stmt->execute();
?>

See how much cleaner that is already? And this has given us not only database portability, but also SQL injection protection and database efficiency through prepared statements all in one shot.

Now we can do even a little better by using named parameters:

<?php
$stmt = $dbh->prepare("SELECT * FROM user WHERE user_id=:user AND password=:password");
$stmt->bindParam('user', $_GET['user']);
$stmt->bindParam('password', $_GET['password']);
$stmt->execute();
?>

This way it doesn’t matter what order the parameters go in the query, they always go into the right spots in the query when they are executed.

So not bad, huh? The PDO library makes database access easier to read and maintain, more efficient, less susceptible to SQL injection attacks and portable between database vendors. How cool is that!