SQL Server HashBytes Function

Question

Can someone please tell me why I'm getting different answers for the following:

Here is a very simple function used to return the hash Value:

CREATE Function [fn_GetPin](@strText VARCHAR(254))
RETURNS VARCHAR(254)
AS
BEGIN
    SET @strText = HashBytes('MD5', @strText);
    RETURN @strText;
END

Here is my SQL that returns 2 different values:

SELECT HashBytes('MD5', '7690') as Hash1
        , dbo.fn_GetPin('7690') AS Hash2

Results:

Hash1   Hash2
0xC6776F93BC9AFE2E511A1DBD5478362C  Æwo“¼šþ.Q½Tx6,

For some reason the function is not returning the same value as HASH1. I'm just not sure why.

Thanks.

UPDATE:

Accepted answer is below, I just added (max) to the VARBINARY to return the whole value. Thanks All.

CREATE Function [fn_GetPin](@strText VARCHAR(254))
RETURNS VARBINARY(max)
AS
BEGIN
    Return HashBytes('MD5', @strText);
END

I think this is because the function HashBytes returns varbinary and your (presumably interactive?) result is a bit of hex. Your function forces the return of varchar so it's showing you the binary blob interpreted as a string. — pvg, Dec 22 '15 at 16:26
Little thing, but MD5 isn't a recommended hash algorithm for what you look like you're doing any more (and ideally the hash would be salted as well). Try SHA1 if you're running on <2012, or SHA2_256 or SHA2_512 otherwise. Have a look at https://www.mssqltips.com/sqlservertip/2988/understanding-the-sql-server-hashbytes-hashing-algorithms/ for more info. — eftpotrm, Dec 22 '15 at 17:30
Thanks @eftpotrm I was using MD5 just for testing purposes. Turns out we are using SHA1. — iamtheoracle, Dec 22 '15 at 17:39

score 2 · Accepted Answer · answered Dec 22 '15 at 17:04

2

CREATE Function [fn_GetPin](@strText VARCHAR(254))
RETURNS VARBINARY
AS
BEGIN
    Return HashBytes('MD5', @strText);
END

function should return a varbinary value.

answered Dec 22 '15 at 17:04

Akshey Bhat

8,227
1
20
20

score 1 · Answer 2 · answered Dec 22 '15 at 17:01

1

pvg pretty much nailed it above.

CREATE Function [fn_GetPin](@strText VARCHAR(254))
RETURNS VARBINARY
AS
BEGIN
    SET @strText = HashBytes('MD5', @strText);
    RETURN CAST(@strText as VARBINARY);
END

answered Dec 22 '15 at 17:01

HKImpact

610
1
9
23

SQL Server HashBytes Function

2 Answers2