MySQL: Using a password on the command line interface can be insecure
If you've ever tried to use the MySQL command line tool in some automated bash scripts or cron jobs, you'll probably wonder how to pass your password to MySQL without having to type it in. The most obvious choice would be to use the -p or --password command line arguments. If you've ever tried that, you'll probably have seen this warning message:
mysql: [Warning] Using a password on the command line interface can be insecure.
This is because any other users or programs running on that computer will have the opportunity to see the password, because process command line arguments are publicly available on Linux for some reason.
Today I learned a better way to do this. Actually I learned two ways from this Stack Overflow question:
- You can use a configuration file, either in
~/.my.cnfor in a file specified with the--defaults-filecommand line argument, or - You can use the MYSQL_PWD environment variable.
For me, #2 was the easiest solution, even though it's not the most secure option to use generally. MySQL documentation warns:
This method of specifying your MySQL password must be considered extremely insecure and should not be used. Some versions of ps include an option to display the environment of running processes. On some systems, if you set MYSQL_PWD, your password is exposed to any other user who runs ps. Even on systems without such a version of ps, it is unwise to assume that there are no other methods by which users can examine process environments.
If this sounds scary, go with the configuration file approach. I'm not worried about environment variables on my servers, and this other Stack Overflow thread, Is passing sensitive data through the process environment secure?, explains that this used to be a big concern, but nowadays the MySQL documentation warning is a bit over-the-top. These days, on most Linux systems, environment variables are a safe way to pass around secrets.
Since I was already using environment variables to store my database credentials, I decided to just change my bash script from this:
#!/bin/bash
mysql -h "$MYSQL_HOSTNAME" -u "$MYSQL_USERNAME" "$MYSQL_DATABASE" --password="$MYSQL_PASSWORD" "$@"to this:
#!/bin/bash
MYSQL_PWD="$MYSQL_PASSWORD" mysql -h "$MYSQL_HOSTNAME" -u "$MYSQL_USERNAME" "$MYSQL_DATABASE" "$@"Sure, I could've renamed my environment variable from MYSQL_PASSWORD to MYSQL_PWD, but I have other code relying on that, so this was the easiest way to eliminate that warning.