Preventing SQL injection in PHP/MySQL with PDO

This is a bit old but i wanted to add it here so it stays on record.

SQL injection is the action to inject SQL code in web forms to perform a site attack and disrupt its services. For a better definition, go read wikipedia here (it even has some code for you to test on your friend’s websites)

So read on for the mini tutorial/reference.

This is WRONG:

– Take a simple login form with a username and password input box.
– After you input your username/password and click the submit button, the request is sent to the server and a simple query is performed to check your entered data against what is on DB:

For example:

select * from users where username=’$u’ and password=’$p’

where $u is username and $p is password

A simple SQL injection would be using:

‘ or ‘1’=’1

as your username and password. If, on the server, the query is done using mysql_*, the resulting query would be:

select * from users where username=” or ‘1’=’1′ and password=” or ‘1’=’1′

which would list all the users on the “users” table. Not good. You could even add a few drop tables and really mess things up for the sysadmin.

So, to prevent this, we can use several methods, but today’s class will be only about using PDO (PHP data objects).

To make this a simple and small post, i will show you some sample code, explain it a bit and leave some links for you to read more.

Take this bad, bad code:

$sql = "SELECT * from users where username='$u' and password='$p'";
$res = mysql_query($sql)
if ($res) {
    $output = mysql_fetch_assoc($res);
} else {
    $saida = 0;
}

Delete it, and replace it by something like this to prevent injection:

// this is how PDO connects to the DB
$db = new PDO('mysql:host=localhost;dbname=[dbname_here];charset=utf8', '[username]', '[password]');

// preparing your query
$stmt = $db->prepare("SELECT * from users where username=:usern and password=:pw");

// adding parameters (several ways to do this, see links at the bottom of the post)
$stmt->bindValue(':usern', $u, PDO::PARAM_STR);
$stmt->bindValue(':pw', $p, PDO::PARAM_STR);

// execute the query
$stmt->execute();

// oooh, results
$output = $stmt->fetchAll(PDO::FETCH_ASSOC);

What PDO does, in a nutshell: the prepare method sends the query to the server where it is compiled with the placeholders for the arguments (:usern and :pw). the execute command sends the arguments to the server and runs the compiled statement. Since query and arguments are sent separately, no SQL can be sent on the arguments..so no SQL injection is possible.

Go read more here:

– PDO Tutorial for MySQL Developers (Great tutorial and reference)

– PDO documentation (php.net)

– Bobby Tables (xkcd little bobby tables or mom exploits)