mysql - 如何在较新版本的 MySQL 中以编程方式验证密码？
问题描述
在较新版本的 MySQL 中，有一个可插入的身份验证系统。以前有 PASSWORD 函数可以生成在 中找到的散列user.authentication_string，但该函数已在 MySQL 的更高版本（8.0.11 AFAICT）中删除。
如果我想验证用户密码（没有实际登录 - 因为用户可能无法从我所在的主机登录），有没有办法使用现代 MySQL 来做到这一点？
解决方案
如果我想验证用户密码（没有实际登录 - 因为用户可能无法从我所在的主机登录），有没有办法使用现代 MySQL 来做到这一点？
PASSWORD(..)函数接缝与使用此 SQL 相同CONCAT('*', UPPER(SHA1(UNHEX(SHA1(..)))))
我不知道您将密码用于何处，但您不应将其用作应用程序的密码。
笔记
PASSWORD()由 MySQL Server 中的身份验证系统使用；您不应该在自己的应用程序中使用它。为此，请考虑一种更安全的方法
询问
SELECT
PASSWORD('password')
, CONCAT('*', UPPER(SHA1(UNHEX(SHA1('password')))))
, PASSWORD('password') = CONCAT('*', UPPER(SHA1(UNHEX(SHA1('password')))));
结果
| PASSWORD('password') | CONCAT('*', UPPER(SHA1(UNHEX(SHA1('password'))))) | PASSWORD('password') = CONCAT('*', UPPER(SHA1(UNHEX(SHA1('password'))))) |
| ----------------------------------------- | ------------------------------------------------- | ------------------------------------------------------------------------ |
| *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | 1 |
看演示
一个更好的测试接缝来确认它。
询问
SELECT
HEX(number_generator.number) AS 'password'
, PASSWORD(HEX(number_generator.number))
# notice that HEX(number_generator.number) below is the "password" here
, CONCAT('*', UPPER(SHA1(UNHEX(SHA1( HEX(number_generator.number) )))))
, PASSWORD(HEX(number_generator.number)) = CONCAT('*', UPPER(SHA1(UNHEX(SHA1( HEX(number_generator.number) )))))
FROM (
SELECT
@row := @row + 1 AS number
FROM (
SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) row1
CROSS JOIN (
SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) row2
CROSS JOIN (
SELECT @row := 0
) init_user_params
) AS number_generator
结果
| password | PASSWORD(HEX(number_generator.number)) | CONCAT('*', UPPER(SHA1(UNHEX(SHA1( HEX(number_generator.number) ))))) | PASSWORD(HEX(number_generator.number)) = CONCAT('*', UPPER(SHA1(UNHEX(SHA1( HEX(number_generator.number) ))))) |
| -------- | ----------------------------------------- | --------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------- |
| 1 | *E6CC90B878B948C35E92B003C792C46C58C4AF40 | *E6CC90B878B948C35E92B003C792C46C58C4AF40 | 1 |
| 2 | *12033B78389744F3F39AC4CE4CCFCAD6960D8EA0 | *12033B78389744F3F39AC4CE4CCFCAD6960D8EA0 | 1 |
| 3 | *C4E74DDDC9CC9E2FDCDB7F63B127FB638831262E | *C4E74DDDC9CC9E2FDCDB7F63B127FB638831262E | 1 |
| 4 | *908BE2B7EB7D7567F7FF98716850F59BA69AA9DB | *908BE2B7EB7D7567F7FF98716850F59BA69AA9DB | 1 |
| 5 | *7534F9EAEE5B69A586D1E9C1ACE3E3F9F6FCC446 | *7534F9EAEE5B69A586D1E9C1ACE3E3F9F6FCC446 | 1 |
| 6 | *C3AB9ECDF746570BBF9DCAA9DB3586D25956DC93 | *C3AB9ECDF746570BBF9DCAA9DB3586D25956DC93 | 1 |
| 7 | *23E7A7428138939FBE2F69D23E5B87383EFD83C9 | *23E7A7428138939FBE2F69D23E5B87383EFD83C9 | 1 |
| 8 | *6AF37A8C78E3A957D16D98F12788D1CFB2987A4C | *6AF37A8C78E3A957D16D98F12788D1CFB2987A4C | 1 |
| 9 | *7E9FDC7F61153649AB9A75CED26807DF74F86E65 | *7E9FDC7F61153649AB9A75CED26807DF74F86E65 | 1 |
| A | *26307F6B5CDB40C15C247B96C131CC1E0B3FFD1B | *26307F6B5CDB40C15C247B96C131CC1E0B3FFD1B | 1 |
| B | *693EFD3BD44CCBA9924731C2DB18ADB8825C0B0A | *693EFD3BD44CCBA9924731C2DB18ADB8825C0B0A | 1 |
| C | *8B1F657800F87E02617CD07126FDCF7B9F13E955 | *8B1F657800F87E02617CD07126FDCF7B9F13E955 | 1 |
| D | *3F7A80713CAA5954D376F883C83B8E4FEFEAF72C | *3F7A80713CAA5954D376F883C83B8E4FEFEAF72C | 1 |
| E | *1355D7A5CA049A2A7FA92669438A10C77D4FB706 | *1355D7A5CA049A2A7FA92669438A10C77D4FB706 | 1 |
| F | *2201A8B92856ABC4CDA3731B6D3AC61EEC87916C | *2201A8B92856ABC4CDA3731B6D3AC61EEC87916C | 1 |
...
... |
... |
| 53 | *30E6AFC81FB2DB79651D461029189713DDD2D847 | *30E6AFC81FB2DB79651D461029189713DDD2D847 | 1 |
| 54 | *A28085F893F86EA1E692F52D847EA3B203C448E1 | *A28085F893F86EA1E692F52D847EA3B203C448E1 | 1 |
| 55 | *4C951E13CC5E761093F241590580096A2276ECAC | *4C951E13CC5E761093F241590580096A2276ECAC | 1 |
| 56 | *060C8650684D90D54F2D537D0B8513C74F1AE4DD | *060C8650684D90D54F2D537D0B8513C74F1AE4DD | 1 |
| 57 | *F22C0306C4BB97CAE897F4BA7A3D22870725E51D | *F22C0306C4BB97CAE897F4BA7A3D22870725E51D | 1 |
| 58 | *C96B8933A9A02563E00980C026C3401B1E3FB6A2 | *C96B8933A9A02563E00980C026C3401B1E3FB6A2 | 1 |
| 59 | *42AC75307953D669FDEBD5928227A0A991AABFB0 | *42AC75307953D669FDEBD5928227A0A991AABFB0 | 1 |
| 5A | *A43D92E9EC11516AC82C4561124A08E91DE4F208 | *A43D92E9EC11516AC82C4561124A08E91DE4F208 | 1 |
| 5B | *0A89BF1EEF0EDB061EE4F72477E498E5C3233909 | *0A89BF1EEF0EDB061EE4F72477E498E5C3233909 | 1 |
| 5C | *78B1EECD64E0949B20E747230E30538898833DC1 | *78B1EECD64E0949B20E747230E30538898833DC1 | 1 |
| 5D | *C536CE7F28C05D5BBB5E776A92D9DCFF515A6955 | *C536CE7F28C05D5BBB5E776A92D9DCFF515A6955 | 1 |
| 5E | *5C1BA2FD08D6FC2724860A81B70B44CC14912E95 | *5C1BA2FD08D6FC2724860A81B70B44CC14912E95 | 1 |
| 5F | *7031DDE5CFC93067F81FBD30445112136AB32E53 | *7031DDE5CFC93067F81FBD30445112136AB32E53 | 1 |
| 60 | *0B30A071BE5EFE9C738FC899EFF47F90202C533D | *0B30A071BE5EFE9C738FC899EFF47F90202C533D | 1 |
| 61 | *DCDBF922065A133AE5985C3AA7465179DF4C8086 | *DCDBF922065A133AE5985C3AA7465179DF4C8086 | 1 |
| 62 | *0B3DF0C237D6FD5EA8D743889B33384299F8059F | *0B3DF0C237D6FD5EA8D743889B33384299F8059F | 1 |
| 63 | *2A3522DE0C5E510153DA977554999B35C2CA0B56 | *2A3522DE0C5E510153DA977554999B35C2CA0B56 | 1 |
| 64 | *61EB3D64954A1F12CD41EA35F2EB27A0E785E997 | *61EB3D64954A1F12CD41EA35F2EB27A0E785E997 | 1
看演示
推荐阅读
- php - readfile 不适用于连接
- gulp - 如果目标文件太大，gulp-sourcemaps 会失败
- dynamic-programming - DP方法？输出应该是最大和。在知名公司面试（仍然不知道，任何提示）
- elasticsearch - 如何有效地确定搜索结果中是否有任何术语
- pandas - 在时间序列中使用 set_index 从 DataFrame 中消除假期数据行
- scala - 无法使用 Flink 1.5 集群提交作业
- python - 已安装的库无法在 Jupyter Notebook 中导入
- php - 在 Woocommerce 的 WP_Query 循环中显示产品价格
- android - 更改屏幕方向时添加的额外空间
- elm - 在 let 表达式中使用 elm 去抖动