Sql Server 2008 - How can I select all rows and update a field with its hash code?

Question

Hi everyone!

In my project, I'm in a point that is lack of knowledge of sql programming. I have a tbUsers with a passowrd field. But, now I have to trasnform them to hash MD5, so far so good for the hash.

But, how can I select all passwords recorded and update them with its hashvalue? I don't really need the hashvalue part, just the select all and update each single row.

And since the HashBytes code is

HashBytes('MD5', 'MyPassword')

I need to take each row 'MyPassword' and do the hash part;

Thanks!!

Answer 1

If indeed HashBytes() is as described, this is all that you may need

   UPDATE tblUsers
    SET password = HashBytes('MD5', password)
    --WHERE   -- here you could have some condition if somehow you didn't want 
              --all of the row updated.

However, you may consider instead, modifying the table to have an new column, call it md5, and instead do 'SET md5 = ... ' this would allow you to keep the passwords temporarilly, which could come handy, in cases things do not work, or if you want test a few logins with the asssociated MD5 login.

Answer 2

The solution that was useful for me is this:

UPDATE tbUsers
SET password = SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5', tbUsers.password)),3,32)
FROM tbUsers INNER JOIN tbUsers tbUsers2
ON tbUsers.codUser = tbUsers2.codUser

Answer 3

I'm not sure the reason for this, but for the record, the hash of a password offers little protection against password discovery for someone who can access the hashed password, at least for weak passwords. It's simple to precalculate the MD5 hashes of a few billion potential passwords (words, names, the same with A->4, I->1, O->0 substitutions, words doubled or with a 1 tacked on the end, short random strings, etc.) and store them, indexed, in a table with the passwords that generated them. Find a hashed password, and if the password is one of those billions, you find it in a flash. You can also find out if groups of people all use the same password (if you have access to all the password hashes).

If you're doing this for security, look into the possibility of storing hashed salted passwords as a start.