php Mysql密码散列方法新旧

Mysql password hashing method old vs new

提问by The Disintegrator

I'm trying to connect to a mysql server at dreamhost from a php scrip located in a server at slicehost (two different hosting companies). I need to do this so I can transfer new data at slicehost to dreamhost. Using a dump is not an option because the table structures are different and i only need to transfer a small subset of data (100-200 daily records) The problem is that I'm using the new MySQL Password Hashing method at slicehost, and dreamhost uses the old one, So i get

我正在尝试从位于 slicehost（两个不同的托管公司）服务器中的 php 脚本连接到 Dreamhost 上的 mysql 服务器。我需要这样做，以便我可以将 slicehost 上的新数据传输到 Dreamhost。使用转储不是一种选择，因为表结构不同，我只需要传输一小部分数据（100-200 条每日记录） 问题是我在 slicehost 和 dreamhost 上使用新的 MySQL 密码哈希方法使用旧的，所以我明白了

$link = mysql_connect($mysqlHost, $mysqlUser, $mysqlPass, FALSE); 

Warning: mysql_connect() [function.mysql-connect]: OK packet 6 bytes shorter than expected
Warning: mysql_connect() [function.mysql-connect]: mysqlnd cannot connect to MySQL 4.1+ using old authentication
Warning: mysql_query() [function.mysql-query]: Access denied for user 'nodari'@'localhost' (using password: NO) 

facts:

事实：

• I need to continue using the new method at slicehost and i can't use an older php version/library
• The database is too big to transfer it every day with a dump
• Even if i did this, the tables have different structures
• I need to copy only a small subset of it, in a daily basis (only the changes of the day, 100-200 records)
• Since the tables are so different, i need to use php as a bridge to normalize the data
• Already googled it
• Already talked to both support stafs

• 我需要在 slicehost 上继续使用新方法，但我不能使用旧的 php 版本/库
• 数据库太大，不能每天用转储来传输
• 即使我这样做，表格也有不同的结构
• 我只需要每天复制其中的一小部分（仅当天的更改，100-200 条记录）
• 由于表是如此不同，我需要使用php作为桥梁来规范化数据
• 已经用谷歌搜索过了
• 已经与两位支持人员进行了交谈

The more obvious option to me would be to start using the new MySQL Password Hashing method at dreamhost, but they will not change it and i'm not root so i can't do this myself.

对我来说更明显的选择是在 Dreamhost 上开始使用新的 MySQL 密码哈希方法，但他们不会改变它，而且我不是 root，所以我自己不能这样做。

Any wild idea?

任何疯狂的想法？

By VolkerK sugestion:

由 VolkerK sugestion 提供：

mysql> SET SESSION old_passwords=0;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT @@global.old_passwords,@@session.old_passwords, Length(PASSWORD('abc'));
+------------------------+-------------------------+-------------------------+
| @@global.old_passwords | @@session.old_passwords | Length(PASSWORD('abc')) |
+------------------------+-------------------------+-------------------------+
|                      1 |                       0 |                      41 |
+------------------------+-------------------------+-------------------------+
1 row in set (0.00 sec)

The obvious thing now would be run a mysql> SET GLOBAL old_passwords=0; But i need SUPER privilege to do that and they wont give it to me

现在显而易见的事情是运行 mysql> SET GLOBAL old_passwords=0; 但我需要超级特权才能做到这一点，他们不会给我

if I run the query

如果我运行查询

SET PASSWORD FOR 'nodari'@'HOSTNAME' = PASSWORD('new password');

I get the error

我收到错误

ERROR 1044 (42000): Access denied for user 'nodari'@'67.205.0.0/255.255.192.0' to database 'mysql'

I'm not root...

我不是根...

The guy at dreamhost support insist saying thet the problem is at my end. But he said he will run any query I tell him since it's a private server. So, I need to tell this guy EXACTLY what to run. So, telling him to run

Dreamhost 支持人员坚持说问题出在我的尽头。但他说他会运行我告诉他的任何查询，因为它是一个私人服务器。所以，我需要确切地告诉这个人该跑什么。所以，告诉他跑

SET SESSION old_passwords=0;
SET GLOBAL old_passwords=0;
SET PASSWORD FOR 'nodari'@'HOSTNAME' = PASSWORD('new password');
grant all privileges on *.* to nodari@HOSTNAME identified by 'new password';

would be a good start?

会是一个好的开始吗？

采纳答案by zombat

Yeah, that looks like a toughie. Without cooperation from your hosts or the ability to change password formats or client libraries, you don't have a lot of options.

是的，这看起来像个硬汉。如果没有您的主机的合作或更改密码格式或客户端库的能力，您将没有很多选择。

Honestly, my first choice would be to ditch Dreamhost. That's probably a lot of work, but if they're going to be stuck using old incompatible stuff, it will continue to be problematic.

老实说，我的第一选择是放弃 Dreamhost。这可能需要大量工作，但如果他们要使用旧的不兼容的东西，那将继续存在问题。

