Migration Help -> Password (MD5/Salt Encryption)

I'm migrating 40,000+ users from a non-Boonex site to D7.  I thought I resolved my password migration issue with some help from here back in D7rc3 when I wrote the migration scripts, but I just realized that my passwords are not working in the D7 Final.

The passwords in my current db are MD5 and I bring them over to the D7 Profiles table using the following query:

SELECT user_id, user_name, user_email, user_password, CONV(FLOOR(RAND()*99999999999999), 10, 36), etc....

I use the CONV function above to create the Salt value.

Once this query is completed, I execute the following script to convert the MD5 format to D7 format:

UPDATE Dolphin.Profiles SET Password = SHA1(CONCAT(Password, Salt)) WHERE ID > 1

I'm using ID > 1 in the query so that I don't change the admin password.

End result is that I can't login with the migrated users using the correct (and verified) passwords. Did the D7 password format change?

Looking for anyone to please help resolve this

Thanks...

I don't think your sql querys are accurate. I looked the procedure up, and it has not changed.


You should also be doing this in a loop  to make sure each member gets a different salt value instead of setting them all the same.

This is just an example. Not tested.

$startID = 2; // start at 2 to skip admin
$endID = lastidindatabase;

for ($loop=$startID; $loop <= $endID; $loop++) {

// Create salt for user.
mysql_query("UPDATE `Profiles` SET `Salt` = CONV(FLOOR(RAND()*99999999999999), 10, 36) WHERE `ID` = '$loop'");

// Convert MD5 password to SHA1 with salt added to end.
mysql_query("UPDATE `Profiles` SET `Password` = SHA1(CONCAT(`Password`, `Salt`)) WHERE `ID` = '$loop'");

}

Thanks for the suggestion Deano, but the SQL script does generate a unique salt value for each user (I've verified this). Also, I'm executing the migration scripts directly in MySQL, not through PHP.

What I really need to know is if this part is correct in my script:

SET Password = SHA1(CONCAT(Password, Salt))

Yes it is. field names should be properly back quoted though. Try it like this.

UPDATE `Profiles` SET `Password` = SHA1(CONCAT(`Password`, `Salt`)) WHERE `ID` > 1

I added the back quotes to make sure the mysql knows these are field values.

Oh, and in case mysql got confused and treated the valuse in CONCAT as string values instead of field values, then you might have to reimport the origional MD5 passwords.

Thanks for verifying the SHA1 script.  The script actually contains the back quotes, I removed them when I pasted it here, so it's not that.

I'm going to empty the Profiles table and run the migration script again, and check the data between the two steps. I'll post an update soon.

In the meantime if anyone has any other suggestions please feel free to post...

I just remembered something.

If dolphin loaded at least once before you converted the password, dolphin may have cached them.

User information including the encrypted password is stored in the cache directory.

You may want to try clearing the contents of the dolphin cache folder except the .htaccess file.

User information is cached in a file named userx.php where x is the member id.

Dolphin will use the cache information for that user if it's there not even looking in the database.

Hmmmm... that's very interesting...  I will be sure to clear that as well. I'll keep you posted...

Thanks again Deano for your help. The cache was the issue. I ran my migration scripts again, then cleared the user cache, and everything is working as expected.

I know its been a while on this topic, I'm basically doing the same thing. Need to migrate over 1600 or users from a non Boonex site to D7 can anyone explain how to do this, the previous site has MD5 but no Salt of SHA 1.

Thanks in advance

Charles

Anyone able to post some step by step on this?

thanks

Any ideas on this?