How do I batch encrypt existing passwords in a MySQL db?

Question

I am updating a web system for a client, and his 'accounts' table has over 20,000 users, each with a password field of straight text, meaning none of the passwords are encrypted. (I know, scary!) Anyway, I need to know how to take ALL of these passwords and easily encrypt them, preferably in batch style, and import them back into the db. I couldn't find much in searches. I did see B-Crypt, but that looks to be used mostly for files. I'm pretty novice at this so ANY suggestions would be greatly appreciated! Thanks. :)

Bueller · Accepted Answer · 2011-07-18 15:17:22Z

2

Here is one way to do this.

UPDATE table_Users
SET password = EncryptionFunctionYouChoose(password);

EDIT where "table_Users" is the table where your password is held and "password" is the column name for the password. END EDIT

Although a better idea would be to hash each password with an individual salt value rather than encrypting. (my opinion)

answered Jul 18, 2011 at 15:17

Bueller

2,32417 silver badges11 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 12:04:16Z

1

As far as I know MySQL does not provide in-built B-CRYPT function. Refer. If you just want to solve this issue by using MySQL queries you can do something like

update users set password = SHA2(password, 256);

But if you do like to BCrypt, I guess you will have to do it at app level.

There are always interesting discussions on storing password in database. See this list

edited May 23, 2017 at 12:04

CommunityBot

11 silver badge

answered Jul 18, 2011 at 15:21

Nishant

56.1k13 gold badges119 silver badges131 bronze badges

Collectives™ on Stack Overflow

How do I batch encrypt existing passwords in a MySQL db?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related