Security SQL Injection

Security SQL Injection Hot

Tag it:

Articles Reviews Structured Query Language

Written by Phil Harrison

Friday, 02 February 2007

{mos_sb_discuss:29}

A common web application vulnerability is SQL injection, an attack very similar to XSS. The difference is that an SQL injection vulnerability exists whenever you use un-escaped data in an SQL query. (If these names were more consistent, XSS would probably be called HTML injection.)

The following example demonstrates an SQL injection vulnerability:

<?php
$hash = hash($_POST['password']);
$sql = "SELECT count(*)
FROM users
WHERE username = '{$_POST['username']}'
AND password = '$hash'";
mysql_query($sql)
?>

The problem is that without escaping the username, its value can manipulate the format of the SQL query. Because this particular vulnerability is so common, many attackers try usernames such as the following when trying to log into a target site:

dan' --

I often joke that this is my favorite username, because it allows access to the chris account without me having to know the password. This is what the SQL query becomes:

SELECT count(*)
FROM users
WHERE username = 'dan' --'
AND password = '...'";

Because two consecutive hyphens (--) indicate the beginning of an SQL comment, this query is identical to:

SELECT count(*)
FROM users
WHERE username = 'dan'

If a positive count suggests success, this allows an attacker to log into any account without having to know or guess the password.

Safeguarding your applications against SQL injection is primarily accomplished by escaping output:

<?php
$mysql = array( );
$hash = hash($_POST['password']);
$mysql['username'] =
mysql_real_escape_string($clean['username']);
$sql = "SELECT count(*)
FROM users
WHERE username = '{$mysql['username']}'
AND password = '$hash'";
$result = mysql_query($sql);
?>

However, this only assures that the data you escape is interpreted as data. You still need to filter data, because characters like the percent sign (%) have a special meaning in SQL, but they don't need to be escaped.

The best protection against SQL injection is the use of bound parameters. The following example demonstrates the use of bound parameters with PHP's PDO extension and an Oracle database:

<?php
$sql = $db->prepare('SELECT count(*)
FROM users
WHERE username = :username
AND password = :hash');
$sql->bindParam(':username',
$clean['username'],
PDO_PARAM_STRING,
32);
$sql->bindParam(':hash',
hash($_POST['password']),
PDO_PARAM_STRING,
32);
?>

User reviews

There are no user reviews for this item.

Add new review

Last Updated ( Saturday, 07 July 2007 )

< Prev		Next >

[ Back ]