DEV Community

loading...

MySQL: Using a password on the command line interface can be insecure

jesseskinner profile image Jesse Skinner Originally published at codingwithjesse.com on ・2 min read

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:

  1. You can use a configuration file, either in ~/.my.cnf or in a file specified with the --defaults-file command line argument, or
  2. 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" "$@"
Enter fullscreen mode Exit fullscreen mode

to this:

#!/bin/bash

MYSQL_PWD="$MYSQL_PASSWORD" mysql -h "$MYSQL_HOSTNAME" -u "$MYSQL_USERNAME" "$MYSQL_DATABASE" "$@"
Enter fullscreen mode Exit fullscreen mode

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.


Interested in web development? Subscribe to the Coding with Jesse newsletter!

Discussion (2)

Collapse
darkain profile image
Vincent Milum Jr

Not sure about the current status of MySQL, but MariaDB supports passwordless local authentication now. You can use it to map a system user to an internal MariaDB user. No more passwords at all at that point to access the SQL CLI. It "just works", and is also the default on some MariaDB OS packages now.

Collapse
jesseskinner profile image
Jesse Skinner Author

Very cool, great to know, thanks!

Forem Open with the Forem app