MySQL Bugs: #98048: MySQL 8 & PHP 7.4 caching_sha2_password Access denied for long passwords

Bug #98048	MySQL 8 & PHP 7.4 caching_sha2_password Access denied for long passwords
Submitted:	21 Dec 2019 19:40	Modified:	23 Feb 2020 17:51
Reporter:	Paul Ohlhauser	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	Connectors: mysqlnd ( PHP )	Severity:	S2 (Serious)
Version:	mysql Ver 8.0.18-0ubuntu0.19.10.1 for Li	OS:	Ubuntu
Assigned to:	MySQL Verification Team	CPU Architecture:	ARM (also on x86)
Tags:	access denied, authentication, caching_sha2_password

Description:
This might seem like user error, but most certainly is not.

Not sure if issue with MySQL or PHP, so reported to both.

Might be security issue.

Not going to repeat myself, see: https://stackoverflow.com/questions/59432704/php-7-4-mysql-caching-sha2-password-denying-o...

How to repeat:
See link above.

Increased severity to S2, since password depended issues hint a serious underlying problem.

Passwords with more than 19 characters get denied by mysqlnd, until somehow refreshed by mysql shell (mysql -u test -p).
Probably not a security issue.

Hi,

This is PHP bug. Please report to PHP team

thanks

Hello,

The bug has already been reported to PHP prior to this bug report. The link is available at the link above (at the end of the question).

The assignee at PHP said that it is probably a MySQL issue, since logging into MySQL CLI fixes the issue temporarily.

An explanation to why that would happen would help a lot I think.

Since I'm an unnecessary middle man, I'd suggest you contact them directly.

PHP Bug report: https://bugs.php.net/bug.php?id=79011

Well, I can def. say this is reproducible, rather easy:

[arhimed@localdev msb_8_0_18]$ ./use -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 39
Server version: 8.0.18 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql [localhost:8018] {root} ((none)) > alter user 'test'@'localhost' IDENTIFIED BY 'l0QDEptp*L6tNo28ey^8';
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:8018] {root} ((none)) > alter user 'test'@'127.0.0.1' IDENTIFIED BY 'l0QDEptp*L6tNo28ey^8';
Query OK, 0 rows affected (0.01 sec)

mysql [localhost:8018] {root} ((none)) >  \q
Bye
[arhimed@localdev msb_8_0_18]$ php x.php
PHP Warning:  mysqli::__construct(): (HY000/1045): Access denied for user 'test'@'localhost' (using password: YES) in /home/arhimed/sandboxes/msb_8_0_18/x.php on line 8
[arhimed@localdev msb_8_0_18]$ ./use --host 127.0.0.1 --port 8018 -utest -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 41
Server version: 8.0.18 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql [127.0.0.1:8018] {test} ((none)) > \q
Bye
[arhimed@localdev msb_8_0_18]$ php x.php
[arhimed@localdev msb_8_0_18]$

-- login working:

mysql [localhost:8018] {root} (mysql) > select host,user,plugin,hex(authentication_string) from user ;
+-----------+------------------+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| host      | user             | plugin                | hex(authentication_string)                                                                                                                   |
+-----------+------------------+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| 127.0.0.1 | test             | caching_sha2_password | 244124303035241479664642781C65352E79110B68560C02646C1673664F3345343846554D7A7878656E376146436E2F46466D454D50452E794B4874587A37626B3277625644 |
| localhost | test             | caching_sha2_password | 24412430303524214D5510303607724B37475C5905477B63287B3342696D38727977476F474F6E62456A3658734E424F33384E74584F33526267464D4E48333056526D4F6F37 |
+-----------+------------------+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------+

mysql [localhost:8018] {root} (mysql) > alter user 'test'@'localhost' IDENTIFIED BY 'l0QDEptp*L6tNo28ey^8';
Query OK, 0 rows affected (0.01 sec)

mysql [localhost:8018] {root} (mysql) > alter user 'test'@'127.0.0.1' IDENTIFIED BY 'l0QDEptp*L6tNo28ey^8';
Query OK, 0 rows affected (0.01 sec)

-- login now not working:

mysql [localhost:8018] {root} (mysql) > select host,user,plugin,hex(authentication_string) from user ;
+-----------+------------------+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| host      | user             | plugin                | hex(authentication_string)                                                                                                                   |
+-----------+------------------+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| 127.0.0.1 | test             | caching_sha2_password | 244124303035245349045B7222326D1A484828774A282638531543637264595143425136536F51415647546C50585642664271786E386E4A6B756969627571683150417A3644 |
| localhost | test             | caching_sha2_password | 24412430303524102A073E283D71072F11140A135070342A5C43116D39417A6759437148694E4E6F77586C7445624F38315630555667536D5676346477524F75354942326F30 |
+-----------+------------------+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------+

-- other terminal, loggged in as test
-- php login now working

mysql [localhost:8018] {root} (mysql) > select host,user,plugin,hex(authentication_string) from user where user='test';
+-----------+------+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| host      | user | plugin                | hex(authentication_string)                                                                                                                   |
+-----------+------+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| 127.0.0.1 | test | caching_sha2_password | 244124303035245349045B7222326D1A484828774A282638531543637264595143425136536F51415647546C50585642664271786E386E4A6B756969627571683150417A3644 |
| localhost | test | caching_sha2_password | 24412430303524102A073E283D71072F11140A135070342A5C43116D39417A6759437148694E4E6F77586C7445624F38315630555667536D5676346477524F75354942326F30 |
+-----------+------+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------+

xapi test, works like a charm

...
  mysqlx_session_t  *sess;
  const char   *url = (argc > 1 ? argv[1] : "mysqlx://test:l0QDEptp1L6tNo28ey18@127.0.0.1:18018");
  mysqlx_error_t *error;
  sess = mysqlx_get_session_from_url(url, &error);
...

capi works like a charm

[arhimed@localdev msb_8_0_18]$ gcc -o y y.c -I/home/arhimed/opt/mysql/8.0.18/include -L/home/arhimed/opt/mysql/8.0.18/lib -Wl,-R,/home/arhimed/opt/mysql/8.0.18/lib -lmysqlclient -lpthread -lm -lrt -lssl -lcrypto -ldl
[arhimed@localdev msb_8_0_18]$ cat y.c
#include <stdio.h>
#include <unistd.h>
#include <mysql.h>

int main(char** argv, int argc){
  MYSQL mysql;

  mysql_init(&mysql);
  if (!mysql_real_connect(&mysql,"127.0.0.1","test","l0QDEptp1L6tNo28ey18","test",8018,NULL,0))
  {
     fprintf(stderr, "Failed to connect to database: Error: %s\n", mysql_error(&mysql));
     return(-1);
  }
  printf("Connected...\n");
  return(0);
}

[arhimed@localdev msb_8_0_18]$
[arhimed@localdev msb_8_0_18]$ php x.php
PHP Warning:  mysqli::__construct(): (HY000/1045): Access denied for user 'test'@'localhost' (using password: YES) in /home/arhimed/sandboxes/msb_8_0_18/x.php on line 8
[arhimed@localdev msb_8_0_18]$ php x.php
PHP Warning:  mysqli::__construct(): (HY000/1045): Access denied for user 'test'@'localhost' (using password: YES) in /home/arhimed/sandboxes/msb_8_0_18/x.php on line 8
[arhimed@localdev msb_8_0_18]$ ./y
Connected...
[arhimed@localdev msb_8_0_18]$ php x.php
[arhimed@localdev msb_8_0_18]$

@Paul,

the very simple api to login works, both simple capi using old protocol and new using mysqlX both work 1/1 .. no idea why php won't login after ALTER and before any other mysql client is connected. This really makes zero sense to me but the question is how is mysql client compiled, with what... 

The mysql drivers for PHP is from what I can say linking mysqlclient statically so 

[arhimed@localdev msb_8_0_18]$ ldd /usr/lib64/php/modules/mysqli.so
        linux-vdso.so.1 (0x00007ffd643d0000)
        libc.so.6 => /lib64/libc.so.6 (0x00007f1aa8796000)
        /lib64/ld-linux-x86-64.so.2 (0x00007f1aa89aa000)
[arhimed@localdev msb_8_0_18]$ ldd /usr/lib64/php/modules/mysqlnd.so
        linux-vdso.so.1 (0x00007ffedd024000)
        libc.so.6 => /lib64/libc.so.6 (0x00007fd711679000)
        /lib64/ld-linux-x86-64.so.2 (0x00007fd7118ab000)
[arhimed@localdev msb_8_0_18]$ ldd /usr/lib64/php/modules/pdo_mysql.so
        linux-vdso.so.1 (0x00007ffd88721000)
        libc.so.6 => /lib64/libc.so.6 (0x00007fb5f0eb1000)
        /lib64/ld-linux-x86-64.so.2 (0x00007fb5f10aa000)
[arhimed@localdev msb_8_0_18]$

maybe they are linking some buggy version?!

One should look at the source of mysqli.so and see where the problem is but AFAIK it's not us that maintain mysqli.so and that's where the problem is.

same issue with pdo .. dunno how this works with php, both mysqli and pdo use mysqlnd.so ? so this: https://github.com/php/php-src/tree/master/ext/mysqlnd ? or mysqli / pdo use libmysqlclient directly? Anyhow that's code maintained by PHP guy's .. I can try to debug it but it's waaaaaaaaaaaay faster if it's done by someone who already know that code :)

all best
b.

[arhimed@localdev msb_8_0_18]$ php x.php
Connecting using MYSQLI
PHP Warning:  mysqli::__construct(): (HY000/1045): Access denied for user 'test'@'localhost' (using password: YES) in /home/arhimed/sandboxes/msb_8_0_18/x.php on line 8
ERROR: (1045) Access denied for user 'test'@'localhost' (using password: YES)
Connecting using MYSQLPDO
ERROR: SQLSTATE[HY000] [1045] Access denied for user 'test'@'localhost' (using password: YES)[arhimed@localdev msb_8_0_18]$
[arhimed@localdev msb_8_0_18]$
[arhimed@localdev msb_8_0_18]$ ./y
Connected...
[arhimed@localdev msb_8_0_18]$ php x.php
Connecting using MYSQLI
MYSQLI Connected!

Connecting using MYSQLPDO
PDO Connected!
[arhimed@localdev msb_8_0_18]$ cat x.php
<?php
const DB_HOST = '127.0.0.1';
const DB_USERNAME = 'test';
const DB_PASSWORD = 'l0QDEptp1L6tNo28ey18';
const DB_NAME = 'test';

echo "Connecting using MYSQLI\n";
$mysqli = new mysqli(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_NAME, 8018);
if ($mysqli->connect_errno) {
    echo "ERROR: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
} else {
    echo "MYSQLI Connected!\n";
}

echo "\n";
echo "Connecting using MYSQLPDO\n";
  try{
    $conn = new PDO("mysql:host=127.0.0.1:8018;dbname=test", DB_USERNAME, DB_PASSWORD);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "PDO Connected!\n";
  } catch(PDOException $err) {
    echo "ERROR: " . $err->getMessage();
  }
?>
[arhimed@localdev msb_8_0_18]$

@Bogdan,

thanks for the tests and hints. I'll make sure the PHP guys have a look.

They have at least fixed some related issue in upcoming PHP 7.4.2, not sure yet if that fixes it.

Hi,

I'm testing with 

[arhimed@localdev msb_8_0_18]$ php --version
PHP 7.4.1 (cli) (built: Dec 17 2019 16:35:58) ( NTS )
Copyright (c) The PHP Group
Zend Engine v3.4.0, Copyright (c) Zend Technologies
    with Zend OPcache v7.4.1, Copyright (c), by Zend Technologies
[arhimed@localdev msb_8_0_18]$

I also pinged my colleagues to check if they notice anything ... but this time of year, I'm sure you understand. Anyhow there is a similar issue that PERL has, and they have also a workaround... it's not same but similar:
https://github.com/perl5-dbi/DBD-mysql/commit/58e017b74dbaa3854aea04b7ca3dc87025ce7d92

Me or some of my colleagues will update this report if we figure out something but it's in the php's court.

all best
b.

Just for completeness:

The issue was with PHP and has been fixed in version 7.4.3.

Thanks @Bogdan for the help.