MySQL Bugs: #66546: MySQL client now issue a warning if a password is given on the command line

Bug #66546	MySQL client now issue a warning if a password is given on the command line
Submitted:	27 Aug 2012 8:23	Modified:	6 Aug 2013 18:56
Reporter:	wei liu	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S4 (Feature request)
Version:	5.6.5	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	5.6.5 new feature warning password command line

Description:
Changes in MySQL 5.6.5 (2012-04-10, Milestone 8):
Security Fix: MySQL client programs now issue a warning if a password is given on the command line that this can be insecure.

Please can u provide a switch to enable/disable showing this warning?
I don't wanna get it because the endless warning will increase the size of the error log. And I do not think that it's an error to me.

How to repeat:
-bash-3.00# mysql -uroot -ppasswd -e "select 1\G" 1>/dev/null 2>err.log
-bash-3.00# more err.log
Warning: Using a password on the command line interface can be insecure.

Suggested fix:
Provide a parameter/switch that we can disable the new feature.

Thank you for the problem report.

And I need to hardcode password in my script.

Just curious : why is using mysql_config_editor to create and maintain a logins file holding your credentials (http://dev.mysql.com/doc/refman/5.6/en/mysql-config-editor.html) not an option for you ?

Note that even if your script uses multiple canned credentials according to some internal logic it still can do this using the new 'mysql' command line option --login-path.

I just suggested that can we have a switch to disable/enable the new feature to make user decide whether need it by themselves.
But I also can understand if set it to a force warning for security reasons.

I believe that we now have a better implementation (keep passwords separate from scripts). It's not functionally inferior to the previous implementation IMHO (feel free to re-open the bug if you can find a case where it lacks flexibility). So I think that not having an obvious way to disable it is a good insentive for users to consider the new implementation.

The warning is an issue when using scripts that deal with multiple versions of MySQL. 
Users don't update instantly to a new version (and MySQL 5.6 is not even GA yet) so there are applications that work for multiple versions of MySQl, and maintaining an extra script for MySQL 5.6 is just a hassle that we rather avoid.

Some applications won't work with this enabled. In my case, I wanted to use application, which will not be updated, but It is detecting problems at start:

Could not connect to the MySQL host service. 
Error details:
Warning: Using a password on the command line interface can be insecure.

Much like Mateusz Krzacze, we have a program which needs to run mysql. It's unfeasible to maintain a distinct version per mysql version. In order to provide useful feedback to customers when something goes wrong, we capture stderr along with stdout. If the 'show-warnings=FALSE' flag actually avoid showing warnings, this wouldn't be an issue.

This needs a config file option to suppress the warning.
A number of applications already use this existing functionality that has now been broken.

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?

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).

Here is a post-change cognos report.  It shows that p=this-is-a-test
searches are categorized as test searches (by association with test
environments), rather than as production searches.

*** GDFP Data backfill
    CLOCK: [2013-06-13 Thu 10:10]

I agree with several of the commenters: the "Warning: Using a password
on the command line interface can be insecure" is a nuisance, and the
lack of an option to disable it is a severe nuisance.

For mysql clients, I'd also say this warning is somewhat pointless.

Security is a game of risk mitigation, so let's take a step back and
assess the risk associated with using -pPASSWD on the mysql command
line.

The instinctual answer is "well, if your password appears on the
command line, then it can be seen in the output of ps".  But mysql
clients (at least as far back as 4.0) don't do that: they overwrite
argv[n] where n is any element of argv that contains a password
string.  Hence, ps shows an obfuscated password string, and not a
cleartext password.

As Todd Farmer commented, you can avoid the warnings by using
MYSQL_PWD instead of -pPASSWD.  Of course, the cleartext value of
MYSQL_PWD *can* be collected from ps.  In my opinion, that decision
was badly thought out.

I agree that there's merit to separating scripts and passwords, and
encrypting passwords that are stored on disk.  What about those of us
who already follow this practice?  (i.e., script reads encrypted
password from file, decrypts, then uses cleartext password on the
mysql command line.)  You've forced us to change from one form of
encrypted file storage to another form of encrypted file storage.
Lots of work?  Yes.  Improved security, no.

