SQL truncation vulnerability usually exists in MySQL databases. This vulnerability was first described in CVE-2008-4106, which was related to WordPress CMS.
How SQL Truncation Attacks Work
This attack works due to the truncation of user input in databases using the ‘selection’ and ‘insertion’ functions.
- When input is given in the form field, the ‘select’ function checks for redundancy corresponding to inputs in the database.
- After checking for redundancy, the ‘insertion’ function checks the length of the input, and the user input will truncate if the length exceeds.
Suppose that a developer creates the “users” table via the following query:
user_id INT NOT NULL AUTO_INCREMENT,
user_name VARCHAR(20) NOT NULL,
password VARCHAR(40) NOT NULL,
PRIMARY KEY ( user_id )
);
Using this schema, if the developer creates an admin account with the following:
password = “secret_p4ssw0ord”
Obviously, these credentials are not public. There is only one admin account in the database, and if an attacker tries to register another account with the ‘admin’ username, the attacker will fail due to the redundancy checks of the database. The attacker can still bypass that redundancy check to add another admin account by exploiting the SQL Truncation vulnerability. Suppose that the attacker registers another account with the following input:
(x are the spaces)
&
Password = ”RandomUser”
The database will take the ‘user_name’ (26 characters) and check whether this already exists. Then, the user_name input will be truncated, and ‘admin ’(‘admin’ with space) will be inputted in the database, resulting in two duplicate admin users.
The attacker is then able to create an ‘admin’ user with its own password. Now, the database has two admin ‘user_name’ entries, but with different passwords. The attacker can log in with the newly created credentials to get an admin panel because both user_names “admin” and “admin ” are equal for the database level. Now, we will look at a sample practical attack.
Sample Attack
In this example, we will take a scenario from the website overthewire.org. The overthewire community provides wargame CTFs on which we can practice our security concepts. The scenario of SQL truncation occurs in natas game Level 26->27. We can access the level using the following:
Username: natas27
Password: 55TBjpPZUUJgVP5b3BnbG6ON9uDPVzCJ
This level is available at: https://overthewire.org/wargames/natas/natas27.html. You will be shown a login page that is vulnerable to an SQL Truncation attack.
Upon inspecting the source code, you will see that the length of the username is 64, as shown below.
A user named ‘natas28’ already exists. Our goal is to create another user named ‘natas28’ using the SQL_truncation attack. So, we will input natas28, followed by 57 spaces and a random alphabet (in our case, a), username, and any password. The letter ‘a’ is not visible in the screenshot because of the 65-character length username. After the creation of the user account, you will be able to see the ‘a.’
If the database contains sql_truncation vulnerability, then the database should now have two ‘natas28’ usernames. One username will contain our password. Let us try to input the credentials on the login page.
Now, we are logged in as the ‘natas28’ user.
Mitigation
To mitigate this attack, we will need to consider multiple factors.
- We should not allow for the duplication of critical identities like the username. We should make these identities Primary Keys.
- The truncate function should be implemented for all fields of frontend forms, as well as backend code, so that databases receive truncated inputs.
- Strict mode should be enabled at the database level. Without strict mode enabled, databases only give warnings in the backend, but still save the duplicated data. With strict mode, databases give errors in case of duplication and avoid saving data.
For example, let us check for the strict mode using the following query:
We will create a database and the table ‘users.’
Query OK, 1 row affected (0.02 sec)
mysql> Use test
Database changed
mysql> CREATE TABLE users (username VARCHAR(10), password VARCHAR(10));
Query OK, 0 rows affected (0.05 sec)
Next, we will create an admin user with credentials using the INSERT query.
Query OK, 1 row affected (0.01 sec)
We can see the ‘users’ table information using the ‘select * from users’ option.
The username length is 10 characters. Now, we will try the SQL truncation attack.
When we try to input the following:
(x are the spaces)
&
Password = ‘pass2’
We will get an error, meaning that strict mode is totally effective.
ERROR 1406 (22001): Data too long for column ‘username’ at row 1
Without strict mode enabled, the database will output warnings, but will still insert the data in the table.
Conclusion
Attackers can obtain access to high-privilege accounts if the sql_trunction vulnerability exists in your application. The attacker can easily get information about a username and its database length using the critical fields, then create the same username, followed by spaces and random alphabet after the minimum length, resulting in the creation of multiple high-privilege accounts. This vulnerability is critical, but it can be avoided if you take some security precautions, such as activating strict mode for user inputs and making the sensitive field the Primary Key in the database.
