php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #79011 MySQL caching_sha2_password Access denied for password with more than 20 chars
Submitted: 2019-12-21 03:06 UTC Modified: 2020-01-23 17:34 UTC
From: bendix dot ohlhauser at gmail dot com Assigned: nikic (profile)
Status: Closed Package: MySQLi related
PHP Version: 7.4.2 OS: Ubuntu 18 LTS
Private report: No CVE-ID: None
 [2019-12-21 03:06 UTC] bendix dot ohlhauser at gmail dot com
Description:
------------
--- Potential security issue, not sure though ---

--- Potentially issue with MySQL, instead of PHP, not sure though ---

Disclaimer: This might look like user error, but most certainly is not.

See: https://stackoverflow.com/questions/59432704/php-7-4-mysql-caching-sha2-password-randomly-denying-passwords

MySQL caching_sha2_password is supported by PHP7.4, but it handles the passwords incorrectly.

The following password via PHP: l0QDEptp*L6tNo28ey^8
Results in Access Denied.
Logging in to the account via mysql shell and running it again fixed the issue.
Also, removing a character fixed the issue.

Regarding php.ini: opcache is enabled.

Feel free to contact me for additional information.

Test script:
---------------
const DB_CHARSET = 'UTF8MB4';
const DB_HOST = '127.0.0.1';
const DB_USERNAME = 'test';
const DB_PASSWORD = '';
$mysqli = new mysqli(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_NAME);

Expected result:
----------------
Successful login

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

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2019-12-21 19:15 UTC] bendix dot ohlhauser at gmail dot com
Image showing the issue: https://imgur.com/a/AomDhtt
 [2019-12-21 20:26 UTC] requinix@php.net
-Status: Open +Status: Feedback
 [2019-12-21 20:26 UTC] requinix@php.net
PHP isn't somehow running your script differently because it knows you did something with the mysql client, so this sounds like an issue with MySQL's authentication system.

We can wait to see what they think about it.
https://bugs.mysql.com/bug.php?id=98048
 [2019-12-23 02:07 UTC] bendix dot ohlhauser at gmail dot com
-Summary: MySQL caching_sha2_password Authentication inconsistency +Summary: MySQL caching_sha2_password Access denied for password with more than 20 chars -Status: Feedback +Status: Open
 [2019-12-23 02:07 UTC] bendix dot ohlhauser at gmail dot com
It actually happens with passwords with over 19 charaters. See my stackoverflow answer.

It is probably a issue with mysql, but since I'm not 100% sure, I'll leave this open.

Probably not a security issue.
 [2019-12-23 02:16 UTC] bugreports at gmail dot com
> --- Potential security issue, not sure though ---

how could "Access denied" be a security issue to begin with?
if you get access with a random wrong password it would be
 [2019-12-23 02:23 UTC] requinix@php.net
It's *potentially* a security issue because the authentication system (<- important) doesn't seem to be working correctly. Don't know exactly how it's malfunctioning, which means don't know exactly what is wrong or whether it can be abused.
 [2019-12-27 15:24 UTC] bendix dot ohlhauser at gmail dot com
A developer at Oracle said that this is a PHP issue. Not sure if he really looked into it.

I personally would rather say it has probably something to do with MySQL, but I have very little understanding of the internals.

@cmb
Thanks for the hint, but there are 2 possible issues with it:
 - Why does it work after logging into MySQL CLI?
 - Is the error later 'converted' to "Access Denied"? I'd expect a different exception for that.

Since I'm a unnecessary middle man, I'd suggest you should talk to the assignee at Oracle. I will say the same to him.

I'd appreciate an update if you know the underlying issue or the issue has been fixed.
 [2019-12-27 15:44 UTC] nikic@php.net
-Assigned To: +Assigned To: nikic
 [2019-12-27 15:44 UTC] nikic@php.net
As you mention that logging in via shell fixes the issue, it seems unlikely that this is related to the password length. caching_sha2_password uses a cache (as the name implies) and has a different handshake depending on whether the cache is hit or not.

Most likely you are running into an issue where you specify a default socket, but the actual connection uses TCP, resulting in mysqlnd mistakenly classifying it as a secure transport. This should be fixed once https://github.com/php/php-src/pull/5034 lands.
 [2019-12-27 16:43 UTC] nikic@php.net
-Status: Assigned +Status: Feedback
 [2019-12-27 16:43 UTC] nikic@php.net
This should be fixed now. I'm going to leave this open for now until someone can confirm. (The fix is in the current 7.4 development branch and will be in 7.4.2.)
 [2019-12-28 20:37 UTC] bendix dot ohlhauser at gmail dot com
-Status: Feedback +Status: Assigned
 [2019-12-28 20:37 UTC] bendix dot ohlhauser at gmail dot com
@nikic

The MySQL assignee, did some testing and had some interesting notes. I'd recommend checking them out. (https://bugs.mysql.com/bug.php?id=98048)

Btw, when will PHP 7.4.2 be available for Ubuntu 19?
 [2019-12-28 20:39 UTC] bugreports at gmail dot com
> Btw, when will PHP 7.4.2 be available for Ubuntu 19

probably never because most distributions are that stupid to think know anything better than upstream and cherry pick fixes or not
 [2020-01-23 17:34 UTC] bendix dot ohlhauser at gmail dot com
-PHP Version: 7.4.1 +PHP Version: 7.4.2
 [2020-01-23 17:34 UTC] bendix dot ohlhauser at gmail dot com
PHP 7.4.2 was released today. I updated my system - same issue.
Guess I'm waiting for PHP 7.4.3 :D

Please, before the next release test yourself. It's a really easy setup (example for Ubuntu 19):

apt update
apt upgrade

# Apache2
apt install apache2

# PHP 7.4
add-apt-repository ppa:ondrej/php
apt update
apt install php7.4-common php7.4-mysqli libapache2-mod-php7.4

# MySQL 8
wget -c https://repo.mysql.com//mysql-apt-config_0.8.13-1_all.deb
dpkg -i mysql-apt-config_0.8.13-1_all.deb
rm mysql-apt-config_0.8.13-1_all.deb
apt update
apt install mysql-server
mysql -u root
  CREATE USER 'php'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'Test123+++!!!';
  GRANT ALL PRIVILEGES ON mysql.* TO 'php'@'localhost';
  FLUSH PRIVILEGES;
  QUIT
# download test script from this answer: https://stackoverflow.com/a/59449405/4180937
# replace "const DB_NAME = 'test_db';" with "const DB_NAME = 'mysql';"
php test.php

---

The script will fail with "Access denied", if the bug is still present.
If the bug has been fixed it should print "Done.".
 [2020-01-24 13:55 UTC] nikic@php.net
Automatic comment on behalf of nikita.ppv@gmail.com
Revision: http://git.php.net/?p=php-src.git;a=commit;h=bb5cdd9b7469b37ceef0627100a415ead68f0030
Log: Fixed bug #79011
 [2020-01-24 13:55 UTC] nikic@php.net
-Status: Assigned +Status: Closed
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Fri Oct 11 08:01:27 2024 UTC