MySQL and SQL Column Truncation Vulnerabilities

While SQL-Injection is one of the most discussed security problems in web applications other possible problems for SQL queries like overlong input are usually ignored although they can lead to all kinds of security problems.

This might be caused by the fact that security problems that are the result of overlong input are often buffer overflows and buffer overflows are something many web application security experts know nothing about and choose to ignore.

There are however several security problems for SQL queries that are caused by overlong input and no one talks about.

max_packet_size

In MySQL there exists a configuration option called max_packet_size which is set to one megabyte by default and controls the maximum size of a packet sent between the SQL client and server. When queries or result rows do not fit into a single packet a error is raised. This means an overlong SQL query is never sent to the server and therefore never executed.

This can lead to security problems when an attacker is able to supply long data elements that are then used in SQL queries. A good example are logging queries that combine information like the HTTP User-Agent, session ids and log messages into a large query that then does not fit into the packet anymore.

Another example from a real world application is a session table cleanup process that first selects all sessions matching certain parameters into a PHP array, then performs a multiple level cleanup and in the end all selected session ids are put into single delete query. It should be obvious that when there are many session identifiers in the table that need deletion the query gets too long. The result of this is that flooding the application with new sessions in a short time will result in no unused session being deleted later anymore.

Therefore web application developers should always ensure that they do not sent overlong data to the server. And it doesn’t matter if they use prepared statements or not.

SQL Column Truncation Vulnerabilities

When user input is not checked for its length SQL Column Truncation Vulnerabilities can arise. “SQL Column Truncation Vulnerability” is the name I use to describe security problems arising from overlong input that is truncated during insertion in the database. By default MySQL will truncate strings longer than the defined maximum column width and only emit a warning. Those warnings are usually not seen by web applications and therefore not handled at all. In MySQL the sql_mode STRICT_ALL_TABLES can be activated to turn these warnings into errors but applications will run most of the time on servers that run in the default mode and even if an application uses the stricter sql_mode it should not produce this error in the first place. Therefore a length check is required.

To understand why the truncation on insert can lead to security problems imagine the following application.

• The application is a forum where new users can register
• The administrator’s name is known e.g. ‘admin’
• MySQL is used in the default mode
• There is no application restriction on the length of new user names
• The database column username is limited to 16 characters

A potential attacker might now try to register the name ‘admin ‘, which will fail because the ‘isAlreadyRegistered’ check will result in the SQL query.

SELECT * FROM user WHERE username='admin '

Because MySQL does not compare strings in binary mode by default more relaxed comparison rules are used. One of these relaxations is that trailing space characters are ignored during the comparison. This means the string ‘admin ‘ is still equal to the string ‘admin’ in the database. And therefore the application will refuse to accept the new user.

If the attacker however tries the username ‘admin x’ the application will search for it in the database and will not find it, because it is impossible to find a username with a length of 17 in a database field that has a 16 character limit. The application will accept the new username and insert it into the database. However the username column is to short for the full name and therefore it is truncated and ‘admin ‘ is inserted into the database.

The result of this is that the user table now contains two users that due to trailing spaces both will be returned when the SELECT query above is executed. At this point a potential security problem arises because now it depends on how the username is treated throughout the application. The following pseudocode for example is vulnerable.

$userdata = null;
if (isPasswordCorrect($username, $password)) {
  $userdata = getUserDataByLogin($username);
  ...
}

When the previous piece of code uses the SQL query

SELECT username FROM users WHERE username = ? AND passhash = ?

to detect if the user password is correct and then does a lookup of the user data by name a security problem manifests.

SELECT * FROM users WHERE username = ?

Because the attacker created the newly created admin user he knows the correct password to pass this check. And because the real admin user is first in the table it will be returned first when the user data lookup by name is executed later.
by Stefan Esser