C# Question
Why does SqlCommand not pass parameter inside a HASHBYTES function?
I have a simple
SqlConnectionusing (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
Recommended for you: Get network issues from WhatsUp Gold. Not end users.
Answer Source
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.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download