Adding new users to a database and generating them unique passwords

Question

I currently have a large user database where every user has a unique password. All the passwords are md5 encrypted. The way I originally set it up was by converting the list of user details to SQL by saving the excel sheet I had as a CSV, and then converting that to SQL at csv2sql.com. I then used sql to create the unique passwords with the following command line:

UPDATE users SET numbers = SUBSTRING(MD5(RAND()) FROM 1 FOR 10)

To make sure this command didn't generate duplicates I made the password field a UNIQUE field. I then exported this table so I had a copy of all the original passwords. Once exported I then encrypted the list to md5 using the following command line:

UPDATE users SET `password` = MD5(`password`)

This all worked fine, albeit not the most efficient way of dealing with it. Now I have to add a whole load more users to the database. Of course I have to maintain the original passwords from the original users. I'm able to insert the new users, but I'm unsure how I'm going to create the new passwords for them without changing all the previous ones. Can someone point me in the right direction please!

does the user who enters a password which is actually in use, gets the warning: password already taken? .... — helle
– helle, Commented Jun 1, 2011 at 9:15

keppla · Accepted Answer · 2011-06-01 08:55:41Z

1

You could import your csv into a temporary table, do your modifications there, and then insert into users (...) select ... from temp_users
You could add a column 'unencrypted password' to your user table, import the csv, so that the unencrypted pw is in that column, and then run update users set password = md5(unencrypted_password) where unencrypted_password is not null
You could use a different csv-sql-converter. As a hack, i often imported the csv into excel/oo-calc, and made a column like this: =concat('insert into table (...) values (', A1, ', ', A2, ')');, which allowed me to do custom sql-statements

answered Jun 1, 2011 at 8:55

keppla

1,7712 gold badges15 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding new users to a database and generating them unique passwords

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related