SQL Truncation

SQL Injection. At the top in the OWASP Top 10 List.

I was going through some missions, and came across one with SQL Truncation vulnerability. It is an ignored vulnerability, and many have patched the vulnerability, but there are lots of websites which still have this vulnerability. Here I’m explaining you (ELI5) the basics of SQL Truncation and how the vulnerability is exploited.

Lets take an example of a website where a user can register himself with a username and password, and later login with the same username-password combination. Lets name this website pikachu.com

Whenever a user registers the username and password, using SQL they are stored in the table. For the table, there is some specific maximum-length for the username and password. Lets consider that the username and password should be max 20 characters. In the HTML form, the following would be given:

<td><label>Select an Username: </label></td>
<td align=”right”><input type=”text” name=”username” value=”” maxlength=”20″ /></td>

<td><label>Select a Password: </label></td>
<td align=”right”><input type=”text” name=”password” value=”” maxlength=”20″ /></td>

<td><label>Verify Password: </label></td>
<td align=”right”><input type=”text” name=”password” value=”” maxlength=”20″ /></td>


This enforces the user to have username-password of maximum length 20 characters only.

Now, suppose the user enters ‘pokemon’ as the username and some random password. It will be checked in the column of usernames whether a username ‘pokemon’ exists or not. If the username does not exist, the table will store ‘pokemon’ under the username column and the password for it in the password column. Here pokemon is the administrator of the website.

Now, we are the attackers and we want to login to that site with the username ‘pokemon’. Possible? Yeah, possible if it is vulnerable to SQL Truncation. The following scenario:

  • Use the add-on Web Developer (for Firefox) or something similar in your browser, to break the ‘maxlength=20’ barrier.
  • Create a new user ‘pokemon             b’, which exceeds 20 characters. After pokemon you need to have white spaces filling the 20 characters and then some random characters.
  • The application will search in the username column for ‘pokemon             b’, and doesn’t find any so will store it in the database with our password. But since the max limit is 20 characters, it will store only ‘pokemon             ‘ and since there are only white spaces, it becomes ‘pokemon’. If we provide just ‘pokemon   ‘ at the username registration, it will take only ‘pokemon’ as it truncates the white spaces – and hence we gave ‘pokemon             b’ where the trailing character ‘b’ will not let it truncate the white spaces.
  • Thus we inserted the user ‘pokemon’ into the database with our password, and now onward we can login with our own password and ‘pokemon’ username.
  • Whenever we use ‘pokemon’ as the username, now it will check the two different cells in the table with the same username, and will validate our credentials.

SQL Truncation is a type of SQL Injection, which is a low hanging fruit. If it is not properly patched in the application, can cause a severe damage to the application data.


(Reference: http://www.suspekt.org/2008/08/18/mysql-and-sql-column-truncation-vulnerabilities/)