Using HashBytes in SQL Server returns different re

问题:

I try to calculate md5 hash on a certain value, but I get a weird result.

I run it in two different ways:

SELECT HASHBYTES('md5',ZLA_PASSWORD),ZLA_PASSWORD, len(ZLA_PASSWORD) FROM ZLA_PASSWORD;

SELECT HASHBYTES('md5', '123456');

I get two different results, where only the second one is valid:

0xCE0BFD15059B68D67688884D7A3D3E8C  123456  6
0xE10ADC3949BA59ABBE56E057F20F883E

This is done on an SQL Server 2005.

checking the result of MD5 on 123456 was the same as the second result checking online.

Any ideas?

Thanks!

回答1:

You have different data types

declare @str1 as varchar(10)
declare @str2 as nvarchar(10)

set @str1 = '123456'
set @str2 = '123456'

select
  hashbytes('md5', @str1) as 'varchar',
  hashbytes('md5', @str2) as 'nvarchar'

Result

varchar                             nvarchar
0xE10ADC3949BA59ABBE56E057F20F883E  0xCE0BFD15059B68D67688884D7A3D3E8C

回答2:

LEN Trims the contents before returning the length (of the trimmed string.)

Most likely your password field is a CHAR field and got whitespace in there.

Try doing a RTRIM before hashing:

SELECT HASHBYTES('md5',RTRIM(ZLA_PASSWORD))

回答3:

More exactly this should solve the issue:

SELECT HASHBYTES('md5',CAST(ZLA_PASSWORD AS varchar)),ZLA_PASSWORD, len(ZLA_PASSWORD) FROM ZLA_PASSWORD;