Mariadb/Mysql Password Exposure in Bash

How can we securely provide a password to mysql without exposing it to the world by just putting it directly in the command?

How to Provide a Password to mysql?

Recently, I was writing a small Bash program with a GUI (using zenity) that would prompt the user for their MariaDB credentials and then retrieve some information. Passing username and password to the mysql program looks like:

$ mysql -u user -h host -ppassword mydatabase -e 'select 1;'

(Note that there cannot be a space between -p and the beginning of the password.)

Now, this obviously works fine, except for two problems:

1. We cannot reuse the connection to the database, so we have to make a new connection every time we want to execute a query.
2. The password of user user is given in the command itself. This means that everyone on the same machine can list the processes and see the command-line arguments passed to mysql.

In this post, I want to talk about the second problem.

$ pgrep -af mysql
8046 mysql -u user -h 192.168.123.5 -px xxxxxxxxxxxxxxxxxxxxxxx mydatabase
$ ls -l /proc/8046/cmdline
-r--r--r-- 1 neftas neftas 0 16 feb 10:33 /proc/8046/cmdline
$ cat -et /proc/8046/cmdline
mysql^@-u^@stefan^@-h^@192.168.123.5^@-px^@xxxxxxxxxxxxxxxxxxxxxxx^@mydatabase^@

As we can see, mysql replaces the provided password with xes, but at the same time warns in man mysql that

Specifying a password on the command line should be considered insecure. You can use an option file to avoid giving the password on the command line.

The option file man mysql is talking about is either one of the configuration files located at $HOME/.my.cnf or /etc/my.cnf, or a custom one specified on the command line. So, one option would be to put the credentials in $HOME/.my.cnf before executing the program, stopping this issue cold in its tracks.

But let’s say that we want to dynamically connect to a database. Asking the user to provide credentials and then write them to a file would provide the same issues as passing it directly to the mysql command. The only difference would be that we now pass the sensitive information to different command:

$ printf '[client]\nhost=%s\nusername=%s\npassword=%s\n' \
    host username password > creds.tmp
$ mysql --defaults-extra-file=creds.tmp mydatabase

The Environment Solution

Although not mentioned in my version of man mysql (10.4 from 29 March 2019), the safest way, it turns out, is to set an environment variable named MYSQL_PWD. Why is this safer?

$ ls -l /proc/8046/environ
-r-------- 1 neftas neftas 0 16 feb 11:00 /proc/8046/environ

At least on Linux, environment variables are only readable by the user that started the process. This means other users (beware of root) cannot look at them.

To summarize:

declare -x MYSQL_PWD
IFS='|' read -r username MYSQL_PWD < <(zenity --password --username)
mysql -u "$username" -h host mydatabase

declare -x declares a variable that is going to be exported on assignment. This eliminates the need to export MYSQL_PWD explicitly somewhere down the road: when MYSQL_PWD gets a value assigned, it will export that value.

In the second line, I use process substitution (see Process Substitution in man bash) to provide a pipe-separated string to read that will split the string and assign the values to username and MYSQL_PWD.

Now that MYSQL_PWD is set, we no longer have to worry about providing the password directly to the mysql command.

Versions Used In This Post

This post was written using the following versions:

$ uname -a
Linux cirrus7 5.5.3-arch1-1 #1 SMP PREEMPT Tue, 11 Feb 2020 15:35:41 +0000 x86_64 GNU/Linux
$ mysql --version
mysql  Ver 15.1 Distrib 10.4.12-MariaDB, for Linux (x86_64) using readline 5.1
$ zenity --version
3.32.0