Starting to stop SQL injections, part 2#

In a previous posting, I gave an example of how to make database queries safer by using parameter binding and basically stopping SQL injections. The next step is to make the code more readable and maintainable. This doesn’t sound like a priority for secure software development, but readable code is also code that can be verified and maintained by other people. It gives you the edge to debug problems quickly and invites others to supply patches. So let’s take the example where the previous posting ended.

<?php

$sth = $dbh->prepare('select userid from accounts where username = ?');
$sth->execute(array($form_username));

For one or two parameters this may work, but when queries become bigger you need to start counting, and counting beyond three is a bad idea in most cases. Let’s change the question mark with a named variable called ‘:username’ in this example. One could then use the function bindParam() to specify which named variable needs to be replaced and has additional features, but in this example, we use the standard binding during in execute phase.

<?php

$sth = $dbh->prepare('select userid from accounts where username = :username');
$sth->execute(array(':username'=>$form_username));

Please remember to use a named variable only once in a SQL query as it will only be replaced one time and not multiple times.