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

Both problems described here are two new things web applications needs to be audited for because both can lead to real security problems. And because no one searches for these kind of vulnerabilities, now that it is public most probably the next weeks will bring several advisories about open source software suffering from these problems.

Setting Up a Database with the MySQL Tools

Today we will take a look at the different options you have when you want to create a new database. We are going to use the MySQL Tools we presented earlier here on the site. We will look at two approaches on creating and setting up a database:

   1. Using the MySQL Administrator
   2. Using the MySQL Query Browser

The two approaches are very different. Using the first option you don’t need any SQL knowledge at all, everything is done in an interface meanwhile the section option is all about SQL and writing your own scripts. Depending on your knowledge of writing SQL code you should pick the option best suited for you.

We start with option 1, creating and setting up a database using the MySQL Administration Tool. If you have read the post about the MySQL Tools you know that the software is available for free on the Internet. OK, we begin by starting the MySQL Administrator. The very first screen that pops up is about which server you want to connect to. If you are using your own WAMP server you simple add localhost as your hos, enter your username and password you supplied when you installed the MySQL Toolkit. I think default is username = root and password is set to nothing. When you are done, click OK. You might get another popup about an error, just click OK on that for now. Now we are up and running! If you are brand new to the MySQL Administrator I suggest you take a look around on the various menus and links so you feel familiar with the tool we are using. I will not go through each section here.

At the very bottom of the left menu section there is a link called ‘Catalogs’. Simple press that link once and another set of links will be reveal further down that section. Here you will find all your current databases found in your MySQL installation. information_schema, mysql and in some cases a test database is the default databases found in the list. To create a new database:

   1. Right click in the window listing the database.
   2. In the drop down menu, click Create New Schema.

You could also just hit Ctrl-N. A database schema name box will popup, enter a new and press OK. Now the list of databases should be updated with your newly created database. We continue by clicking on that database and the main window will be filled with an empty set of tables. To create a new table, simple click on the ‘Create Table’ button at the bottom of the screen. A new window will come in letting you enter your data about each table. If you have done a good job designing your database schema these next steps will be easy.

You start off by naming the table itself. When that is done, you double click on the top left corner of the big open white space, under the topic ‘Column Name’. Now we are able to add new columns. You are also able to reach this editing view by clicking at the tab ‘Column Details’ a few lines down. Under that tab we are able to set up everything we want: name, data type, primary key, not null, flags, comments, charsets and much more. You simple activate them by clicking or enter values into them. If you are using a InnoDB engine you are also able to apply foreign keys and stored procedures.

When you have assigned each table you are pretty much done. Your database is created and it’s filled with tables, still no data inside your tables though. One more important feature in the MySQL Administration tool that is useful and good to know is the Backup and Restore links in the main menu. It is very simple and straightforward to do backups and restore databases with the Administration toolkit. As soon as you are done and happy with your database to a backup,  try to restore it and learn it by heart. When your hard drive crashes down and everything disappears, it is very nice and calming to know that you do have a backup or backups.

Next up is option two: creating and setting up a database using the MySQL Query Browser. The setup of a database using the MySQL Query Browser requires a lot of SQL and MySQL syntax. I will not go through the syntax itself in such details, if you want to read more about SQL syntax click here. First we start with opening the MySQL Query Browser. The look of the window popping up is similar to the first popup we got with the MySQL Administration Tool. Enter your own settings and click OK. Next press the ‘File’ tab at the top of window and lookup and click on ‘New Script Tab’. The main window will change a bit and now we are able to enter SQL code to create our database.

We start with creating a database. Simply write:

    CREATE DATABASE databaseName;

To execute the line you look at your top right corner. There is a green button called ‘execute’, the one with a lightning on it. Press on that button and the code should execute and you should have a new database visible. Next up we can create a table inside our database by adding:

    CREATE TABLE test (id int auto_increment, name varchar(45), CONSTRAINT id_key PRIMARY KEY(id));

If you try to run this code you get an error. Database already exists. When writing scripts it is useful to use the DROP IF EXISTS statement. What this does is simple: We drop the database, table or method if it already exists. We can do this if we know we are re-creating the database, table or method later on. If we don’t want to test run our SQL code after each new line we write we don’t need this. Although, if we encounter any errors we have some problems. So, our final SQL script for creating a database and adding one table would look like this:

    DROP DATABASE IF EXISTS databaseName;
    CREATE DATABASE databaseName;
    USE databaseName;
    DROP TABLE IF EXISTS databaseTableName;
    CREATE TABLE databaseTableName(
    id int auto_increment,
    name varchar(45),
    CONSTRAINT id_key PRIMARY KEY(id)
    );

Explanation: We drop the database if it exists before we try to create a new one.  We name it databaseName, this is where you add your own name. We then use the keyword USE databaseName which tells the MySQL software that this is the database we want to use and work on. We drop the databaseTableName if it exists before we create a new table with the same name. We add two columns: id and name. Id is an int with a auto_increment flag and name is a varchar with the length of 45. Lastly we add a constraint by using the CONSTRAINT keyword. We supply the constraint with a  name, id_key, and the constraint type which in this case is a PRIMARY KEY and finally the column in the table this constraint should be applied to. Clicking on execute this will create a new database and a new table.

This last option with using SQL code to create databases and tables is good if we want to use PHP and modify tables in anyway. We can’t do that when we use the graphic interface the MySQL Administration tool provide. The SQL code can be added in the same way a SELECT, INSERT, UPDATE or DELETE statement is executed.

OK, we are done. I personally enjoy writing SQL code when I have the time. For me writing the SQL makes me more in control over the tables and I know what I’m doing, although it takes a lot more time then using the Administration tool. As for viewing a table’s or database’s schema I always use the Administration tool even though you can get the same information from the MySQL Query Browser by opening a new script tab, select your table and drag it into the scripting window and drop it. Although you will see a lot of other default SQL code added. Enjoy! Remember to check the SQL syntax if you are unsure before you start!