Squeezing in one last coding post before the new year! I have been putting off the login logic for my project because of my sub-project choices, it was the least interesting. And the most daunting. However, I sucked it up and started implementing bits (no pun intended) of it at a time. The one part that had me ground to a complete stop was when I tried to verify a valid login against the database and it kept coming up false.

The Setup: I have a table that stores User info, the basics of which are the login/username (nvarchar) and password (binary).

I have not found out if it is better to store the password as a string/char or binary. I am worried the binary format will present problems later on down the line. Strings are always much easier to work with for an apple to apple comparison in most cases. Although, it does seem that .NET is able to talk with Sql Server, in terms of being able to transmit byte[] back and forth.

My “naive” approach was to use a simple SQL SELECT:

SELECT * FROM [User]
WHERE [User].[Login] = @Login
	AND [User].[Password] = HASHBYTES('SHA1', @Password)

This did not work. Replacing the variables with the actual strings did work, but for whatever reason passing a variable into HASHBYTES() was not generating the same hash value.

My next approach was to generate the hash value in .NET and pass that into the query:

SELECT * FROM [User]
WHERE [User].[Login] = @Login
	AND [User].[Password] = @Password

I tried passing in a string and even tried checking the numeric value in Management Studio, but I was still getting false. Then I realized, the trick here is since I declared the Password column to be of binary type, I had to pass in a byte array. After all, HASHBYTES() returns a byte array, despite the mundane-looking hex value in Sql Server Management Studio, which makes it look like a string (e.g., 0x12345abcde…) or number.

My code for generating the hash byte[]:

public byte[] GetPasswordHash(string password)
{
	byte[] data = Encoding.UTF8.GetBytes(password)
	byte[] dataHash = null;
	using (SHA1 sha1 = SHA1.Create())
	{
		dataHash = sha1.ComputeHash(data)
	}
	return dataHash;
}

I imagine inserting a new User would follow the same concept (e.g., INSERT INTO User VALUES (@Login, @Password), where @Password is a byte[]). I have read articles about encoding playing a role in generating different hash values, so I’ll have to be diligent and test that…later.

Sidenote: Yes, I am aware of salting and using a stronger encryption than SHA1.