Because security is a balance between cost and risk, I'm disappointed
that Oracle feels the need to impose cost, without having any
knowledge of individual organization's risk profiles.  If an
organization's risk profile says "it's okay to give passwords on the
command line", then mysql should accept that.

Finally, I'm very disappointed in Oracle's lack of commitment to
maintaining backwards compatibility in their products.  (Several
people have commented on how "Using a password" warning has
introduced backwards compatibility issues).

(please ignore the first six lines of the preceding comment)

Steve,

I understand your frustration in having to address the new warning and am thankful for sharing it. 
Unfortunately, as you're pointing out yourself, the password can be retrieved from the ps output even with the tricks the mysql command line binary tries to do. So your custom encryption scheme is vulnerable to that when it needs to pass the decrypted password on the command line. 

So you need either an encryption scheme that mysql binary can understand or to reevaluate why do you need passwords for your scripting accounts to begin with.

Georgi,

You misunderstood Steve's point in his comment.  Because MySQL clients obfuscate the command line, a password passed in on the command line CANNOT be retrieved via ps (or, for that matter, via the /proc interface).

Todd Farmer pointed out in an earlier comment that a workaround is to use the environment variable MYSQL_PWD to pass the password to the MySQL client.  However, environment variables CAN be snooped by other processes.

So, to sum up, the command line argument interface for providing passwords is secure, while the environment variable interface for providing passwords is insecure.  What this warning is doing is encouraging people to move from a secure method of information-passing to an insecure one.

Steve's other point is that just because a password is passed in on the command line does not mean that it was stored plaintext in a script file, which is where the potential security issue is.

Please reopen this bug and revert this unnecessary warning.

And again: we are several DBA's that has to handle all GA versions of MySQL; making scripts work for several versions of of the client programs.

Please add functionality that can be used over all MySQL GA versions without too much hassle.

So, Bug is already 1 Year old and still no acceptable solution.

Todd Farmer described 6 possible workarounds, but none of them actually perfect.

In our case we have thousands of sites running on the servers, which are configured to create backups daily using mysqldump tool. And it turns into thousands of daily "spam" emails reporting that password usage in command line is insecure.

I was thinking to recompile all client tools with "warnings off", but this option is way off from ideal, because this way we can miss some real, valuable warnings.

So I'm offering 2 options:
1. Remove this warning completely (I really think that this warning is totally useless).
2. Provide a switch on/off functionality for this warning.

PS: I think that we can increase Severity of this bug taking into account how many systems are affected.

Please note that although the MySQL client obfuscates the password passed on the command-line, there is still a small time window between starting the process and when this step is completed.

During this time window the password can be read from the process list, and it is trivial to catch this with a ps|grep script.

After finding this topic I downloaded the MySQL Advanced or Enterprise and to my surprise I cannot use "show-warnings=false" to suppress that message after login through mysql client mentioning the password after -p option. What I've done was positioning that option in the [mysqld] section and restart mysqld.

Check it out below what happened:

[mysqld]
show-warnings=false
#
# skip settings
skip-external-locking
skip-name-resolve
skip-secure-auth=false

$ mysqld --user=root --console
[...]
Thread pool plugin started successfully with parameters:
thread_pool_size = 16,
thread_pool_algorithm = Low Concurrency Algorithm
thread_pool_stall_limit = 6
thread_pool_prio_kickup_timer = 1000
thread_pool_max_unused_threads = 0
thread_pool_high_priority_connection = 0
2013-09-01 18:37:39 8799 [ERROR] mysqld: unknown variable 'show-warnings=false'
2013-09-01 18:37:39 8799 [ERROR] Aborting
[...]

Any hint on that? My unique interest is to suppress that message which appears when you use the password on the command line after the -p option when using the mysql client. Even known that is a unsafe option...thanks a lot!

How about this insane idea....

How about MySQL quits trying to save the world from itself and REMOVE THIS STUPID WARNING MESSAGE. It's NONE OF YOUR BUSINESS if I want to use a password on the command line or not. So quit worrying about it. Remove the warning. We don't need to be nannied to death by the likes of you.

Is there any news on this. We knowingly use the password via cmd on our staging machines and get flooded with that message.

One more request. It's dumb this cannot be turned off (and the workarounds suck)

I have the same problem, I am evaluating mysql enterprise 5.6, change all of the command lines in the code uffff, please change medium priority to high.

