Bug #68034 warning on password with empty password
Submitted: 5 Jan 2013 17:58 Modified: 7 May 2013 13:02
Reporter: Giuseppe Maxia Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S4 (Feature request)
Version:5.6.9 OS:Any
Assigned to: Georgi Kodinov CPU Architecture:Any

[5 Jan 2013 17:58] Giuseppe Maxia
Description:
Using an empty password on the command line triggers a warning.

mysql --defaults-file=$SOMEPATH/my.sandbox.cnf --user=root --port=5000 -h 127.0.0.1 --password=''
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.9-rc MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

How to repeat:
Install a database server, with the default user.
Access using --root --password=''

See the warning

Suggested fix:
Remove the warning on empty passwords.
Even better, make an option that prevents the warning from showing up.
[6 Jan 2013 8:58] Shane Bester
Tempted to make this "Not a bug" or "Won't fix" but would like input from others.

There is no difference between specifying a password is empty, and a password that is not empty on the command line.  Either case reveals the password to a bash history/ps/task manager.

So warning is *legitimate* for any case that has password on the cmdline:

1.  mysql --no-defaults --password=''
2.  mysql --no-defaults --password=123123
3.  mysql --password=''
4.  mysql --password=123123
5.  mysql --defaults-file=/some/my.cnf --password=''
6.  mysql --defaults-file=/some/my.cnf --password=123123

So I take this is a minor feature request to disable the warning via some option? Since when do we make the client output static and suitable for screen scrapers anyway.  For special cases, just use an older client that doesn't have the warning.
[7 Jan 2013 11:41] Shane Bester
see also bug #66546
[7 Jan 2013 11:53] Giuseppe Maxia
The reason why this is a bug is that, when you want to set a password (first time you use the server), you must enter with root and no password. To make sure that the client does not pick a password from an option file, I add --password=''.
This procedure usually happens from a script. The extra warning is a nuisance that spoils the script output and raises unnecessary concerns during testing.

One more case: when I want to test that a use can access with a newly assigned password (again, using a test script), I need to use the password on the command line, because setting an option file for each user just for this test would be really inconvenient.

All the above come from a real test suite that used to run without warnings from MySQL 5.0 to 5.5, and now is full of these bogus warnings.
[9 Jan 2013 3:48] Todd Farmer
Just noting here (as well as previously noted in comments on Giuseppe's blog post) that there is a viable workaround.  Just as the use of --password='' leverages the fact that command-line arguments take priority over configuration files, one can define a connection using mysql_config_editor that has a blank password:

bin/mysql_config_editor set --login-path=no_pass --password

[press Enter when prompted for password]

Because the .mylogin.cnf file takes priority over configuration files, the following will log in with the root user and no password, even if a password is defined in something.cnf in a [client] section:

bin/mysql -uroot --login-path=no_pass --defaults-file=something.cnf

Because no password is passed on the command-line, no warning message is produced, and the root user is authenticated using no password.
[15 Mar 2013 16:43] Karl Nicoletti
Does the MySQL development team have ANY idea how many man-hours of work they have inflicted on DBAs and developers relying on command line input responses that DO NOT return the word "Warning" or "Error" in the case of successful execution???  I alone will be spending at least 100 hours to upgrade and test all our in-house maintenance, and installation scripts to work around this idiotic warning. Maybe, just MAYBE you could use the word "NOTE:" instead of "Warning:"??? Better yet, provide an option, --no-cmd-line-warning that would shut the damn thing off?
[10 Apr 2013 16:48] Sinisa Milivojevic
Having in mind all available solutions, the only option remaining is an additional command-line option that would prevent warning. Also, warning should go strictly to stderr, on *nix systems.
[5 May 2013 19:15] Giuseppe Maxia
Please notice that, while workarounds are available, this behavior breaks many applications that, until MySQL 5.6, did not expect warnings to appear for this case.
The correct fix should be to make the warning optional.
Less desirable (because of the existing applications that need to be updated) is an option that prevents the warning.

You should try this exercise, with a script that needs to work with previous MySQL versions and with MySQL 5.6. Would you be happy to have to maintain two versions of the script, with the aggravation that you can't automate the version detection using the MySQL CLI client, just because of this bug?
[7 May 2013 12:51] Georgi Kodinov
Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

This is a duplicate of bug #66564.

I hope you agree with me that from a pure security prospective pointing out weak spots is important. 

Giuseppe,

In your case you'll need to be collecting the new password from the user anyway. Why not either use --no-defaults or even let the mysql client ask for the current password ? 

On your second use case the result code from setting the password should be enough of an indication IMHO.
[7 May 2013 13:02] Giuseppe Maxia
Joro,
This bug does not seem to be a duplicate of  bug#66564.
Regarding your suggestion, one of the scripts that is broken by this behavior is using a customized defaults file and the purpose of the test was to make sure that using a password in the command line could override the password in the file. In this case there is really no workaround, other than altering the script to parse off the  warning.
The script must work with MySQL 5.0, 5.1, 5.5, and so on. If the world contained only MySQL 5.6, it would not be a problem
[9 May 2013 18:06] Todd Farmer
This is a duplicate of Bug#66546, and I'm copying my comment from that bug here for reference - please direct all further comments to bug#66546:

I actually think there is a widespread understanding that a number of scripts and tools handle passwords insecurely by passing them as plain-text arguments to MySQL clients, and that investment will need to be made to eliminate this insecure practice.  Generation of a warning (not an error; MySQL clients don't *refuse* connections when the password is supplied as a command-line argument) is an appropriate and limited first step to help solve this problem.  If such a warning had been in place initially, it's likely many of the affected scripts would have been developed differently.

That said, there are a handful of workarounds which can help users who don't have time immediately to fix such scripts:

1. Use pre-5.6 clients. Since the client issues the warning, not the server,
you can upgrade the server to 5.6 and retain your 5.5 clients.

2. Compile your own 5.6 clients from GPL-licensed Community sources, making modifications to suppress the warning.

3. Use 5.6 clients with the password placed in the defaults-file (either using mysql_config_editor or standard defaults files).

4. Set the MYSQL_PWD environment variable instead of passing it as an
argument to the client.  This is also very insecure, as it can be exposed
(via ps and other means) on some OSes, but it doesn't issue a warning (yet).
This is documented in our manual.

5. Use an authentication mechanism which doesn't rely on passwords, such as Socket Peer-Credential, PAM or Windows Auth.

6. Don't use a password at all.  If your environment is sufficiently secure that exposing passwords as plain text passwords isn't a concern, perhaps you don't need passwords at all to connect from said host.

Note I'm not advocating any such workarounds, although #3 and #5 are the best in my mind.  The purpose of the warning is to promote changes to insecure password handling practices.  Avoiding it by downgrading to a 5.5 client, or by using MYSQL_PWD environment variable, only deals with the symptom rather than the core problem.  

Introducing a proposed new switch to control whether the warning is generated to the user not masks the symptoms and allows the unsafe password handling to continue, but also won't be supported by multiple versions:

D:\mysql> bin\mysql -uroot -P13307 --skip-insecure-warning
bin\mysql: unknown option '--skip-insecure-warning'

Even if such a flag were introduced, it would be version-specific, and scripts would need to perform version checking before leveraging it (a key complaint above).