T-SQL Get bytes out of VARCHAR without converting to UNICODE

Question

We store a password hash in the database. Basically we get the MD5 hash (array of bytes) and then run Encoding.Unicode.GetChars on the byte array and store that resulting string in the database. Then when someone logs in we hash their password the same way and compare the two strings.

This works great, except for that I cannot extract a password hash in T-SQL and insert it into another row. The only way I can get that to work is if I actually set the password hash directly from a select of another row with a valid hash.

I have tried to convert the string to hex and let SQL convert it, but that does not work either. I assume it's because the bytes we encode as UNICODE are probably don't make up a valid UNICODE string.

So for instance (SQL Server: Convert a string into a hex string and back):

When you run the following command, the word 'Help' gets converted to var binary and then back to a string and hey presto! You end up with 'Help' again.

SELECT CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), 'Help'))

Problem is that when I run that on my password hash column, the resulting value is not the same as is stored in the column. If I paste the string containing the hash into SQL Management studio between two ' symbols, it jumps past the ' and puts in some other characters. Again, probalby trying to interperet an incomplete UNICODE sequence seeing as it's not a proper UNICODE string.

So what I am looking for is a way to simply take the bytes in the VARCHAR(64) that contains the password hash and write it to an encode text format, something like:

0x0F037584C99E7FD4F4F8C59550F8F507

So that I can then do something like

UPDATE   [User]
SET      PasswordHash = CONVERT(NVARCHAR(64), 0x0F037584C99E7FD4F4F8C59550F8F507)
WHERE    UserID = 123

Any ideas?

Answer 1

DUH! Found it. As I was rereading my post I realized that in my examples I was using a convert to VARCHAR, not NVARCHAR. Once I changed it to NVARCHAR it all started to work.