SQL Server HASHBYTES conversion inconsistency?

Question

When I execute this hardcoded, I get the correct result:

Declare @result nvarchar(32)

Set @result = convert(varchar(32), hashbytes('MD5', '1' + 'One' + 'Two' + 'Three'), 2)

select @result

Result: 4173AB4C6EE66BC1FF7B7E5D44A872CA (correct)

But when I call/execute this stored procedure, giving it the same parameters, it's a different result

ALTER Procedure [db_owner].[CheckTheTransaction]
    @DataID nvarchar(50),
    @Data1 nvarchar(50),
    @Data2 nvarchar(50),
    @Data3 nvarchar(50)
as
    Declare @result nvarchar(32)
    Set @result = convert(varchar(32), hashbytes('MD5', @DataID + @Data1 + @Data2 + @Data3), 2)

    Select @result

My execution:

DECLARE @result int

EXEC    @result = [db_owner].[CheckTheTransaction]
        @DataID = '1',
        @Data1 = 'One',
        @Data2 = 'Two',
        @Data3 = 'Three'

SELECT  'Result' = @result

GO

Result: 5BD42777932EE959AD5A4C9FEE142F00 (wrong)

Where did I go wrong?

I'm guessing `'1'` is VARCHAR and you are passing NVARCHAR. I'm willing to bet both values are right - just the second one is UTF16. — ta.speot.is, Dec 01 '16 at 12:21
Although your hash function seems bad generally, `@Data1 = 'XX', @Data2 = 'YY'` and `@Data1 = 'XXY', @Data2 = 'Y'` will return the same hash value. — ta.speot.is, Dec 01 '16 at 12:24

score 2 · Answer 1 · answered Jul 05 '17 at 14:18

2

That is a data type issue. You will see it match by changing the T-SQ script to followings.

Declare @result nvarchar(32)

Set @result = convert(varchar(32), hashbytes('MD5', N'1' + N'One' + N'Two' + N'Three'), 2)

select @result

answered Jul 05 '17 at 14:18

user8259799

21
2

score 1 · Answer 2 · answered Dec 01 '16 at 12:24

1

Change all nvarchar datatype as varchar

ALTER Procedure [db_owner].[CheckTheTransaction]
@DataID varchar(50),
@Data1 varchar(50),
@Data2 varchar(50),
@Data3 varchar(50)
as
  Declare @result nvarchar(32)
  Set @result = convert(varchar(32), hashbytes('MD5', @DataID + @Data1 +         
  @Data2 + @Data3), 2)

Select @result

answered Dec 01 '16 at 12:24

Mansoor

4,061
1
17
27

That worked, but now i am unable to provide it with characters like æøå which i have to – humudu Dec 01 '16 at 12:57
You probably want UTF8 encoded text, then. ASCII characters = one byte, fancy characters = more than one byte. This is actually kind of hard in SQL Server. http://stackoverflow.com/questions/12512687/sql-server-utf8-howto – ta.speot.is Dec 01 '16 at 14:10

score 1 · Accepted Answer · edited May 23 '17 at 12:01

1

My team member asked a similar question and accepted the answer that solved it. Comparing a C# generated Checksum with a SQL Server one

edited May 23 '17 at 12:01

Community

1
1

answered Dec 04 '16 at 12:40

humudu

699
1
7
13

SQL Server HASHBYTES conversion inconsistency?

3 Answers3

Linked