Replacing a md5 password column with a plain text column

[ripcurlksm]

In our database we have a 'user' table with username, password, email, etc columns. From the start we have always encrypted a user password into the database before storing it. This has been troublesome for our staff when a user asks what their password is, we can only reset it for them.

So for the past two months I have modified our login script to capture the plain text password our users are entering upon successful login. Is there an easy way to match the user id from my new passwords table and overwrite it with the encrypted password in the user table?

Data looks like this:

`user` table
user_id | username | password | email | etc

`password_helper` table
user_id | password

I want to copy all of the passwords from `password_helper` and overwrite the password rows that match with the user_id from the `user` table.

[ripcurlksm]

still working on this Any idear's?

[ScallioXTX]

There's a reason passwords are stored in md5. It's because they are private. People might use them on other websites, their bank account, whatever.

I'd strongly advise you against storing passwords plaintext. The reset method is used by 99% of the websites out there and works fine.

If your problem is that the staff has to much work resetting everyones password, you could better implement an "I forgot my password" page where a user can enter his/her e-mail-adress and if a user exists with that e-mail adress, generate a new password, update the database to the md5 of that password, and e-mail the password to the user on the given e-mail-address.

[ripcurlksm]

All valid points Scallio, and reasons I also argued for when this was currently implemented. There is a reset password feature, but the limited <200 clients we have can't be bothered with it, or can my boss. so its top down.

would a find and replace work for this? I always screw up the SQL find and replace when it replaces parts of the word for example, if there was three rows of data: 17, 31, 1 and I tried to do a find and replace for "1" and make it "2" i would end up with 27, 32, 2 instead of 17, 31, 2

[ScallioXTX]

Find what and replace it with what, exactly?

BTW, if clients call you to reset the password I'd tell them you can't be bothered to do that, they need to reset their own passwords, you've got better things to do than reset peoples password / read people their passwords all day. Come to think of it, reading out peoples password is also not a good idea, what if they don't hear you correctly / write it down wrongly, then they will call again. And again, and again, and again ...

[felgall]

If all you have is the MD5 of the password then you have no way of working out what the plain text version of the password is in order to do the substitution. That's the whole point in using MD5 to store a password in the first place - the conversion only goes one way.

[ripcurlksm]

i have a separate table that has the plain text passwords and user id. I want to take those passwords and match them with the real user table's id

[felgall]

Okay, now I understand what it is you are trying to do. still don't think it is a good idea since it could mean someone with access to your server gets passwords that just happen to work for people's bank accounts and you might end up liable for any money that gets stolen but the code to replace the passwords is simple enough.

UPDATE user, password_helper SET user.password=password_helper.password
WHERE user.user_id=password_helper.user_id;