I have a site 'accounts' table which contains account details for all our users.
id-username-password-etc
We also have a forum (phpBB) that I'm trying to integrate with the site. The integration is done by creating an entry in the phpBB users table with the same username and password as the main account.
Our system uses md5 hashed passwords but the latest version of phpBB3 has a different format (Phpass) so I need to write a script that will replace the phpBB password with the 'account' password for roughly 300,000 users.
What would be the best way of approaching this?
I've got a query that joins the two tables together based on the username, I could write something in PHP to go through each record and update it (might cause performance issues?)
or do the conversion in MySQL?
Is there another way of doing this?
Just to clarify, when these accounts were created in both tables ... we used the same usernames and passwords for both, so each record in our_db.accounts has the md5 hash of the same encrypted password in phpbb.users. What I'm trying to do for this large dataset is replace the password in the phpBB table with the one from our accounts table
You cannot convert your MD5-hashes password to any other format because hash values are not reversible.
Integration must take place with one app using the login services of the other you add phpbb password to their database as users successfully logged into your app (in that momemt you have the clear text password for a second).
Edit: In case passwords hash techniques match:
UPDATE phpBBdb.accounts SET password = (SELECT password FROM yourApp.accounts ya WHERE (ya.username = phpBBdb.accounts.username))
Might depend a little on database types/version.
But: This means both systems use no salt or the same salt. If no salt think about adding a salt in the future. This will protect decoding md5-hashed-password with rainbow-tables and is your responsibility against your users.