Search code examples
sql-serverprepared-statementsha1

SQL Server sha1 value in prepared statement gives a different value than hardcoded string

I am trying to encrypt a password in SQL Server and I'm getting two different results when I use a string vs. using a prepared statement parameter.

For example:

SELECT 
    sys.fn_varbintohexstr(HASHBYTES('sha1', ?)),
    sys.fn_varbintohexstr(HASHBYTES('sha1', 'password'))

Where the ? is populated by 'password'. It gives me

0xe8f97fba9104d1ea50479...
0x5baa61e4c9b93f3f06822...

Why am I getting two different results for what should be the same thing?

Also, this is only happening in SQL Server, if I do a similar query in MySQL, it returns the same value for both.

I know I should be using better encryption, but I am stuck with sha1 (no salt) for now.

Thanks

Solution

  • One is a varchar the other nvarchar

    SELECT 
 sys.fn_varbintohexstr(HASHBYTES('sha1','password')),
 sys.fn_varbintohexstr(HASHBYTES('sha1',N'password'))

    returns

    0x5baa61e4c9b93f3f0682250b6cf8331b7ee68fd8
0xe8f97fba9104d1ea5047948e6dfb67facd9f5b73