If I have a database that stores the login credentials of 1,000,000 users in PLAINTEXT, how much effort will it take me to md5 hash these passwords?

  • On a semi decent server, less than a minute. – Petah Jun 3 '11 at 11:38
  • 17
    engadget.com/2011/06/02/… -- coincidence? ;-) – vartec Jun 3 '11 at 12:06
  • 4
    Is no-one going to mention that MD5 hashing passwords is really not very good from a security perspective? Admittedly, it's better then plaintext, but still... – Ed James Jun 3 '11 at 12:14
  • @Ed I didn't see your comment; but my answer says just that. – George Stocker Jun 3 '11 at 13:40
  • What kind of effort are we talking about here? Are you asking how to do it? – Adam Lear Jun 3 '11 at 21:41

After some tinkering I generated for you a quick and dirty solution


  1. First create a back up of your table, (By using this code you hold yourself fully accountable for all damages)
  2. Secondly as mentioned by other users consider a different hash other than MD5
  3. Consider Thorbjørn Ravn Andersen comment on learning how to do commit and rollbacks and update the code provided accordingly.
  4. Finally consider adding some salt and pepper for taste.

The basic concept:

5  ON 
6      TARGET.id = SOURCE.id
7  SET
8      TARGET.[password_field_name] = md5(SOURCE.[password_field_name])
10     TARGET.id = SOURCE.id

What this essentially does is link the table with the password to itself with an inner join. It uses the aliases (Target and Source) to differentiate between the two copies of the table. Then using the regular Update syntax from sql, the Set statement on lines 7 and 8 causes the password field in the original table to become equal to the MD5 hash of the same password. To make sure that this sql only acts on a per row basis we establish the where statement using the primary key of the table.

| improve this answer | |
  • P.S. I ran it on some dummy data of 2 columns and ~30,000 rows; Took about ~2 seconds. So on 1,000,000 rows should take about 8 seconds ± 5. (It will probably take only 2 though) – Mallow Jun 3 '11 at 21:37
  • +1 for the effort, but don't go using MD5 for hashing. SHA256 is currently considered unbroken but is computationally expensive. Not as expensive as losing user accounts, though. – Gary Rowe Jun 3 '11 at 21:54
  • @Gary Rowe Computationally expensive is a good thing. If it's expensive for you to generate, it's expensive for them to break. – George Stocker Jun 4 '11 at 3:37

Please don't convert it to MD5; it's not considered a secure hashing scheme.

If you're going to convert them to something else; then I'd suggest reading up on Password security. The topic is way too long to go into detail here.

Also, could you tell us the company so we can be sure not to store our credentials there?

| improve this answer | |
  • 1
    MD5 is not a protocol – user1249 Jun 3 '11 at 13:40
  • @Thor Yup, I edited it about 9 seconds before you wrote that comment because I noticed the same typographical error in my post. – George Stocker Jun 3 '11 at 13:49

Your effort = the same as if it were 10 rows. Just convert one, and put it in a loop.

Computers effort = effort (time) of converting one row * 1000000

The exact execution time of creating a md5 from plain text is a measurable value. The exact execution time of converting them all is predictable from the first result.

I reckon that your own effort would actually mostly be spend to the other parts of your login system and not this one, like forgotten password and similar. E.g. if the previous system used to send exact password to users, you'll have to replace it with a password reset functionality.

| improve this answer | |
  • Can be much faster if you use batching. – user2567 Jun 3 '11 at 11:33
  • 1
    @Pierre: True. Use loop to build up the batch query, then send it off to execute. In any case, it's just a run-once script. As long as it's reasonably fast and you're not paying per resource use (like on Google App Engine) whatever is easiest is the way to go. – Goran Jovic Jun 3 '11 at 11:51
  • 2
    @Pierre, oh the joys of commit-rollback. Use MySQL - much easier :) – user1249 Jun 3 '11 at 11:53
  • @Thorbjørn Ravn Andersen: no need, by default each command has its implicit transaction (if you don't specify one). – user2567 Jun 3 '11 at 12:56
  • @Pierre, depends on the semantics of the given driver technology. – user1249 Jun 3 '11 at 13:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.