mysqlnd fails to authenticate with sha256_password accounts using passwords longer than 19 characters.

[weigon]

Description

The following code:

<?php
// CREATE USER sha256_20 IDENTIFIED WITH sha256_password BY '01234567890123456789';
// CREATE USER sha256_19 IDENTIFIED WITH sha256_password BY '0123456789012345678';
new mysqli("127.0.0.1", "sha256_19", "0123456789012345678");
new mysqli("127.0.0.1", "sha256_20", "01234567890123456789");

Resulted in this output:

PHP Fatal error:  Uncaught mysqli_sql_exception: Access denied for user 'sha256_20'@'localhost' (using password: YES) in Command line code:1
Stack trace:
#0 Command line code(1): mysqli->__construct()
#1 {main}
  thrown in Command line code on line 1

But I expected this output instead:

With the mysql client, authentication works as expected.

$ mysqladmin --user=sha256_19 --password=0123456789012345678   ping
mysqld is alive
$ mysqladmin --user=sha256_20 --password=01234567890123456789 ping
mysqld is alive

PHP Version

PHP 8.1.2

Operating System

Ubuntu 22.04

[nielsdos]

I think there's an off-by-one in mysqlnd_xor_string. If I subtract 1 from the xor_str_len in the modulo operand, it seems to work... I'll dig a bit deeper.
EDIT: I think it works with -1 because it ends up with a length of 20, which interestingly equals SCRAMBLE_LENGTH.

[MohamedAbuZamil]

The behavior you are experiencing is related to how PHP's mysqli extension handles the connection authentication method when using the sha256_password plugin.

In MySQL 8.0.4 and later, a new default authentication plugin was introduced called caching_sha2_password, which is more secure than the older mysql_native_password plugin. However, PHP's mysqli extension doesn't fully support this new default plugin, and that's why you are encountering the "Access denied" error for the user 'sha256_20'.

To resolve this issue, you have a couple of options:

1. Use a Supported Authentication Plugin: Change the authentication method for the user 'sha256_20' to use the mysql_native_password plugin instead of sha256_password. This way, PHP's mysqli extension should be able to authenticate successfully.

   For example:

   ALTER USER 'sha256_20'@'localhost' IDENTIFIED WITH mysql_native_password BY '01234567890123456789';

2. Use MySQL Native Driver (mysqlnd): The mysqli extension can work seamlessly with the MySQL Native Driver (mysqlnd), which is a PHP native driver for MySQL. It provides better compatibility with the new authentication plugins, including caching_sha2_password.

   To use mysqlnd, you need to make sure it is enabled in your PHP configuration. Look for the following line in your php.ini file:

   ;extension=mysqlnd
   

   Uncomment it by removing the semicolon and restart your web server.

   With mysqlnd enabled, the mysqli extension should be able to handle the sha256_password plugin without any issues.

Note that using mysqlnd is generally recommended for better compatibility with the latest MySQL features and improvements. However, if you have a specific need to stick with sha256_password, you can use the first option to switch the authentication plugin for the 'sha256_20' user.

Remember to restart your web server (e.g., Apache or Nginx) after making any changes to the PHP configuration.

[nielsdos]

@MohamedAbuZamil your answer doesn't make sense. Please refrain from posting nonsense AI-generated content.