PHP 7.4及更高版本MySQL:caching_sha2_password拒绝长(20c)密码 [英] PHP 7.4 & MySQL: caching_sha2_password Denying long (20c) passwords

问题描述

更新:

2020年2月23日:该错误已在 PHP 7.4.3 .

23.02.2020: The bug has been fixed in PHP 7.4.3.

2019年12月23日:我发现了哪些密码受到影响.请在下面查看我的答案. 为何长密码被拒绝的答案仍然值得赞赏.

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.

免责声明:我已经尝试了大约2个小时，只是设置了不同的密码.我绝对相信以下密码会导致此问题.它可以与其他密码一起使用.我确实知道如何解决它:请使用其他密码.我想知道为什么它不起作用.因为这种不一致是不可接受的.

我能够在系统上重现该问题.

I am able to reproduce the issue on my system.

最近切换到PHP 7.4和MySQL 8，默认情况下使用caching_sha2_password. 在验证之后，实际上是受PHP支持的，我无法使其与我随机生成的密码一起使用，因此我再次将PHP 7.3与mysql_native_password一起使用.

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.

现在，我安装了一个新服务器，它可以正常工作，因此我试图找出问题所在，因此可以在其他服务器上使用caching_sha2_password.

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 (通过使用root的mysql shell)

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);

测试(再现)

php db.php

错误

PHP警告:mysqli :: __ construct():(HY000/1045):在第5行/db.php中拒绝对用户'test'@'localhost'(使用密码:是)的访问

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

解决方法:有趣的事情正在运行，它由于某些疯狂的原因而再次得到修复:

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.

我将原始密码(40个字符)简化为这个密码(20个字符):

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

此密码将其破坏:l0QDEptp*L6tNo28ey^8

所有较短的组合都可以正常工作: 此密码的工作方式例如:l0QDEptp*L6tNo28ey^以及该密码:0QDEptp*L6tNo28ey^8

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

重要提示:仅在安全环境中测试此公共密码！

如果您能够重现问题或对此有任何想法(解决方案/可能的原因)，请告诉我.

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

注意:我所有随机生成的密码(实际上是40个字符长且包含特殊字符(例如:％^ $！))实际上都会出现此问题.我猜一些相对常见的组合会触发此问题.

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.

图片:

MySQL版本:用于aarch64((Ubuntu))Linux上的mysql Ver 8.0.18-0ubuntu0.19.10.1
PHP版本:PHP 7.4.1(cli)(内置:2019年12月18日14:44:55)(NTS)
PHP错误报告: 此处
MySQL错误报告: 此处

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

推荐答案

此问题仅在具有19个以上字符的密码时出现.可以通过通过MySQL Shell(mysql -u test -p)登录一次来解决此问题.

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测试脚本:

<?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;
    }
?>

为什么长密码会发生这种情况? IDK (也许是缓存限制?)

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

这篇关于PHP 7.4及更高版本MySQL:caching_sha2_password拒绝长(20c)密码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！