Hi Guys,
I found an interesting behaviour to be discussed here, This behaviour is quite old bug that was found maybe more than 5 years ago. I would like to explain the detail of MySQL Truncation Attack that could enable the integrity of the database impacted and to be used by the adversaries to bypass certain control based on the data in the database. I will use mysql 5.0.67 to exlplain this 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 do truncation automatically when the data that is inserted into the field is more than the defined length. Also with 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 so the total lenght of the value will be 9 with the spaces are being counted too. When we execute the above query, there is no error eventhought the lenght is over the required maximum lenght

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
There is a web application that enable the user to signup to the system in order to login and purchase. Normal user will only be able to do purchase and admin user will have more functionality to approve purchase from user.
The signup process will be as below steps
- The application will check if the user that is input by the user is exist in database or not. If exist then reject.
- 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 the above query then basically the user cannot signup with username is equal to admin since this username is already exist 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 exist in the database as shown above then the system will proceed to step No. 2 where user = “admin 9” will be stored to 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.