If that's not an option, what about a joint automated process? You could export the data on the Slicehost side into a CSV file and massage it into whatever format is necessary for Dreamhost, and then upload it to the Dreamhost server. You could have a cron script on the Dreamhost server check periodically for the uploaded file and process it (making sure to move or delete it after it was successfully processed).

如果这不是一种选择，那么联合自动化流程呢？您可以将 Slicehost 端的数据导出为 CSV 文件，然后将其转换为 Dreamhost 所需的任何格式，然后将其上传到 Dreamhost 服务器。您可以让 Dreamhost 服务器上的 cron 脚本定期检查上传的文件并对其进行处理（确保在成功处理后移动或删除它）。

回答by VolkerK

On some conditions you may still be able to set and use a "new hashing algorithm password".
MySQL 4.1+ servers are able to handle both login algorithms. Which one is used is independent from the old-passwords variable. If MySQL finds a 41 character long hash starting with * it uses the new system. And the PASSWORD() function is also able to use both algorithms. If the field mysql.user.Password is wide enough to store 41 characters and the old-passwords variable is 0 it will create a "new" password. The documention for old_passwordssays Variable Scope Bothso you might be able to change it for your session.
Connect to the MySQL server (with a client that is able to do so despite the global old_passwords=1), e.g. HeidiSQLand try the following:

在某些情况下，您仍然可以设置和使用“新的散列算法密码”。
MySQL 4.1+ 服务器能够处理这两种登录算法。使用哪一个与 old-passwords 变量无关。如果 MySQL 找到以 * 开头的 41 个字符长的散列，它将使用新系统。并且 PASSWORD() 函数也可以使用这两种算法。如果字段 mysql.user.Password 足够宽以存储 41 个字符并且 old-passwords 变量为 0，它将创建一个“新”密码。old_passwords的文档说，Variable Scope Both所以您可以为您的会话更改它。
连接到 MySQL 服务器（使用尽管全局 old_passwords=1 仍然能够这样做的客户端），例如HeidiSQL并尝试以下操作：

SET SESSION old_passwords=0;
SELECT @@global.old_passwords,@@session.old_passwords, Length(PASSWORD('abc'));

If it prints 1, 0, 41(meaning the global old_passwords is on, but for the session it's off and PASSWORD() returned a "new" password) you should be able to set a new passwordusing the new algorithm for your account within the same session.

如果它打印1, 0, 41（意味着全局 old_passwords 已打开，但对于会话它已关闭并且 PASSWORD() 返回“新”密码），您应该能够在同一会话中使用新算法为您的帐户设置新密码。

But if dreamhost really wants to disable the new passwords algorithm the mysql.user.Password field will be less than 41 characters long and there's nothing youcan do about it (except nagging them).

但是如果 Dreamhost 真的想禁用新密码算法，mysql.user.Password 字段的长度将少于 41 个字符，并且您无能为力（除了唠叨它们）。

回答by TehShrike

I just had this issue, and was able to work around it.

我刚刚遇到了这个问题，并且能够解决它。

First, connect to the MySQL database with an older client that doesn't mind old_passwords. Connect using the user that your script will be using.

首先，使用不介意 old_passwords 的旧客户端连接到 MySQL 数据库。使用您的脚本将使用的用户进行连接。

Run these queries:

运行这些查询：

SET SESSION old_passwords=FALSE;
SET PASSWORD = PASSWORD('[your password]');

In your PHP script, change your mysql_connect function to include the client flag 1:

在 PHP 脚本中，更改 mysql_connect 函数以包含客户端标志 1：

define('CLIENT_LONG_PASSWORD', 1);
mysql_connect('[your server]', '[your username]', '[your password]', false, CLIENT_LONG_PASSWORD);

This allowed me to connect successfully.

这使我能够成功连接。

回答by Bill Karwin

I would solve this by dumping the data at Slicehost, using SELECT ... INTO OUTFILE.

我会通过在 Slicehost 上转储数据来解决这个问题，使用SELECT ... INTO OUTFILE.

This allows you to design your query to make sure the output is in the format matching the table structure at the destination site.

这允许您设计查询以确保输出的格式与目标站点的表结构匹配。

Then transfer the dump file to Dreamhost and use LOAD DATA INFILE.

然后将转储文件传输到 Dreamhost 并使用LOAD DATA INFILE.

As an aside, Dreamhost is really stillusing MySQL 4.0? They're extremely outdated -- even MySQL 4.1's extended support is expiring this month(December 2009).

顺便说一句，Dreamhost 真的还在使用 MySQL 4.0 吗？它们非常过时——甚至 MySQL 4.1 的扩展支持也将于本月（2009 年 12 月）到期。

回答by Dennis C

I think you should make WebServices/RPC from slicehost and write the corresponding service on the to handle it.

我认为你应该从 slicehost 制作 WebServices/RPC 并在上面编写相应的服务来处理它。

回答by f.eberharter

I had the same issue. To solve it, I did the following:

我遇到过同样的问题。为了解决它，我做了以下事情：

SET PASSWORD = PASSWORD('[your password]');