Ulf Wendel

Solution for: MySQL 5.6 password expired, PHP can’t connect, application stops

2013/01/10 by admin

MySQL 5.6 introduces a new features that must be used with great care. A MySQL users password can be marked as expired. This way, a DBA can force a user to set or reset his password. The MySQL user must set a (new) password before he is allowed to do anything else. As a consequence, if a users password is expired all standard PHP MySQL API connect calls will fail. Applications stop working unless the application is changed to include a user dialog for setting a new password. To develop such a dialog for resetting an expired password one has to use a new connection flag introduced in PHP 5.4.12-dev. Don’t panic: to get in trouble DBA actions have to be at one level with dropping the MySQL user of a production PHP application…

Relax: IF MySQL 5.6 AND IF …AND IF …

You are being warned in time about a pitfall that DBAs may tap into in the future. What’s discussed affects you only:

  • IF using: MySQL 5.6, which is not GA/stable yet at the time of writing
    • AND IF using: ALTER USER user@host PASSWORD EXPIRE
      • AND IF: user@host is used by a PHP application to log in to MySQL
        • AND IF: you have not read about your options discussed below
      • OR

        • user@host is told to reset the password using an unmodified (= todays) PHP web application, for example, phpMyAdmin.

The pitfall, step-by-step

As of MySQL 5.6 a DBA can force a MySQL user to set or reset his password. Let’s assume I want to give Andrey access to my local MySQL 5.6 server. Thus, I create a MySQL user called Andrey. As I do not need to know his password but want him to set a password, I use the password expire feature to force him to set one.


nixnutz@linux-fuxh:~/ftp/mysql56bzr/install> bin/mysql -uroot -S/tmp/mysql56bzr.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2510
Server version: 5.6.11-debug Source distribution
[...]
mysql> GRANT ALL ON test.* TO andrey@localhost;
Query OK, 0 rows affected (0,05 sec)

mysql> ALTER USER andrey@localhost PASSWORD EXPIRE;
Query OK, 0 rows affected (0,00 sec)

Before I tell Andrey, I verify the account changes. It turns out that he can log in to MySQL but is not allowed to run any command but SET PASSWORD. As a good DBA, I have scanned the MySQL documentation and nothing seems suspicious. 


nixnutz@linux-fuxh:~/ftp/mysql56bzr/install> bin/mysql -uandrey -S/tmp/mysql56bzr.sock test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2511
Server version: 5.6.11-debug
[...]
mysql> SHOW TABLES;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

Quickly after asking Andrey to log in, he replies 🙁 haha…. He fails to log in to set a password. Given that he can’t log in, how should he set a password?! 


andrey@johannes_box:~/> /usr/local/mysql/bin/mysql -uandrey -S/tmp/mysql56bzr.sock test
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

Maybe PHP works, is Andrey’s baby mysqlnd affected?!


andrey@johannes_box:~> php -r '
$link = new mysqli("localhost", "andrey", "johannes", 
    "test", NULL, "/tmp/mysql56bzr.sock"); 
var_dump(mysqli_connect_error());'
PHP Warning:  mysqli::mysqli(): (HY000/1820): You must SET PASSWORD before executing this statement in Command line code on line 1
string(53) "You must SET PASSWORD before executing this statement"

andrey@johannes_box:~> php --version
PHP 5.3.11-dev (cli) (built: Mar  6 2012 11:28:19) (DEBUG)
Copyright (c) 1997-2012 The PHP Group
Zend Engine v2.3.0, Copyright (c) 1998-2012 Zend Technologies

I go and check whether I did anything wrong, which seems not the case. I manage to set the password. Immediately thereafter user andrey@localhost can create and populate table, log in using the new password and query the table.


nixnutz@linux-fuxh:~/ftp/mysql56bzr/install> bin/mysql -uandrey -S/tmp/mysql56bzr.sock test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2513
Server version: 5.6.11-debug
[...]
mysql> SET PASSWORD=PASSWORD('johannes');
Query OK, 0 rows affected (0,00 sec)

mysql> SHOW TABLES;
Empty set (0,08 sec)

mysql> CREATE TABLE test(id INT);
Query OK, 0 rows affected (0,96 sec)

mysql> INSERT INTO test(id) VALUES (1);
Query OK, 1 row affected (0,03 sec)

mysql> QUIT;
Bye
nixnutz@linux-fuxh:~/ftp/mysql56bzr/install> bin/mysql -uandrey -p -S/tmp/mysql56bzr.sock test
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2516
Server version: 5.6.11-debug Source distribution
[...]
mysql> SELECT * FROM test;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0,00 sec)

Step 1: check client versions

