PHP - SQL Injection

| Print |

 

PHP - SQL Injection
The second most 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:

chris' --

 

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 = 'chris' --'

AND    password = '...'";

 

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

SELECT count(*)

FROM   users

WHERE  username = 'chris'

 

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);

 

?>

 

Because bound parameters ensure that the data never enters a context where it can be considered anything but data (e.g., it's never misinterpreted), no escaping of the username and password is necessary.

 

Users' Comments (0)

Comment an article   Name   E-mail    Title Available characters: 600 Enter what you see:

No comment posted