SQL truncation attack

May 13, 2014 by

The SQL Truncation vulnerability is a very interesting flaw in the database. The successful exploitation of this issue leads to user account compromise, as it means an attacker can access any users account with his own password. Sounds interesting!

First we will see why this issue occurs in the database. If the user input value is not validating for its length, then a truncation vulnerability can arise. If the MySQL is running in default mode, Administrator account as admin, the database column is limited to 20 characters.

What should you learn next?

What should you learn next?

From SOC Analyst to Secure Coder to Security Manager — our team of experts has 12 free training plans to help you hit your goals. Get your free copy now.

Now what's happening in the backend database? By default, MySQL will truncate longer strings than the defined maximum column width and only emit a warning. But those warnings are usually are seen only in the backend database, not by web applications, and are therefore not handled at all. MySQL does not compare strings in binary mode. By default, more relaxed comparison rules are used. One of these relaxations is that trailing space characters are ignored during the comparison. This means the string 'admin ' is still equal to the string 'admin' in the database. And therefore, the application will refuse to accept the new user. If the attacker provides 'admin ninja' and the application searches in the database for this user, and it can't find it because the username column name is limited to 20 characters and the attacker supplied 21 characters, the application will accept the new username and insert into the database. Due to the 20 character column length, the application will truncate the username and insert it as 'admin '. Now the table contains two admin users, 'admin' and 'admin '.

Now we are going to see a practical scenario of this attack. Recently a CTF challenge took place at http://ctf.notsosecure.com/ and the first issue was SQL Truncation for capturing the first flag.

We opened the URL and found a login page.

Our first attempt was to check for default credentials. We tried username as admin and password as admin and we successfully logged in.

What the heck happened? That was our reaction, but this is an online hosted challenge, so somebody already created this admin password. But our motive is there, that to gain access to admin with our credentials, it means we first have to create a user by registering into this application.

We logged out from the application and found the register link on that page.

So we registered a user from this form and then logged in into the application.

Now it shows a message that "You are not Admin". We need to compromise that admin account. The first thing we know is the default admin account exists, now we check for the username character limit, if there is any limit or not. We verify that the username with 20 characters is able to register. The application is accepting up to 20 characters, and rest of the characters are not accepted. So here we can perform the truncation attack. So again we try to register a user with username 'admin ninjasecurity', it is 33 characters and the password is pass@123

Here the application will accept up to 20 characters, and the rest of the characters, which are 'ninjasecurity', will be ignored. It will be inserted in the database as 'admin '.

Our user is successfully registered.

Now we try to login as admin with password pass@123 and Boom! We are logged in.

FREE role-guided training plans

FREE role-guided training plans

Get 12 cybersecurity training plans — one for each of the most common roles requested by employers.



Warlock works as a Information Security Professional. He has quite a few global certifications to his name such as CEH, CHFI, OSCP and ISO 27001 Lead Implementer. He has experience in penetration testing, social engineering, password cracking and malware obfuscation. He is also involved with various organizations to help them in strengthening the security of their applications and infrastructure.