Please fix this asap - ridiculous not to have a switch.

Please fix this asap - ridiculous not to have a switch.

any thing new about this Heavy Bug?
This warning fills my logfiles with very very much garbage!

What the hell should this senseless "Warning"???

I also need a password a cmdline (for Monitoring purposes) i know what i do and why and use special users for that, so there is no real security issue, nowhere .....

But i see only "Warning: using a pas....." in my monitoring logs an no real Warning or error, THATS a security Risk .. so Mysql is a security Risk with that senseless Warning, please do a very quick fix now, this BUG ist know over a year now!

Many People show that the Idea for other "solutions" to provide the password are more insecure than passing the password in cmdline, many COTS-Tools have a possibility to change there behavior and they provide the passwords at the cmdline!

This is not a feature request! This is a Bug Report! the developer build ist some senseless, harmful code! remove that immediatly pls!

Is there any new Feedback here?
Still the same error :/

$ mysql --version
mysql  Ver 14.14 Distrib 5.6.15-ndb-7.3.4, for debian6.0 (x86_64) using  EditLine wrapper

$ mysql -u <user> -h <ip> -p<pw>
Warning: Using a password on the command line interface can be insecure.

This warning has made some tools unusable . So I added a new option named "--skip-insecure-warning" to disable the warning.

The patch is simple but  a little rough, I'll attach the file later.

sorry , please ignore the attached file above

Attachment: bug66546.diff (application/octet-stream, text), 3.59 KiB.

update patch , add new option skip-insecure-warning for mysql client

Attachment: bug66546.diff (application/octet-stream, text), 3.07 KiB.

Thanks for the patch, zhai weixiang.
Frankly it's a dirty one and will need cleanup, but works.

To me, this new warning message is preventing mysql client to be used in Bash scripts at all.

Here we are, 12 releases later, and we STILL have this miserable nag. Get on the ball folks! We people, who work in the real world, need this nag removed ASAP!

For most uses cases, consider setting the password in the defaults ~/.my.cnf.  With this method the password can be stored with mode 600 thus only be readable by the user running the job.

[client]
user=root
host=localhost
password='redacedpassword'

If it is necessary to run more than one job running as the same user with different credentials, create a different defaults file for each job.

mysqldump --defaults-file=~/job1.cnf

I too would really like a .cnf param to turn this warning off. Thanks!

Imagine you're using monitis.com. You don't have the power to change how it generates the mysql command line. And you have this "feature". That actually prevents the monitoring widget from working. So I share the view this "feature" wasn't a big idea.

Here's a quick and VERY DIRTY fix to hack the binary to remove this message:

  perl -p -i -e 's/(Warning: Using a password on the command line interface can be insecure..)/"\0"x(length($1))/es' /usr/bin/mysql

Interestingly, changing just the first byte of the string to a null does not cause the string not to display.  Once patched, the binary displays a sequence of null characters, which are not visible in most interfaces.  This says that traditional null-terminated strings are not being used.

This will void your warranty.

Please provide the email address of the person who thought that implementing this message and making it impossible to disable was a good idea, I have about 10000 emails from cron that I need to forward on to this person.

This is a high-severity bug, not a feature request, which makes it impossible to determine which cron jobs are actually generating real, useful warnings, and which are just spitting out this useless, inaccurate drivel

Thank you Andrew McGill for your nasty hack, works well for me.

Here's a hack that works too:

