PHP 7.4 & MySQL: caching_sha2_password Denying long (20c) passwords

Question

UPDATE:

23.02.2020: The bug has been fixed in PHP 7.4.3.

23.12.2019: I've found out which passwords are affected. See my answer below. An answer to why long passwords are rejected would still be much appreciated.

Disclaimer: I have tried about 2 hours just setting different passwords. I am absolutely confident that the password below causes the issue. It works with other passwords. I do know how to fix it: use a different password. I want to know why it does not work. Because such inconsistency is not acceptable.

I am able to reproduce the issue on my system.

Recently switched to PHP 7.4 and MySQL 8, which by default use caching_sha2_password. After validating that it is in fact supported by PHP, I couldn't get it to work with my randomly generated password, so I used PHP 7.3 with mysql_native_password again.

Now, I setup a new server and it worked, so I tried to find out where the issue lies, so I can use caching_sha2_password on my other server.

MySQL (via mysql shell using root)

ALTER USER 'test'@'localhost' IDENTIFIED WITH caching_sha2_password BY '';
FLUSH PRIVILEGES;
QUIT

PHP

const DB_HOST = '127.0.0.1';
const DB_USERNAME = 'test';
const DB_PASSWORD = '';
const DB_NAME = 'test_db';
$mysqli = new mysqli(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_NAME);

TEST (to reproduce)

php db.php

Error

PHP Warning: mysqli::__construct(): (HY000/1045): Access denied for user 'test'@'localhost' (using password: YES) in /db.php on line 5

WORKAROUND: Funny thing is running this fixes it again for some insane reason:

mysql -u test -p
  # Enter password
  QUIT

Re-setting the password introduces the issue again.

I noticed that it failed based on the password I used. So I thought it might be unsupported characters or pasting issues.

I boiled my original password (40chars) down to this (20chars):

This password breaks it: l0QDEptp*L6tNo28ey^8

All shorter combinations work with no issue: This password works for example: l0QDEptp*L6tNo28ey^ as well as this one: 0QDEptp*L6tNo28ey^8

Important: Only test this public password in safe environments!

If you are able to reproduce the issue or have any thoughts on this (solution / possible reason), let me know.

Note: This issue actually occurs for all my randomly generated passwords, which are 40 characters long and contain special chars (e.g.: %^$!). I guess some relatively common combination triggers this issue.

Image: Demo

MySQL Version: mysql Ver 8.0.18-0ubuntu0.19.10.1 for Linux on aarch64 ((Ubuntu))
PHP version: PHP 7.4.1 (cli) (built: Dec 18 2019 14:44:55) ( NTS )
PHP Bug report: Here
MySQL Bug report: Here

I can't reproduce any error. I tried the blank password and the long password you said breaks in your environment, but both work. — Bill Karwin, Dec 21 '19 at 00:59
Have you tried `const DB_HOST = 'localhost';` instead of the IP address? — Funk Forty Niner, Dec 21 '19 at 01:01
@BillKarwin Its just not filled in.The password is below. Did you try this exact password? I tried about a hundred times. — Minding, Dec 21 '19 at 01:02
@FunkFortyNiner No, but it works with any other password. Just not with this exact combination of charaters — Minding, Dec 21 '19 at 01:03
can you post a line from users database related to that user? My bet is on having wrong host or something. — Your Common Sense, Dec 21 '19 at 01:23
@YourCommonSense [user test - host localhost - plugin caching_sha2_password]. **But it literally works with the one character removed.** — Minding, Dec 21 '19 at 01:29
If you do manage to come up with something reproducible, you should probably raise a bug with MySQL. Not sure what we can do about it on Stack Overflow. In the meantime, your solution is to use one of the other billions of possible passwords. — Lightness Races in Orbit, Dec 21 '19 at 18:21
@LightnessRaceswithMonica I'm confused to what exactly causes the issue, because logging in to mysql shell works, just not logging in via MySQLi, leading me to think it has to do with PHP. Then again the "fix" seems to indicate some issue with MySQL. Added version to post. — Minding, Dec 21 '19 at 18:30
@LightnessRaceswithMonica Yeah, but i'd rather use `mysql_native_password`, since this seems pretty sketchy. Already reported a bug to PHP (hinting that it might be a MySQL issue instead), but I might report this to MySQL as well. — Minding, Dec 21 '19 at 18:35
I ran into the exact same problem : I upgraded to MySQL 8 and PHP7.4 and the only way to make the PDO connection work is to set a password that is 19 characters long (maximum). Would you happen to know if the issue is being handle by the PHP team or the MySQL team? — Vincent, Jan 07 '20 at 15:05
@Vincent Fixes which will probably fix this issue are currently in the development branch and will ship with PHP 7.4.2 (see PHP Bug report above). — Minding, Jan 07 '20 at 15:11
@Vincent FYI: Bug is still present in PHP 7.4.2 (released today). I contacted the PHP team again. — Minding, Jan 23 '20 at 17:37
@Minding, yes I saw it today too. No trace of bug #79011 in the changelog :( — Vincent, Jan 23 '20 at 23:01
@Minding It has been fixed https://www.php.net/ChangeLog-7.php#7.3.15 — Vincent, Feb 23 '20 at 14:21

