Mysql charset Truncation vulnerability

By http://www.80sec.com/

We found that there is a interesting feature in mysql database,when you are
using utf8,gbk or

other charsets.This feature may make your application unsecure.

Stefen Esser shows some attack manners of mysql in his paper[1], in which he
issues the SQL

Column Truncation vulnerability.

The application is a forum where new users can register
The administrator's name is known e.g. 'admin'
MySQL is used in the default mode
There is no application restriction on the length of new user names
The database column username is limited to 16 characters

Although the application restrict the length of the username, we can bypass
it in the following

example:

<?php

    $user=$_REQUEST['user'];


    mysql_connect("localhost", "root", "") or
        die("Could not connect: " . mysql_error());
    mysql_select_db("test");

 mysql_query("SET names utf8");

    $result = mysql_query("SELECT * from test_user where user='$user'");

 if(trim($user)=='' or strlen($user)>20 ){
  die("Input user Invalid");
 }
 if(@mysql_fetch_array($result, MYSQL_NUM)) {
  die("already exist");
 }
    else {
  $sql="insert test_user values ('$user')";
  mysql_query($sql);
  echo "$user register OK!";
 }

    mysql_free_result($result);
?>


Read the code here:

    $result = mysql_query("SELECT * from test_user where user='$user'");

If the attacker input a username 'admin                      z', and the sql
will be like this:

SELECT * FROM user WHERE username='admin                      z'

And the application will check the length of username with the following
code:

if(trim($user)=='' or strlen($user)>20 ){
die("Input user Invalid");
}

The attack will failed because the length of the username
'admin                      z' is

greater then 20.

But it will not end here, attacker can input username 'admin0xc1zzz', and
the sql will be like

this:

SELECT * FROM user WHERE username='admin0xc1zzz'

This pass the application's logic,when the insert commond executes:

insert test_user values ('admin0xc1zzz')

because the table is created in charset utf8,the 0xc1 is not a valid utf8
character,it will be

striped,also all of the next characters will be striped too.Then the
attacker got a user

"admin";

As you see,when mysql works at utf8,the invalid data will be striped ,but
the webapplication

doesn't know this,it works at binaray.The difference between webapplication
and database make a

vulnerability.


Reference:

[1]
http://www.suspekt.org/2008/08/18/mysql-and-sql-column-truncation-vulnerabilities/