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
– pvg, Commented Dec 22, 2015 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 mssqltips.com/sqlservertip/2988/… for more info. — eftpotrm
– eftpotrm, Commented Dec 22, 2015 at 17:30
Thanks @eftpotrm I was using MD5 just for testing purposes. Turns out we are using SHA1. — iamtheoracle
– iamtheoracle, Commented Dec 22, 2015 at 17:39

Akshey Bhat · Accepted Answer · 2015-12-22 17:04:35Z

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, 2015 at 17:04

Akshey Bhat

8,6872 gold badges23 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

HKImpact · Accepted Answer · 2015-12-22 17:01:53Z

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, 2015 at 17:01

HKImpact

6201 gold badge10 silver badges23 bronze badges

Collectives™ on Stack Overflow

SQL Server HashBytes Function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related