printf '\x75' | dd of=mysql bs=1 seek=$((16#$(objdump --disassemble -F mysql |grep password_warning|grep je|awk '{print $1}'|cut -d: -f1|sed -e 's/^.//g'))) count=1 conv=notrunc

It patches the IF condition to just do a jump.

In reply to Todd's comment on 9 May 2013:

No, the problem is also the presumption that putting passwords in plain text on the command line is insecure, when this is not in fact true and the warning is explicit in saying it MAY be insecure, not that it is insecure. Your description of it being insecure without accepting the may word is part of the problem here.

Consider something as straightforward as a client box that has an encrypted password stored on a hard drive that is decrypted just before use and how this interacts with the workarounds that you mention:

1. If we accept people not upgrading because we fail to consider the range of use cases this is an option.

2. Also an option if we want to encourage use of clients that we don't supply.

3. Storing the password on the hard drive in an unencrypted form is insecure, exposing it if the disk is removed from the device. This is not a secure workaround.

4. This is possible but makes life unnecessarily harder.

5. Potentially possible.

6. The client being secure against password snooping does not mean that a server on a different box can have accounts with no passwords. This is not a secure solution.

We need to recognise that there is a broad range of use cases and not just presume that they are all the ones that present the security risk that prompted this warning being added. For those cases it's useful but there are others where clients are secured.

Adding an option to provide per-message control of individual errors, warnings and notes seems to be the way forward here. We then warn initially but those for whom the warning is inappropriate because they have secure client machines that do not expose a password on the command line can add the setting to their cnf/ini files to suppress it. This has the additional advantage of being the general direction in which we want all message handling to be going in.

We need to do better here, recognising that the warning is correct that passwords on the command line MAY be insecure, not that they always are, and allowing those with appropriate environments to act appropriately for those, while we initially default to giving the warning so we cover the cases where it is insecure.

James Day, MySQL Senior Principal Support Engineer, Oracle

The security versus ease of use trade off applies here.  Simply removing ease of use (by adding this nag message) does not simply create security.  Having this educational message in logs, with all its subtleties about possibilities of insecurity has little merit.  I suggest that it should only be printed when stderr is a terminal - if (isatty(2)) {}.  However, this will not be what everyone needs.

A configurable logging level for nanny-nag messages would be a good middle ground, promising future use (e.g. WARNING: your connection to this mysql database is not encrypted).  I would configure my educational messages to go to stderr only (if (isatty(2))).  There are already too many options of the style --skip-this-and-that-obscure-thing (the --skip namespace is a little cluttered).

My company upgraded to MySQL 55.6.x a couple of months ago, and this BUG is driving us nuts.

I am voting with essentially all the other responders here, asking you to eliminate this warning entirely, or at least let us suppress it.

You at MySQL have no idea of all the use cases for why your users may need to fully automate their scripts.  In particular, you should not assume that manually configuring MySQL credential files is a practical option.  For example, our firm has scripts in SVN that we want to work when downloaded either onto an arbitrary developers workstation, or when downloaded into 1000 new AWS virtual machine instances that we are going to use for only a few hours to do some massive parallel simulations on.  We need what is pulled down from SVN to just work.

This bug has spawned lots of effort by people to work around it; see
	https://stackoverflow.com/questions/20751352/suppress-warning-messages-using-mysql-from-wi...

I chose to replace every script mysql command line that used to look like
	mysql  --password=$mysqlPassword ...
with these 2 lines:
	export MYSQL_PWD="$mysqlPassword"
	mysql ...
This is every bit as "insecure" but at least does not pollute my stderr stream with wrong warnings.

Typo correction: in my last post, "MySQL 55.6.x" should be "MySQL 5.6.x".  (Is there an edit link that I am missing?)

As another person joining the call to kill this warning, Ruby on Rails uses the mysql command line for some tasks, which throws this message:

https://github.com/rails/rails/blob/master/activerecord/lib/active_record/tasks/mysql_data...

This only impacts development AFAIK, but it is obnoxious. And it is not an error or something to be warned about.

If someone has a better idea on how Rails can handle this, I'll try to make a pull request to improve this case. Until this point, this warning is incorrect and misleading.

Please disable this warning. It breaks apps with poor error handling.

I'll add my criticism to chorus about the irresponsible decision to add this warning message.  My team will handle security concerns ourselves, thanks.  Our production backups have been unusable for days.  We're now using sed to remove the line from our spoiled backup files.  Unfortunately, that means we can't dump directly to gzip.  And with a 600GB database, that's a big problem.

MySQL was developed for personal use. It's great that it has grown and gained much greater adoption and done what it has in the world...

...but I'm a personal user, just running a small gameserver at home... I've decided to move my game engine to a new Linux build and I'm bitten up the backside by this.

I can't run a mysqldump at the command line now, thanks to this numbskullery. There needs to be an option to say, "Hey, I'm not a big fish, I'm a small time user and I really don't care that I'm using a password on the command line."

This has really brought my weekend work to an utter, screeching halt. Thanks a bunch for ruining my weekend. I still don't know how the heck I'm going to get around this. Back to Google, I suppose. :-(