Leet Sheet

SQL Truncation

It's an attack that no longer works in the latest MySQL versions.


  • MySQL database backend
A SQL Truncation attack takes advantage of two features in MySQL to subvert application logic. These features are:
  1. 1.
    If the length of a VARCHAR is exceeded, then the rest of it is simply cut off (truncated) - this is no longer a feature of MySQL.
  2. 2.
    By default, MySQL ignores trailing whitespace when making comparisons (MySQL Loose Comparisons)
Note: This attack doesn't work in newer MySQL versions and other databases like PostgreSQL because an error is thrown when the length of a VARCHAR is exceeded.

Attack Walkthrough

Let's say there's a website with a MySQL backend and a users table, where the username is of type VARCHAR(20). Let's also say that there is an admin account
Register the following user:
  • username: admin (lots of spaces in between)And then whatever
  • password: mypassword123
It produces the following SQL query:
INSERT INTO users (name, password) VALUES ('admin (lots of spaces in between)And then whatever', 'mypassword123');
Because of SQL truncation to 20 characters, the above is effectively the same as:
INSERT INTO users (name, password) VALUES ('admin ', 'mypassword123');
When you later try to log in with admin:mypassword123, then that produce the following query:
SELECT * FROM users WHERE username='admin ' AND password='mypassword123'
Because of MySQL loose comparisons, admin is the same as admin and you can log in as the admin user.