Convert HashBytes to VarChar

Question

I want to get the MD5 Hash of a string value in SQL Server 2005, I do this with the following command:

SELECT HashBytes('MD5', 'HelloWorld')

However, this returns a VarBinary instead of a VarChar value. If I attempt to convert "0x68E109F0F40CA72A15E05CC22786F8E6" into a VarChar I get "há ðô§*à\Â'†øæ" instead of "68E109F0F40CA72A15E05CC22786F8E6".

Is there any SQL based solution?

Yes

Answer 1

You may want to try to get rid of the 0x using SUBSTRING or similar.-

Answer 2

Michael,

I have tried this, but the substring function converts the varbinary into a varchar datatype and in doing so, return rubbish instead of the MD5 hash :(

The simplest solution would be to store the output value into a column of varbinary datatype, however, I cannot edit the database I have to work with.

Ste.

Answer 3

You cannot recover a MD5 hashed password. You can only use its value to compare it and see if there is a match:

DECLARE @password VARBINARY(150)
SET @password = HashBytes('MD5', 'mypassword')
SELECT @password

IF (HashBytes('MD5', 'mypassword') = @password)
SELECT 'Match!'
IF (HashBytes('MD5', 'myspecialpassword') = @password)
SELECT 'Match!'

I would highly recommend you to salt all your password since the MD5 is vulnerable to any force brute attack.

Answer 4

jdecuyper,

The situation is that I have to create user account, via sql, for a system that we cannot change (3rd party). The password are (unfortunatly) stored as a simple MD5 hashed string of datatype varchar.

My original question wasn't about recovering MD5 hashed passwords, this know, but I want to know how to convert and varbinary into a varchar and still keep the viable hash instead of gibberish.

Answer 5

I have found the solution else where:

SELECT SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('MD5', 'HelloWorld')), 3, 32) 

Answer 6

Changing the datatype to varbinary seems to work the best for me.