PHP 5.4 PDO 无法使用旧的不安全身份验证连接到 MySQL 4.1+

2021-12-26 00:00:00 php mysql pdo

我知道有很多类似的问题,事实上我已经阅读了所有 (9) 个.

然而,它们都没有解决我的问题.

我有一个共享主机包(最低限度).我的包中包含的是域名和托管 MySQL 服务器的单独 IP 地址.对于开发,我将 http://localhost/ 与 PHP 5.4 的开发服务器一起使用,并且我正在使用我在托管包中获得的 MySQL 服务器.

这个问题只出现在我的电脑上,因为我已经安装了 PHP 5.4,但是我的 web 主机已经安装了 PHP 5.2.17 并且不会升级.MySQL 服务器在 MySQL 5.1.50 上.

幸运的是,phpMyAdmin 具有内置的更改密码"功能.

phpMyAdmin 中有两个用于更改密码的散列选项:

  • MySQL 4.1+
  • 兼容 MySQL 4.0

我使用 MySQL 4.1+ 选项更改了密码,并确认更新成功.

<块引用>

个人资料已更新.SET PASSWORD = PASSWORD('***')

但是,当我执行此查询时:

SELECT @@global.old_passwords, @@session.old_passwords, Length(PASSWORD('abc'));

它告诉我密码长度仍然是 16.输出:

<块引用>

1 1 16

所以问题仍然存在.

<块引用>

无法连接到数据库.SQLSTATE[HY000] [2000] mysqlnd 无法使用旧的不安全身份验证连接到 MySQL 4.1+.请使用管理工具通过命令 SET PASSWORD = PASSWORD('your_existing_password') 重置您的密码.这将在 mysql.user 中存储一个新的、更安全的哈希值.如果在 PHP 5.2 或更早版本执行的其他脚本中使用了此用户,您可能需要从 my.cnf 文件中删除旧密码标志

当使用 phpMyAdmin 中的 DBO 用户登录时,我也尝试执行这些查询:

SET SESSION old_passwords=0;[phpMyAdmin 重新加载到主屏幕,但值仍然 = 1]设置全局 old_passwords = 0;#1227 - 访问被拒绝;您需要此操作的 SUPER 权限同花顺特权;#1227 - 访问被拒绝;您需要此操作的 RELOAD 权限

这与用于设置 DBO 用户的网站主机菜单中所述的内容相矛盾:

<块引用>

数据库所有者
创建新数据库时,需要指定一个Database Owner (DBO) User,该用户具有
对数据库的完全管理员访问权限.

这是我必须与我的虚拟主机商解决的问题吗?或者它可以由我的 DBO 用户解决吗?否则可以在PHP中绕过吗?(因为它适用于 PHP 5.2.17 但不适用于 PHP 5.4)

解决方案

SOLVED!

尽管 SET SESSION old_passwords=0; 在 phpMyAdmin 中不起作用.

我下载了 MySQL GUI 工具并使用了 MySQL 查询浏览器在非 DBO 用户上执行相同的命令:

SET SESSION old_passwords = 0;

SELECT @@global.old_passwords, @@session.old_passwords, Length(PASSWORD('abc'));

现在返回:

1 0 41

所以我只是更改了密码:

SET PASSWORD = PASSWORD('my_old_password')

现在 PHP 5.4 PDO 连接到该用户的数据库!

I know there are a tonne of similar questions, in fact I've read all (9) of them.

However, none of them solve my problem.

I have a shared-hosting package (the minimum). What's included in my package is the domain name, and a separate IP address where the MySQL server is hosted. For development, I'm using http://localhost/ with PHP 5.4's development server, and I'm using the MySQL server I get in my hosting package.

The problem arises only on my PC, because I have installed PHP 5.4, but my web host has installed PHP 5.2.17 and won't upgrade. The MySQL server is on MySQL 5.1.50.

Luckily, phpMyAdmin has a built-in "Change Password" feature.

There are two hashing options in phpMyAdmin for changing a password:

  • MySQL 4.1+
  • MySQL 4.0 compatible

I changed the password with the MySQL 4.1+ option, and it confirmed the update was successful.

The profile has been updated. SET PASSWORD = PASSWORD( '***' )

However, when I perform this query:

SELECT @@global.old_passwords, @@session.old_passwords, Length(PASSWORD('abc'));

It tells me the password length is still 16. Output:

1     1     16

And so the problem persists.

Could not connect to database. SQLSTATE[HY000] [2000] mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication. Please use an administration tool to reset your password with the command SET PASSWORD = PASSWORD('your_existing_password'). This will store a new, and more secure, hash value in mysql.user. If this user is used in other scripts executed by PHP 5.2 or earlier you might need to remove the old-passwords flag from your my.cnf file

I've also tried to do these queries, when logged in with the DBO user in phpMyAdmin:

SET SESSION old_passwords=0; 
[phpMyAdmin reloads to the home screen, but the value remains = 1]

SET GLOBAL old_passwords = 0;
#1227 - Access denied; you need the SUPER privilege for this operation

FLUSH PRIVILEGES;
#1227 - Access denied; you need the RELOAD privilege for this operation

This is contradictory to what is stated in the webhost's menu for setting the DBO user:

Database Owner
When you create a new database, you need to specify a Database Owner (DBO) User, which will have
full administrator access to the database.

Is this something I have to take up with my webhosts? Or can it be solved by my DBO user? Otherwise can this be bypassed in PHP? (since it works with PHP 5.2.17 but not PHP 5.4)

解决方案

SOLVED!

Although the SET SESSION old_passwords=0; wasn't working in phpMyAdmin.

I downloaded the MySQL GUI Tools and used the MySQL Query Browser to execute the same command on non-DBO user:

SET SESSION old_passwords = 0;

SELECT @@global.old_passwords, @@session.old_passwords, Length(PASSWORD('abc'));

now returned:

1      0      41

So I simply changed the password:

SET PASSWORD = PASSWORD('my_old_password')

And now PHP 5.4 PDO connects to the database with that user!

相关文章