n0099 · Answer 1 · 2019-12-21T21:39:52.050

2

Use this script to automatically set random length and content password, then reconnent to mysql server.

$mysqli = new mysqli('localhost', 'username', 'initialpassword', 'dbname');
$i=0;
while (true) {
    if($i!=0){
        $mysqli = new mysqli('localhost', 'username', $pw, 'dbname');
    }
    $pw = bin2hex(random_bytes(rand(1, 16)));
    $successd = $mysqli->query("ALTER USER 'username'@'localhost' IDENTIFIED WITH caching_sha2_password BY '{$pw}'");
    echo "times:{$i} pw:{$pw} {$successd}\n";
    $i++;
}

In my environment there's not a single access denied error occurred after iterate 1 million times.

edited Dec 21 '19 at 21:39

answered Dec 21 '19 at 17:42

n0099

520
1
4
12

I am not sure why nobody seems to be able to reproduce the issue. I had it on two different servers and am able to reproduce it (with the given password). I did however not change the password via PHP, but instead via the mysql shell. – Minding Dec 21 '19 at 18:38
Did you try the exact password I mentioned above? – Minding Dec 21 '19 at 18:53
I've already tried your password setting through mysql-cli at first time, you could also use exec() in php to change password via mysql-cli. And my php version is PHP 7.4.1 (cli) (built: Dec 18 2019 14:44:22) ( NTS ), php-mysql client api libary version: mysqlnd 7.4.1, mysql version: 8.0.18. – n0099 Dec 21 '19 at 21:19
One flaw of this test method is that it only includes 0-F, excluding special chars. I have a feeling that `^` especially has something to do with the issue. – Minding Dec 22 '19 at 04:25
I also set the password via `root`. Logging in via `test` once fixed the issue. So, since you are already logged in when changing the password, you might "auto-fix" the issue. – Minding Dec 22 '19 at 04:35
You could also use these function https://stackoverflow.com/questions/4356289/php-random-string-generator to generate random string from determined character set. – n0099 Dec 22 '19 at 17:45
`root` user in mysql is just a default super privilege user which created while installing, so unlike linux/unix, `root` is just a ordinary username, having no different with other users which is created and granted same privileges with root by you. – n0099 Dec 22 '19 at 17:49
Up above i've mentioned that this will reconnent(so including relogin) to mysql server. Maybe you could also test creating random username and password pair to reproduce this situation. – n0099 Dec 22 '19 at 17:55
1

Thanks for your tests, I found the "answer" (answering *when*, rather than *why*) and posted it along with a test script. I'm curious if you can reproduce it (if your not busy). – Minding Dec 23 '19 at 01:54
1

Now i can got these access denied errors with your script or just using any password longer than 19 chars (like 12345678901234567890), then it can be temporarily fixed after login once within mysql-cli. – n0099 Dec 23 '19 at 11:22

Minding · Accepted Answer · 2019-12-23T01:59:47.687

The issue only occurs for passwords with more than 19 charaters. It can be worked around, by logging in once via the MySQL Shell (mysql -u test -p).

PHP Test script:

<?php
    # Static
    const DB_HOST = '127.0.0.1';
    const DB_USER = 'test';
    const DB_NAME = 'test_db';
    const INITIAL_PW = 'Test123+++!!!';

    # Variable
    const CHARS = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ+-!^$?#%';
    //const CHARS = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    # At least one special char is required, because of my security configuration.
    #  Due to this restriction the script may fail randomly - only 'Access Denied' is important.
    const REQUIRED_SPECIAL_CHAR = '$';
    # Any length below 20 works - 20 and upwards fails.
    const PASSWORD_LENGTH = 20;
    # Number of iterations
    const ITERATIONS = 10;

    # Test
    $pw = INITIAL_PW;
    for($i = 0; $i < ITERATIONS; $i++) {
        $mysqli = new mysqli(DB_HOST, DB_USER, $pw, DB_NAME);
        if($mysqli->connect_errno !== 0) {
            echo('Failed after '.$i.' iterations.'.PHP_EOL);
            echo('Password: '.$pw.PHP_EOL);
            exit;
        }
        $pw = getPassword(PASSWORD_LENGTH);
        $mysqli->query("ALTER USER 'test'@'localhost' IDENTIFIED WITH caching_sha2_password BY '$pw'") OR DIE('Failed to set password (after '.$i.' iterations): '.$mysqli->error.PHP_EOL);
        $mysqli->query('FLUSH PRIVILEGES') OR DIE('Failed to flush: '.$mysqli->error.PHP_EOL);
        $mysqli->close() OR DIE('Failed to close: '.$mysqli->error.PHP_EOL);
    }
    echo('Done.'.PHP_EOL);

    # Helper
    function getPassword(int $length) {
        $pw = '';
        for($i = 1; $i < $length; $i++)
            $pw .= CHARS[rand(0, strlen(CHARS) - 1)];
        $pw .= REQUIRED_SPECIAL_CHAR;
        return $pw;
    }
?>

Why does this happen with long passwords? IDK (maybe caching limit?)

PHP 7.4 & MySQL: caching_sha2_password Denying long (20c) passwords

2 Answers2

Linked