[ALUG] Security nightmares in TZ: Part one SQL injection

[Andreas Tauscher]

Hello list!

I think this post will follow some others, but then on a blog.

Some days ago somebody asked me to have a look on his self coded CMS.
They guy was really proud about it. But now he might use on the
established ones or starting from scratch again:

So even without having an look on the code itself I simply started the
nice browser plugin "SQL inject me".

Only seconds later: Tata! I had a list of all usernames an passwords
nicely presented in the browser.

He did one of the biggest mistakes: Trusting the user. A later look on
the code was then a real horror show.
What else then the username and the password somebody should enter in
fields named Login and Password?
If he is an evil guy, he will enter there anything else but not an
username: Java script, HTML code and many other things.
SQL inject me is entering there SQL queries.
The code for the login form looked somehow like this:

        $username =  $_POST['username'];
        $password =  $_POST['password'];

        if ($username=='' || $password=='')
        {
                $error = "Enter Username and Password";
        }
        else
        {
          $password = md5($password);
          $sql = "SELECT username, password from users where
          username = '$username' and password = '$password'";
                $result = mysql_query($sql);
                $row = mysql_fetch_array($result))
                if ( $_POST['username'] = $row['username'] )
                {
		       $login = 1;
                }

As long somebody enters only a username and a password: Everything is
fine. But what happens if enter instead the username something like this:
' OR '1=1 as username and password?

The query looks then a little bit different:
SELECT username, password from users where username = '' OR '1=1' and
password = '' OR '1=1'";

This query is always true. It will return all users. So I'm logged in as
the last user. With an additional limit statement I try now all users
until I get an administrator. With a few lines script using nice stuff
like WWW::Mechanize this is only a question of seconds: This website is
under my control. Or should I add instead a limit statement maybe a drop
statement? Hope you have a backup of your database.

How to prevent such things?

1: Any input is evil! Don't trust any input. If it comes from the local
PC, the LAN or from the internet. Don't trust any input the user can
manipulate. No cookie, no browser string, no referer, nothing you can
trust. And not only the user might manipulate input. Maybe your since
1000 years not updated router is doing it (different story how to abuse
your old router to hijack you). Any input must be validated. Any
sensitive information usernames, passwords, session IDs and and and must
be encrypted. (This is then for later when we see SQL injection is fixed
and we use other injecions) For the username and the password this would
be at least: What is the minimum and the maximum length of the string?
Which characters are allowed in the string?

2: Don't deal with the database direct. In PHP don't use mysql use
mysqli or PDO. They support a nice feature available in MySQL since
version 4.1: prepared statements.
Prepared statements having two big benefits: Much faster, and SQL
injection is much more difficult.

Usage of prepared statements is easy:
// New connection to DB
$db = new mysqli(localhost, user, password, database);
// Prepare statement
$stmt = $db->prepare("SELECT username, password FROM users WHERE
username = ? and password = ?;");
// Replace the spaceholders
$stmt->bind_param(ss,$username,$password);
//execute query
$stmt->execute();

$db->prepare is handing over the query to MySQL. MySQL now verifies the
query if the syntax is ok, all tables existing ans so on. If yes the
statement is stored in a buffer and reference is returned. Here is the
reason why the queries are faster: The parsing of the query is done only
once when I prepare the statement.
With bind_params I replace now the ? placeholders. The ss is telling:
both are strings. Other possible values are i for integer, d for
double/float and b for blob.
Only the placehoder data is sent to the database and inserted in the
query. The ' I injected in the query will not longer change the query:
It is already parsed. The executed query now will be equal to:
SELECT username, password from users where username = '\' OR \'1=1' and
password = '\' OR \'1=1'";
I query if the user ' OR '1=1 exists.
But this does not mean you don't have to verify the input. It will not
prevent other injections.

To be continued....  Then we abuse the comment option of the CMS a
little bit to install some nice male ware on your PC.

Andreas