Why does SqlCommand not pass parameter inside a HASHBYTES function?

Question

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?

Answer 1

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.