40

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.

BenMorel
  • 4,507
  • 10
  • 57
  • 85
  • 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 06 '13 at 22:44
  • This is not the same problem as in [the suggedted duplicate](http://serverfault.com/questions/56341/mysqldump-prompting-for-password-in-shellscript). 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 06 '13 at 23:53

4 Answers4

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.

BenMorel
  • 4,507
  • 10
  • 57
  • 85
Tuncay Göncüoğlu
  • 621
  • 4
  • 9
  • 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. – Tuncay Göncüoğlu May 03 '13 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 03 '13 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 03 '13 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). – Tuncay Göncüoğlu May 03 '13 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.

7ochem
  • 280
  • 1
  • 3
  • 12
lsd
  • 1,673
  • 10
  • 10
  • 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 06 '13 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 '13 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 '13 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 '15 at 09:28
  • @Benjamin if you're using MySQL from PHP already, why are you forking the `mysql` console client then? – Josip Rodin Dec 09 '15 at 17:17
  • You could use a local puppet apply command with the password stored encrypted in a hiera config file, which is then decrypted by the puppet module, written to the temp config file, run the mysql command, then remove the temp file. Or do that as a shell script, either way. – lsd Dec 09 '15 at 17:58
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.

R. S.
  • 1,714
  • 12
  • 19
  • 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 09 '13 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 09 '13 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 '14 at 22:06
  • @kormoc, Please elaborate on the last paragraph. What's that fairly complex solution you were talking about? – Pacerier Jan 15 '15 at 09: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 '18 at 05: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...

Michael Hampton
  • 244,070
  • 43
  • 506
  • 972
  • I'm not sure how good that would work with quite big .sql files? – BenMorel Feb 07 '13 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 07 '13 at 16:51
  • Do you know if it's possible with PDO? – BenMorel Feb 07 '13 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... – Michael Hampton Feb 07 '13 at 17:47