SQL Truncation

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

SQL truncation attack - Infosec ResourcesInfosec Resources

Overview

Prerequisites:

• MySQL database backend

A SQL Truncation attack takes advantage of two features in MySQL to subvert application logic. These features are:

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. 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.