Home / server / Questions / 476228
In Process
BenMorel
Asked: 2013-02-07 13:48:45 +0800 CST

What's a secure alternative to using a MySQL password on the command line?

  • 772

We have a PHP command-line script to version a database. We run this script whenever a developer has added a new database patch.

The script runs the patch with the MySQL command-line:

system('mysql --user=xxx --password=xxx < patch.sql');

However, MySQL 5.6 now issues the following warning:

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

Which is obviously true, but might or might not be a problem for the user.

  • What's the secure alternative then?
  • Alternatively, is it possible to disable this warning?

Please note that I don't want to have to rely on an external password file.

mysql

4 Answers

  1. In the recent GA version of MySQL, ie, version 5.6, you can do this through the mysql_config_editor command, as described in http://dev.mysql.com/doc/refman/5.6/en/mysql-config-editor.html

    Basically what it does is: encrypt your user/pass credentials with an host alias, and then you use the host alias, put this information into a config file in your home directory, and then, when you need it, instead of doing something like:

    mysqldump -uroot --password=mycleartextpass mydatabase > dumpfile.sql

    you instead write:

    mysqldump --login-path=myhostalias mydatabase > dumpfile.sql

    thereby avoiding to put your password into some script in cleartext.

    For this to work, you first must (only once) define myhostalias as:

    mysql_config_editor set --login-path=myhostalias --host=mysqlhost.localnet.com --user=root --password

    You can use different login paths for different accounts and/or hosts as you like. Pretty good idea if you ask me.

    As a note, I believe, this functionality does NOT exist in any version below 5.6.

    • 21

  2. Use the --defaults-file or --defaults-extra-file option. You can specify user-id and password in it. It has the same format as /etc/my.cnf.

    Reading further, you say that you don't want to have to rely on an external password file, but that is the only really secure way. Anything else will leave traces in the process table or something. You can even put the password file in version control if you really want to. Make it 600 (or 400) and readable only by mysql or the user it is running under.

    • 9

  3. You have 4 options per http://dev.mysql.com/doc/refman/5.1/en/password-security-user.html

    • Use a -pyour_pass or --password=your_pass option on the command line
    • Use the -p or --password option on the command line with no password value specified. In this case, the client program solicits the password interactively:
    • Store your password in an option file.
    • Store your password in the MYSQL_PWD environment variable

    For your needs, MYSQL_PWD might be an option, but it's no more secure. Really you should spawn an interactive process with --password and submit the password interactively, but that's fairly complex of a solution for this problem.

    • 6

  4. If your PHP script already has an open database connection, why don't you just use mysqli_multi_query() to import the .sql file? If the syntax of the .sql file is valid, of course...

    • 4