Categories
Software Development

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 take the example where the previous posting ended.

$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. So let 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.

$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.

Categories
Software Development

Starting to stop SQL-injections

In a lot of PHP-examples strings are concatenated before a database query is being executed as below. Some examples advise to use PHP-functions mysql_real_escape_string() and/or addslashes() to make database query safe against SQL-injections. But this isn’t really a solution as when using addslashes() also requires the use of stripslashes() after retrieving data from a database. Some sites show the lack of proper implementation and show the famous \’ string on a website.

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

Like in Perl with DBI, also PHP has PDO that allows for variables to be parameterized while executing a query as in the example below. This removes the need for homemade solutions that don’t cover all use-cases and allows for a way to provide a stable and more secure interface for your applications when communicating with databases.

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

This doesn’t stop the need for sanitizing variables like with input from users.