SQL Server query not matching for a varbinary type column

Question

I am using this queries below to validate a user.

This query works perfectly fine in SQL Server and in my Asp.Net website.

SELECT * 
FROM AdminUsers 
WHERE username = 'admin' COLLATE SQL_Latin1_General_CP1_CS_AS 
  AND Password = (SELECT HASHBYTES('SHA1', 'admin123'))

However when I put it in Asp.net/ C# code as :

dbManager.Command.CommandText = @"SELECT * FROM AdminUsers 
WHERE username= @UserName COLLATE SQL_Latin1_General_CP1_CS_AS AND
Password = (SELECT HASHBYTES('SHA1', @Password))";

dbManager.Command.Parameters.AddWithValue("@userName", username);
dbManager.Command.Parameters.AddWithValue("@Password", password);

reader = dbManager.GetDataReader();
if (reader.Read() == true)
{ //USER VALIDATED }

This does not match so not sure how to assign the password parameter so it works, just to confirm password entered is correct. And Password datatype in SQL Server table is VarBinary.

Any suggestions?

Answer 1

The data type of your @Password parameter is not correct.

In your first example, 'admin123' is a varchar ascii string.

In your .net code @Password is a string, so is set to type nvarchar unicode string by default. These hash to different values, i.e.:

select hashbytes('SHA1', 'admin123')
select hashbytes('SHA1', N'admin123')

returns

0xF865B53623B121FD34EE5426C792E5C33AF8C227
0xB7BC3A1B04D9E165C6762B0A1CDE5226DF5B6A6A

You need to set it to a varchar SqlDbType:

dbManager.Command.CommandText = @"SELECT * FROM AdminUsers 
WHERE  username= @UserName COLLATE SQL_Latin1_General_CP1_CS_AS AND
Password=(SELECT HASHBYTES('SHA1',@Password))";

dbManager.Command.Parameters.AddWithValue("@userName", username);
dbManager.Command.Parameters.AddWithValue("@Password", password)
    .SqlDbType = SqlDbType.VarChar;

reader = dbManager.GetDataReader();
if (reader.Read() == true)
{ //USER VALIDATED }

Answer 2

@userName and @Password are both nvarchar. HASHBYTES is very sensitive to varchar vs nvarchar; in your working example, 'admin123' is varchar. You'll need to explicitly specify those parameters as varchar (by setting .DbType = System.Data.DbType.AnsiString), or (preferable) use nvarchar throughout (too late for that if already hashed). Note: HASHBYTES by itself probably isn't a great choice for cryptography.