MySQL Bugs: #69027: Default secure_auth value breaking PHP connects

Bug #69027	Default secure_auth value breaking PHP connects
Submitted:	21 Apr 2013 21:54	Modified:	1 May 2013 14:07
Reporter:	Jørgen Thomsen	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S2 (Serious)
Version:	5.6.11	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	secure_auth

Description:
The change of secure_auth from default off to on is breaking programs using libmysqlclient.so e.g. PHP

Apparently libmysqlclient is not reading the my.cnf settings

Currently connections to a remote server (out of my control) are not possible.
 
Using any of
secure-auth
secure-auth=on
secure-auth=off
in the [client] section of my.cnf is working fine when using the mysql program, but not using PHP

How to repeat:
Us a mysqli_connect from PHP to a remote server

Suggested fix:
Always use the my.cnf setting

Changing

  mysql->options.secure_auth= TRUE;
to
  mysql->options.secure_auth= FALSE;

in
mysql_init(MYSQL *mysql) 
in 
mysql-5.6.11/sql-common/client.c
was a quick fix.

Same behavior here when upgrading from MySQL 5.6.10 to 5.6.11.

Not sure if it's a bug with pre-4.1.1 password hashes mangling on 5.6.11 or this release directly deprecates this feature.

FYI: if you need RPM packages for CentOS, I have just commited this patch that backports mysql_init() from 5.6.10 and will allow again old password hashes:

https://github.com/santisaez/powerstack/blob/master/packages/mysql/mysql-powerstack-secure...

Two questions:

Is the remote server 5.6?

Have you tried using skip-secure-auth (instead of secure-auth=off) in the [client] section of the configuration file?

The remote server is 5.1.39-log - FreeBSD port: mysql-server-5.1.39

mysqldump: unknown option '--skip-secure-auth'

It appears that the --skip-secure-auth option only exists for mysql, and not other libmysql-based clients (including mysqldump, for example):

C:\mysql-5.6.11-winx64>bin\mysqldump --skip-secure-auth
mysqldump: unknown option '--skip-secure-auth'

C:\mysql-5.6.11-winx64>bin\mysql --skip-secure-auth
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4

Which would also explain behavior from other libmysql-dependent applications, such as PHP/mysqli.  Need to verify this in code, but it seems likely that this problem will affect all applications *other* than mysql which use libmysql from a 5.6 distribution.

The core problem in this bug is the same as bug#69051.  I'll mark this as a duplicate and update 69051.

Duplicate of Bug#69051.

The workaround ("solution", really) to this is to change the password for the affected user to a post-4.1 hash.  This is really a recommended best practice, regardless - password hashing and authorization process prior to 4.1 has notable security limitations (discussed in documentation at http://dev.mysql.com/doc/refman/5.0/en/password-hashing.html).

MySQL 5.6.12 has still this bug: the server does not allow pre-4.1.1 password hashes although the server is started with "skip-secure-auth option".

Proposed "change password to new hash" solution doesn't work if you still connect to mysql server 4.0 (and there are such setups out there).

This bug is fixed by the patch attached to #75425.
At the time of writing, the future of that patch is unknown, but I recompiled my mysql incorporating the patch and it's doing the job (locally) well.