Friday, November 10, 2006

Avoiding SQL Injections

Since it first saw success as a powerful web development platform, PHP has suffered from the ease of use that bred that success. Inexperienced developers can all too easily build applications that are vulnerable to attack, and one of the most common vulnerabilities is the SQL injection.

From Kees Kodde of Qrios Web Design: “In most security related articles about web development, the threat of SQL injections is mentioned, and there seem to be a lot of ways to defend against this. What is, in your opinion, the most simple and effective way to filter possible SQL injections out of user input?”

The biggest challenge of defending against SQL injection attacks is understanding them, so let’s start with a simple example in PHP. This script fragment determines the price of a product given its ID as submitted by the browser:

$id = $_POST['id'];$sql = "SELECT price FROM
products WHERE id = $id";$result = mysqli_query($db, $sql);$row =
mysqli_fetch_row($result);$price = $row[0];


The problem here, as in most scripts vulnerable to SQL injection attacks, is that an assumption has been made about a value that is being received from the browser. The code assumes that the ‘id’ value sent by the browser will be a number, and can be placed into a string to form an SQL query like this:

SELECT price FROM products WHERE id = 123


But what if the ‘id’ value contains a maliciously-crafted string instead? When the value is placed into the string, it could instead form a query like this:

SELECT price FROM products WHERE id = 123 OR price
<>


That’s an SQL injection. In this example, it will fool the script into fetching a price less than 10 (assuming there is another product with such a price in the database) instead of the actual price. In other cases, SQL injections can be used to bypass password checks when logging into a site, and in some rare cases even modify the data stored in the database.

In general, the solution to SQL injection attacks is to enforce every assumption you make about any value that you insert into an SQL query. You can either do this manually, or use a pre-built library to do it for you. The above example could be modified to force the ‘id’ value to be interpreted as an integer:

$id = (int) $_POST['id'];


For numbers like this, you can force the language to convert values to numbers. For strings to be included in SQL queries, you need to use tools like PHP’s mysqli_escape_string function to convert special characters like quotes into a form that will not interfere with the query’s operation.But relying on yourself and your fellow developers to remember to enforce these rules for all browser-submitted data is problematic. Instead, you should use some library that will do it for you.

PHP5.1’s PHP Data Objects (PDO) API allows you to place values into SQL queries safely, specifying the expected data type.

$stmt = $db->prepare('SELECT price FROM products
WHERE id = :id');$stmt->bindValue(':id', $_POST['id'],
PDO::PARAM_INT);


So to answer your question, the simplest way to defend against SQL injection attacks is to avoid building your own SQL queries, and instead to use an API like PDO that will do it for you, safely. Indeed, PHP is one of the few languages where building SQL queries by combining strings is a common practice, and I’d say the prevalence of SQL injection attacks on PHP-based applications can be largely attributed to this.

No comments: