SQL Truncation is an exploit which arises due to the misconfiguration of databases. This vulnerability can potentially lead to compromise of user account privileges. This exploit can be performed on any web login page if the website also has a register page and if the vulnerability is present, of course. For the sake of this article, we are talking in context to MySQL database.

About the attack 

SQL Truncation is a vulnerability that threatens the primary application that uses the MySQL database server in its default configuration. The threat is based on the fact that if the stored string does not fit into a column with a specific text data type, it will crop and store the value in a truncated form. Together with the property of database systems that are commonly considered to have end-string and end-to-end values, the application may be unexpected.

Why does this attack work?

This attack works because of the nature of Select and Insert query of MySQL database.

How the SELECT query works:

Before passing data to the INSERT query, the SELECT query matches the username with the previous entries to reveal any redundant entries. For example, the username we entered is ‘admin           1’, the select query will not find any similar entry and pass the data to the insert query to do its job.

How the INSERT query stores data.

Talking about MySQL, all strings are truncated before being stored in the database. Also, there is a length restriction of 16 characters. So, ‘admin           ‘ (17 Characters) will be cut to 16 characters by removing the last digit. After passing the validation, the username is ‘admin           1’ (16 Characters) which will be stored as ‘admin’ because of truncation.

How the SELECT query fetches.

When we try to login with username = ‘admin’ and the password that we created, the select query successfully finds this pair and lets us login with admin privileges.

Mitigation

To mitigate this attack multiple things have to be considered:

1. Databases should not allow duplicate entries of critical fields like the username. Primary keys should be implemented.
2. The truncate function should be implemented on all fields even on the application front so that the database receives filtered data.
3. Database and Web forms should have the same character limits on corresponding fields.
4. Strict mode should be enabled on databases.