# 曇花一現的plugin
###### tags: `想知道嗎`, `MySQL`
---
![](https://i.imgur.com/x5KhJ0q.jpg =300x)
Sarah 🐘Vegelephant 🐘
蔬食-環保-玩小孩
past: Python
now : PHP
fb id:100001561720922
---
### php 之 caching_sha2_password plugin 的興衰成敗
* WHY
* WHAT
* HOW
Note:
為什麼需要這個 plugin
他是什麼
php如何解決他造成的問題
---
![](https://i.imgur.com/H2NuPKj.png)
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|<span><!-- .element: class="fragment highlight-green" -->sha256_password</span>|
|8.0|<span><!-- .element: class="fragment highlight-red" -->caching_sha2_password</span>|
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](https://mysqlserverteam.com/mysql-8-0-4-new-default-authentication-plugin-caching_sha2_password/)
* 首次連接:
![](https://i.imgur.com/eSPDRCN.png)
----
* 下一次:
![](https://i.imgur.com/OhDSvbG.png)
Note:
使用SHA-256提供更安全的密碼加密,並使用Cache來加速同一用戶的身份驗證過程。
[reference](https://kknews.cc/code/y3v2j3g.html)
---
支持 caching_sha2_password 的 PHP 版本
<div><font size="5">
| 版本 | 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.3.9
|7.4 (support)|
</font>
</div>
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 有時間](http://ftp.ntu.edu.tw/pub/php/distributions/)
---
WHY NOT SUPPORT
[Bug #76651(~~php programmer筆戰~~)](https://bugs.php.net/bug.php?id=76651)
![](https://i.imgur.com/a3kGge1.png =300x)
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](https://bugs.php.net/bug.php?id=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:](https://github.com/php/php-src/commit/03740ef7dffcc80530a89ebde3ccf5464f7f18e6#diff-398af6cde010c06283216edde1709507)
```
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](https://raw.githubusercontent.com/php/php-src/PHP-7.2.8/ext/mysqlnd/mysqlnd_auth.c)
[PHP-7.4](https://raw.githubusercontent.com/php/php-src/PHP-7.4/ext/mysqlnd/mysqlnd_auth.c)
Note:
C語言的[`a->b` 的含義是 `(*a).b`](https://farseerfc.me/dot-and-arrow-in-c.html)
---
目前處理方式:
---
1. 進入 mysql介面後,可以看一下
```=
show variables like 'default_authentication_plugin';
```
2. 在 /usr/local/etc/my.cnf 中的[mysqld] 加
```=
default_authentication_plugin=mysql_native_password
```
Note:
[處理 caching_sha2_password 中文](https://www.itread01.com/content/1532371255.html)
> 1. root/root 進去
> 2. `ALTER USER 'default'@'%' IDENTIFIED WITH mysql_native_password`
---
Thank you!\^0\^
{"metaMigratedAt":"2023-06-14T23:56:29.572Z","metaMigratedFrom":"YAML","title":"曇花一現的plugin","breaks":"true","slideOptions":"{\"transition\":\"slide\"}","contributors":"[{\"id\":\"0f4b2677-c241-45c8-bc6c-45fe527d2921\",\"add\":5287,\"del\":1267}]"}