MYSQL 5 Truncation detail and Attack

Hi Guys,

I found an interesting behaviour to be discussed here, and This behaviour is quite an old bug found more than five years ago. I want to explain the MySQL Truncation Attack’s detail that could enable the integrity of the database impacted and to be used by the adversaries to bypass specific control based on the data in the database. I will use MySQL 5.0.67 to explain these bug details.

MySQL Truncation Attack

We know that when you create a column in the table that you can specify the maximum length of a varchar for example maximum 8 char will be like this varchar(8)

CREATE TABLE rio.tabletohack (
	user varchar(8) NULL,
	password varchar(100) NULL
)
ENGINE=InnoDB
DEFAULT CHARSET=latin1
COLLATE=latin1_swedish_ci;

MySQL version 5.0.67 with default configuration will automatically do truncation when the data inserted into the field is more than the defined length. The same version will also do data trim (whitespace removal) to the data that is being inserted.

Lets input data with below to initialize the table with the normal data

insert into tabletohack (user , password ) values ('admin','rio1234')

We can see the data has been populated with the data we just inserted

Now, lets input with data where the user value will be more than the specified field lenght which is 9. We will input with below SQL Query

insert into tabletohack (user , password ) values ('admin   9','riohacked12345')

I specify the value of the user = adminspacespacespace9. The total length of the value will be 9, with the spaces are being counted too. When we execute the above query, there is no error even though the length is over the required maximum length

But let see what happened to the actual data stored in the table

the value = “admin 9″ is truncated and the spaces after admin are also trimmed automatically. Let validate our inserted data with the below query

select * from tabletohack t where t.`user`='admin'

So with the above query where user=’admin’ (no space after admin) is giving you the result with all user with value admin including the admin that we inserted with traling spaces

Interestingly when we do query with below sample will also give you the same where MySQL will basically auto trim the trailing spaces after values (see highlighted)

select * from tabletohack t where t.`user`='admin    '

The Attack

With the above fatcs then basically we can have idea on how to exploit this behaviour. Let see a scenario below

A web application enables the user to sign up to the system to log in and purchase. A regular user will only be able to make purchases, and an admin user will have more functionality to approve the user’s purchase.

The signup process will be as below steps

1. The application will check if the user that is input by the user is exist in database or not. If exist then reject.
2. Pass No.1 then the application will store the user and password into database

So lets assume we want to register ourself with user name is equal to admin. when we input our user in the signup form with admin then system will do step number 1

With the above query, the user cannot sign up with a username is equal to admin since this username already exists in the database. With the above behaviour (auto truncate and trim), we can manipulate and bypass it.

To bypass the check at step 1 we can create user is equal = “admin 9” without quote. so the query would be like this below

select * from tabletohack t where t.`user`='admin   9'

with the username above, the query will not find that username exists in the database, as shown above, then the system will proceed to step No. 2, where user = “admin 9” will be stored in the database with the query below

insert into tabletohack (user , password ) values ('admin   9','bypassthecheck')

by exploiting the behaviour we have successfully created a new admin account in the database. let validate

So we can now login with user admin and the password that we specify during the signup process.