SQL dumps raw data for MD5 encryption

First to describe the situation, first there is a batch of users before the default password for 6 8 and then after the MD5 encryption for storage, it is now necessary to change according to the user ID number 6 digits as the password.

1. First found that we sqlserver05 the above version is the MD5 encryption method, and then we verify that its encrypted data with the. NET comes with the MD5 encryption method is the same after encryption.

SQL Server Fetch MD5 method: Select Hashbytes (' MD5 ', ' 888888 ')

The result is that the 16 binary is not the same as what we need.

Make a built-in conversion and intercept the data that forms the MD5 encryption we need to result in

Then we test the encrypted data in. NET to see if the encryption is compliant

Find the same value as the encryption we have in SQL, which means that the encryption method is consistent.

2. Down we go to the database to operate!

After performing the update operation to the system after the check found that the wrong up.

Go back to the database and check. (The search is consistent, this way because the user name is the ID card number (userid), so the userid of the table after the interception of 6 bits and then encrypted processing)

Select Top substring (sys.fn_sqlvarbasetostr (hashbytes (' MD5 ', substring (UserID) -5,6)), Userid,len), SUBSTRING (Userid,len (UserID) -5,6), UserID from db_owner. SystemUser where db_owner. Systemuser.roleguid=25

The result is that the data that was intercepted from the database and then encrypted is different from the value that we manually entered.

We later found that we used the nvarchar type when we stored the UserID, which is actually twice times the number of bytes. The value after encryption is different for this reason.

After it has been converted to varchar for testing

Select Top substring (sys.fn_sqlvarbasetostr (hashbytes (' MD5 '), CAST (substring (Userid,len (UserID) -5,6) as varchar ( )), 3,32), substring (Userid,len (UserID) -5,6), UserID from db_owner. SystemUser where db_owner. Systemuser.roleguid=25

The findings are the same as the results we expected.

SQL dumps raw data for MD5 encryption