0%

php_mysql_plugin

Posted on Edited on

曇花一現的plugin

tags: 想知道嗎, MySQL

php 之 caching_sha2_password plugin 的興衰成敗

  • WHY
  • WHAT
  • HOW
    Note:
    為什麼需要這個 plugin
    他是什麼
    php如何解決他造成的問題


Note:
php 需要一些plugins來和 DB溝通 – Mysqlnd

Versions of MySQL

Release End of support
5.1 December 2013
5.5 December 2018
5.6 February 2021
5.7 October 2023
8.0 April 2026
Release Authentication
5.x mysql_native_password
5.6 sha256_password
8.0 caching_sha2_password

Note:
MySQL 5.6以前，密碼加密方式是 mysql_native_password，SHA1算法。如果兩個用戶帳戶使用相同的密碼，加密出來的結果是相同的
後來MySQL 5.6有 sha256_password 使用多輪SHA256 hash，雖然安全性強，不過因為需要 TLS 或 支援 RSA 的密碼交換來連接到 server，非常耗時，所以後來又推出兩全其美的演算法。
MySQL 8 才升級，預設身份驗證的插件為caching_sha2_password

caching_sha2_password

  • 首次連接:
  • 下一次：

Note:
使用SHA-256提供更安全的密碼加密，並使用Cache來加速同一用戶的身份驗證過程。
reference

支持 caching_sha2_password 的 PHP 版本

版本 no support no no
7.1 7.1.2(0214’17) 7.1.20(0719’18) 7.1.23(1009’18) no
7.2 no 7.2.8 (0717’18) 7.2.11 (1009’18) 7.2.22
7.3 (0708’19) no no no no
7.4 (support)
Note: [官方文件：](https://www.php.net/manual/en/mysqli.requirements.php) When running a PHP version before 7.1.16, or PHP 7.2 before 7.2.4, set MySQL 8 Server's default password plugin to mysql_native_password.

all distributions 有時間

WHY NOT SUPPORT

Bug #76651(php programmer筆戰)

This new machanism requires OpenSSL and ext/hash to be available.

Note:
Without those statically compiled in (we can’t do proper runtime guessing for C symbols) we can’t use them and have a compile-time decision to make.
Bug #76660
PDOException

On github:

Per bug #76651 these changes do not appear to work correctly in
some cases. As no immediate fix seems to be forthcoming, I’m
reverting these changes.

修改 /ext/mysqlnd/mysqlnd_auth.c

revert 內容 on github：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Revert all MySQL auth related changes

Per bug #76651 these changes do not appear to work correctly in
some cases. As no immediate fix seems to be forthcoming, I'm
reverting these changes.

Revert "Fixed invalid free introduced by d6e81f0 (avoid keeping "invalid" pointer)"

This reverts commit 11507c0.

Revert "Fix mysqlnd build without openssl"

This reverts commit 6c9db02.

Revert "Fix VC compilation as variable size array is not supported"

This reverts commit f96df64.

Revert "Fix MySQL 8 auth"

This reverts commit d6e81f0.

如果有興趣了解可以比較，之前升級加密方式改的 code 和最近改的方式差在哪裡
PHP-7.2.8
PHP-7.4

Note:
C語言的a->b 的含義是 (*a).b

目前處理方式：

  1. 進入 mysql介面後，可以看一下
1
show variables like 'default_authentication_plugin';
  1. 在 /usr/local/etc/my.cnf 中的[mysqld] 加
1
default_authentication_plugin=mysql_native_password

Note:
處理 caching_sha2_password 中文

Thank you！^0^