securing your PHP database access

The root cause of SQL injection is a failure to escape output. More specifically, it is when the distinction between the format of an SQL query and the data used by the SQL query is not carefully maintained. This is common in PHP apps that construct queries as follows:

<?php
$query = "SELECT *    FROM   users    WHERE  name = '{$_GET['name']}'";
?>

In this case, the value of $_GET[‘name’] is provided by another source, the user, but it is neither filtered nor escaped.

Escaping data prevents misinterpretation. By contrast, filtering ensures that data is valid before it’s used. The emphasis on filtering input is a reminder that data originating outside of your Web app cannot be trusted.

Assuming we’re using MySQL, the SQL injection vulnerability can be mitigated by escaping the name with mysql_real_escape_string(). The following example demonstrates filtering input and escaping output:

<?php
// Initialize arrays for filtered and escaped data, respectively.
$clean = array();
$sql = array();
// Filter the name. (For simplicity, we require alphabetic names.)
if(ctype_alpha($_GET['name'])) {    $clean['name'] = $_GET['name'];
} else {    // The name is invalid. Do something here.
}
// Escape the name.
$sql['name'] = mysql_real_escape_string($clean['name']);
// Construct the query.
$query = "SELECT *    FROM   users    WHERE  name = '{$sql['name']}'";
?>

Although the use of naming conventions can help you keep up with what has and hasn’t been filtered, as well as what has and hasn’t been escaped, a much better approach is to use prepared statements. With PDO, PHP developers have a universal API for data access that supports prepared statements, even if the underlying database does not.

Remember, SQL injection vulnerabilities exist when the distinction between the format of an SQL query and the data used by the SQL query is not carefully maintained. With prepared statements, you can push this responsibility to the database by providing the query format and data in distinct steps:

<?php
// Provide the query format.
$query = $db->prepare('SELECT *    FROM   users    WHERE  name = :name');
// Provide the query data and execute the query.
query->execute(array('name' => $clean['name']));
?>

Good luck, and enjoy.

Advertisements

About Cameron
I'm a final year Computer Science/Information Systems major. Already finished my BA in Politics/Philosophy. I do web and software freelance on the side, while I finish studying. Hoping to be self-employed by the end of my degree, otherwise off into the real-world I go....

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: