17 Years of Insecure MySQL Client !

Yes, this is a catchy title, but it is true, and it got you reading this post :-).  Another title could have been “Please load this mysql-dump: what could go wrong ?”.  As you guessed, loading a dump is not a risk-free operation.  In this post, I explain how the insecure MySQL client makes this operation risky and how to protect against it.

And if you think this post is not worth reading because you do not load dumps, consider the similarity with running any untrusted SQL scripts.  You might be doing this more often than you think (have you ever had to run a script to prepare MySQL for an upgrade of WordPress, Grafana, or any other software: these are untrusted SQL scripts).

This post is written in the context of my work on MySQL at Aiven: check our careers page or blog to know more about us.  The reason my attention got to the insecurity of the MySQL client is explained further down below.

Ok, so we have a SQL script to execute, what can go wrong ?  One thing that you might already have thought about is running unwanted MySQL commands (DROP DATABASE, CREATE USER, GRANT, SET GLOBAL, and others).  To protect against this, one should use the least amount of privileges for running such script.  If one needs to change the layout and load data in a Grafana MySQL instance (I am using Grafana as an example, it could be any other software using MySQL), it should use the Grafana MySQL user.  It would be a mistake to use the MySQL root user (if the software you are using needs this, you could open a bug for having this fixed).  The risks of using the MySQL root user are obvious to experienced DBAs: accessing and altering data that should not be visible to the Grafana user (on a shared MySQL instance as an example), or even opening a backdoor to MySQL by creating a new user.  These risks are well understood, and this mistake is not usually made.  But I have a more subtle risk in mind.

It is not known by all: there are break out features in the MySQL client (including the pager and the system commands).  These can be abused to execute any command on the host where the MySQL client is run (not on the MySQL Server itself).  I show examples of such abuses in Annex #1: Breaking-Out of the MySQL Client.

Ok, so the MySQL client can be used by an untrusted script to run arbitrary commands, how can we protect against this ?  One way is to use Ulimit to prevent the client from creating new processes (I show how to do this in Annex #2: Protecting Against Some Break Out from the MySQL Client).  In addition to this, the MySQL client should be run with a low privilege Linux user (do not use the root user), and additional hardening could be done with chroot and SELinux.

At Aiven, we recently used a different approach to Ulimit after realizing that the system command could be used in our environment.  Our solution is to recompile the MySQL client without this feature.  For that we simply commented / removed a line from the code (link to this line in MySQL 8.0.36 code).

It is disappointing that the MySQL client does not provide a mode where the break-out features are disabled.  Some other Linux commands, like less, provide a secure mode (see LESSSECURE in the documentation), the MySQL client should probably provide the same.  Also, it might be safer to have the secure mode enabled by default when the client is run in non-interactive mode.  For that, I opened a feature request: Bug#14328: Please provide a "secure mode" for the MySQL Client.  If you also think this is important, you can click the “Affects Me” or the “Subscribe” button.

But why did I mention “17 years of insecure MySQL client” in the title of this post ?  It is because in 2007, so 17 years ago, Sven Tantau created a feature request / contribution about this very precise subject: Bug#2694: mysql client - disable system commands via switch - patch included.  I think it did not receive the attention it deserved, hopefully my new feature request will.

That is all for today.  There is more to say about this subject, but I will stop here for now.

Annex #1: Breaking-Out of the MySQL Client

One way to break-out of the MySQL client is by using the pager command.  From my tests with MySQL 8.0.36 and 8.3.0, it only works in interactive mode.  In below, I use this command to create a file, but it can be used for more devious purposes.

$ rm -f a_file; mysql; ls -l a_file

> pager touch a_file; select 1; exit;
PAGER set to 'touch a_file'
1 row in set (0.00 sec)

Bye
-rw-r--r-- 1 jgagne jgagne 0 Apr 10 14:40 a_file

Another way to break-out of the MySQL client is by using the system command.  In below, like above, I use this command to create a file, but it is easy to be more creative.  The system command also working in non-interactive mode is more problematic: it allows an hostile script to run arbitrary commands.

f=a_file; rm -f $f; echo "system touch $f" | mysql; ls -l $f
-rw-r--r-- 1 jgagne jgagne 0 Apr 10 14:41 a_file

Annex #2: Protecting Against Some Break Out from the MySQL Client

To protect against some break-out from the MySQL client, you can create a wrapper script that disables creating sub-processes.  I call this script mysql_more_secure.sh, and its code is below.  The mysql_unsecure in this script is just the normal MySQL client which I hard-linked (ln mysql{,_unsecure}).  If you do not know about hard links and for this usage, it is enough to think of them as a copy of the binary (additional reading on hard links).

#!/bin/bash
ulimit -u 0
exec /path/to/mysql_unsecure "$@"

Note: this wrapper borks the MySQL client when using the pager command (at least for 8.0.36 and 8.3.0).

This solution is inspired by the Stack Overflow / Unix & Linux Stack Exchange article Block/allow specific subprocesses being launched by a process.  There was also a related question on Stack Overflow about "Making the system command unavailable in mysql", it did not have a good answer, so I added this wrapper script answer.