Jakub Lokša Jakub Lokša - 1 month ago 7
C# Question

Why does SqlCommand not pass parameter inside a HASHBYTES function?

I have a simple

SqlConnection
code, which has a HASHBYTES function in it to retrieve data from my server.

using (var connection = new SqlConnection(connectionString))
{
connection.Open();

using (var command = new SqlCommand(commandString, connection))
{
command.Parameters.Add(new SqlParameter("mail", email));
command.Parameters.Add(new SqlParameter("password", password));
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
return true;
}
throw new InvalidDataException();
}
}
}


The commandString property looks like this:

DECLARE @pass varchar(50);
SET @pass = @password;

DECLARE @pwdHash varbinary(max);
SET @pwdHash = HASHBYTES('SHA2_256', @pass);

SELECT * FROM Users
WHERE email=@mail AND pwd=@pwdHash;


This code works, but why doesn't the shorter code below work?

In the case below, the function doesn't go inside the while loop and instead throws an exception.

DECLARE @pwdHash varbinary(max);
SET @pwdHash = HASHBYTES('SHA2_256', @password);

SELECT * FROM Users
WHERE email=@mail AND pwd=@pwdHash;


In both of these codes, the @password value is set as a
SqlParameter
, so it should work, right? Or am I missing something?

Dai Dai
Answer

String parameters are passed as nvarchar by default; but your longer command casts @password to varchar which has a different binary representation and so would generate a different hash digest which would not match your existing records if their hashes were generated differently.

BTW, you should salt your hashes too.

Comments