Repository navigation
mysqlnd fails to authenticate with sha256_password accounts using passwords longer than 19 characters. #11438
Description
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
Activity
ndossche commentedon Jun 12, 2023
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.
Fix phpGH-11438: mysqlnd fails to authenticate with sha256_password a…
MohamedAbuZamil commentedon Aug 3, 2023
The behavior you are experiencing is related to how PHP's mysqli extension handles the connection authentication method when using the
sha256_passwordplugin.In MySQL 8.0.4 and later, a new default authentication plugin was introduced called
caching_sha2_password, which is more secure than the oldermysql_native_passwordplugin. 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:
Use a Supported Authentication Plugin: Change the authentication method for the user 'sha256_20' to use the
mysql_native_passwordplugin instead ofsha256_password. This way, PHP's mysqli extension should be able to authenticate successfully.For example:
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:
Uncomment it by removing the semicolon and restart your web server.
With mysqlnd enabled, the mysqli extension should be able to handle the
sha256_passwordplugin 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.
ndossche commentedon Aug 3, 2023
@MohamedAbuZamil your answer doesn't make sense. Please refrain from posting nonsense AI-generated content.
Fix GH-11438: mysqlnd fails to authenticate with sha256_password acco…
Merge branch 'PHP-8.1' into PHP-8.2
Merge branch 'PHP-8.2'