41

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.

2
  • 2
    Having your credentials in a file isn't that big of a problem. If a person has root privileges on your server, they can completely bypass the authentication system, just by restarting the mysql server with a particular option.
    – Zoredache
    Feb 6, 2013 at 22:44
  • This is not the same problem as in the suggedted duplicate. MySQL is not prompting for a password, I'm providing it and it's working fine. I'm looking for alternatives to provide the password, excluding a password file.
    – BenMorel
    Feb 6, 2013 at 23:53

4 Answers 4

21

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.

4
  • note: I do realize that mysql_config_editor does actually create an external password file, however, this is not done by you, its how the system works, so there is no manual en/de/crypting to be done by you. May 3, 2013 at 17:04
  • Thanks, I'm using MySQL 5.6 so this is not a problem. That said, your approach is still problematic (at least with how we currently work), because I want to take the password from a PHP config file in real time, and pass it dynamically to the command line. With what you suggest, I would still have to use the password on the command line when calling mysql_config_editor, so it doesn't bring much more value unfortunately. I'm also trying to avoid having the developer doing it manually, thus having to maintain both the PHP config file and the mysql config.
    – BenMorel
    May 3, 2013 at 17:19
  • My best solution is probably to ignore the warnings for now. I'm actually wondering if there's any security issue at all: because it's called from PHP, I guess the command line is not stored in the bash history or anywhere else on the machine?
    – BenMorel
    May 3, 2013 at 17:21
  • not to my knowledge, no, bash history doesnt store it. however, the password being in plaintext in the php config file carries the exact same risk, just in another form. maybe you will prefer to store the password using mysql_config_editor and store the login path in your php config file ? That way you wont expose your password anywhere. (but still will have to maintain the external password). May 3, 2013 at 17:43
9

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.

6
  • 1
    I'm not against the password file for security reasons, it's just that the MySQL credentials are part of a global configuration in the PHP application (used for PDO connection as well), and doing so would mean creating a (temporary) password file just for the sake of running the mysql command line for the lifetime of the script (a few seconds).
    – BenMorel
    Feb 6, 2013 at 23:50
  • How to do this on Windows Server 2012? Where is the config file that contains the --defaults-file option?
    – Jake
    Mar 12, 2013 at 13:27
  • You just specify the file as an option to --defaults-file as in: mysql --defaults-file c:\some\dirs\my.cnf
    – lsd
    Mar 12, 2013 at 18:36
  • @Benjamin, So if it's not about security, then simply type the password in the command line. What's wrong with doing that (besides security)?
    – Pacerier
    Jan 15, 2015 at 9:28
  • @Benjamin if you're using MySQL from PHP already, why are you forking the mysql console client then? Dec 9, 2015 at 17:17
6

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.

5
  • 1
    How would MYSQL_PWD be less secure then command line? The PW never shows up in the process list, which seems to be the primary concern
    – TheLQ
    Feb 9, 2013 at 10:53
  • 1
    Sure it does. man ps has -E Display the environment as well. From the url I linked to: 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.
    – R. S.
    Feb 9, 2013 at 19:37
  • Picking up from this MYSQL_PWD option: I guess that if you set the environment variable at the beginning of some script, then invoke MySQL command line, then clear that afterwards at the end of script, you could reduce at a minimum the time of exposure. Does that sound reasonable?
    – superjos
    May 20, 2014 at 22:06
  • @kormoc, Please elaborate on the last paragraph. What's that fairly complex solution you were talking about?
    – Pacerier
    Jan 15, 2015 at 9:31
  • 1
    It seems that using the environment variable is more secure than the command line. On a default debian system you can do ps and see the command line argument for every process of every user. But ps e only displays the environment for your own processes (unless you're root of course). It's only marginally more secure, but it still is more secure.
    – jlh
    May 14, 2018 at 5:53
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
  • I'm not sure how good that would work with quite big .sql files?
    – BenMorel
    Feb 7, 2013 at 10:03
  • 1
    @Benjamin I imagine no worse than the MySQL client would -- if you're really concerned about security though this would be the least hackish way to go about it, and size is a problem you can solve in a number of ways.
    – voretaq7
    Feb 7, 2013 at 16:51
  • Do you know if it's possible with PDO?
    – BenMorel
    Feb 7, 2013 at 17:45
  • PDO doesn't seem to have an equivalent function for throwing a bunch of queries at the database all at once. Sorry. It was an idea... Feb 7, 2013 at 17:47

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .