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?

As Marc said, you should not reinvent security practices or implement naively. SHA1 is fast and relatively weak so is not a good choice for password hashing. There are a whole load of libraries that can do this for you in .Net, please use those. msdn.microsoft.com/en-us/library/… for example — Lukos
– Lukos, Commented Dec 15, 2014 at 12:30

Rhumborl · Accepted Answer · 2014-12-15 12:17:07Z

7

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 }

answered Dec 15, 2014 at 12:17

Rhumborl

16.7k4 gold badges41 silver badges47 bronze badges

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

1 Comment

confusedMind Over a year ago

Thank you for that, one question reading the comments above , is this weak approach to use for setting passwords?

Marc Gravell · Accepted Answer · 2014-12-15 12:17:00Z

1

@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.

answered Dec 15, 2014 at 12:17

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Collectives™ on Stack Overflow

SQL Server query not matching for a varbinary type column

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related