The solution is in the different versions of the MySQL command-line prompt used in the above examples. The MySQL prompt is an application. It is one of many PHP (Java, ODBC, C++, C, …) MySQL applications. All the applications use a MySQL client library to connect MySQL. Old MySQL client libraries cannot handle the password expiration.

If using an old client, the connect fails:


andrey@johannes_box> /usr/local/mysql/bin/mysql --version
/usr/local/mysql/bin/mysql  Ver 14.14 Distrib 5.1.45, for suse-linux-gnu (x86_64) using readline 5.1

Whereas the latest and greatest clients can handle password expiration and may allow connecting to MySQL for setting a password.


nixnutz@linux-fuxh:~/ftp/mysql56bzr/install> bin/mysql --version
bin/mysql  Ver 14.14 Distrib 5.6.11, for Linux (x86_64) using  EditLine wrapper

The PHP mysqlnd library can handle expired passwords since PHP 5.4.12-dev (next 5.4 release). Please, consult the MySQL documentation on version details for other libraries and clients.

Step 2: connect error is default

It is not enough for an application to use a recent MySQL client library to allow for setting an expired password. Todays standard API connect calls will fail on connect, if the password has expired. Here is an example using PHP 5.4.12-dev and a standard connect call:


nixnutz@linux-0v4u:~/git/superhero/php-src> sapi/cli/php -r '
$link = new mysqli("localhost", "andrey", 
             "johannes", "test", NULL, "/tmp/mysql56bzr.sock");
var_dump(mysqli_connect_error());';

Warning: mysqli::mysqli(): (HY000/1820): You must SET PASSWORD before executing this statement in Command line code on line 1
string(53) "You must SET PASSWORD before executing this statement"

nixnutz@linux-0v4u:~/git/superhero/php-src> sapi/cli/php -v
PHP 5.4.12-dev (cli) (built: Jan 10 2013 15:33:00) (DEBUG)
Copyright (c) 1997-2013 The PHP Group
Zend Engine v2.4.0, Copyright (c) 1998-2013 Zend Technologies

All MySQL client libraries should show this default behaviour: fail on connect if expired password.

The libraries assume that a standard application has no dialog for setting a password, thus the connect call fails by default. Those applications that want to allow resetting an expired password in an interactive way, must use new API features. The MySQL command-line prompt is an example of such an interactive application.

Step 3: new PHP API to allow for setting expired password

To write an interactive PHP application which allows setting an expired password, you have to use mysqli_init(), mysqli_options() and mysqli_real_connect(). Set mysqli_options($link, MYSQLI_OPT_CAN_HANDLE_EXPIRED_PASSWORDS, 1); to be allowed to connect to MySQL. Please note, you must pass the old password to mysqli_real_connect(), if any. Once connected, you are allowed to run SET commands such as SET PASSWORD. As soon as the expired password has been (re-)set, the connected MySQL user can run all commands he has been granted privileges for.

$link = mysqli_init();
$link->options(MYSQLI_OPT_CAN_HANDLE_EXPIRED_PASSWORDS, 1);
if (!$link->real_connect("localhost", "", "johannes", "test", NULL, "/tmp/mysql56bzr.sock")) {
  printf("Cannot log in: [%d] %s\n", $link->connect_errno, $link->connect_error);
} else {
  /* do this in a nice dialog... */
  if (!$link->query("SET PASSWORD=PASSWORD('johannes')")) {
   printf("[%d] %s\n", $link->errno, $link->error);
  }
  if ($res = $link->query("SELECT 1")) {
    var_dump($res->fetch_assoc());
  }
}

Now that the expired password has been (re-)set the application can use standard connect calls (mysqli_connect(), new mysqli(...)) without getting error 1820.

The worst case scenario

Think twice before you expire a password!

Imagine you expire the password of a user and advice the user of resetting it, for example, using todays phpMyAdmin or any similar tool. Todays phpMyAdmin is not prepared to handle the expired password situation. It is using standard PHP MySQL connect calls. It will fail to connect. The user cannot use it to reset the expired password. Ouch…

The solution is above. As a DBA, you must make sure that every MySQL user with an expired password has access to an interactive tool for resetting the password. This can be any PHP 5.4.12-dev+ MySQL application prepared (new flag!) to handle expired passwords or, for example, a MySQL 5.6.10+ command line prompt.

Happy hacking!

@Ulf_Wendel Follow me on Twitter

PS: PDO_MySQL? If you have a nice idea how to properly integrate the new flag into the existing API, please file feature request at bugs.php.net. I don’t think we are in a hurry: too many if’s, if you mind to look above…

Comments are closed.