When a new coder/developer needs to store user name and passwords in a SQL-Server database they tend to create a table that stores passwords in clear text. For these coders/developers they may look at hashing and other methods and think this is way too much work right now and think they will get back to refactoring the code yet never do in most cases.
Using PWDENCRYPT
An easy method to store passwords is PWDENCRYPT which might not be supported in future releases of SQL-Server, the second example uses HASHBYTES which offers more options.
Using HASHBYTES
Another method provides more options using HASHBYTES. In the following example only the password is protected, if you like, the next step would be to concatenate the user name and password together.
Stored procedure
In this case a stored procedure is needed.
CREATE OR ALTER FUNCTION Password_Check ( @v1 VARCHAR(500) )
RETURNS VARCHAR(7)
AS
BEGIN
DECLARE @result VARCHAR(7);
SELECT @result = (CASE
WHEN [Password] = HASHBYTES('SHA2_512', @v1) THEN
'Valid'
ELSE
'Invalid'
END
)
FROM Users1
WHERE [Password] = HASHBYTES('SHA2_512', @v1);
RETURN @result;
END;
Validation method
Method to validate user name and password.
public class DataOperations
{
public static (bool, Exception) ValidateUserLogin(string username, SecureString password)
{
using var cn = new SqlConnection(ConfigurationHelper.ConnectionString());
using var cmd = new SqlCommand() { Connection = cn };
/*
* Note:
* (@Password) as ValidItem is used if you want to load results into a DataTable which
* makes it easy for debugging, otherwise no need for the alias.
*/
cmd.CommandText = "SELECT Id,[dbo].[Password_Check] (@Password) as ValidItem " +
"FROM dbo.Users1 AS u WHERE u.UserName = @UserName";
cmd.Parameters.Add("@UserName", SqlDbType.NChar).Value = username;
cmd.Parameters.Add("@Password", SqlDbType.NChar).Value = password.ToUnSecureString();
try
{
cn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
return (reader.GetValue(1) != DBNull.Value, null)!;
}
else
{
return (false, null)!;
}
}
catch (Exception exception)
{
return (false, exception);
}
}
}
Unit test
And a unit test method for testing the method above.
[TestMethod]
[TestTraits(Trait.PasswordCheck)]
public void ValidLoginTest()
{
// arrange
var userName = "payneoregon";
var password = "!FirstOnMonday";
// act
var (success, exception ) = DataOperations
.ValidateUserLogin(userName, password!.ToSecureString()!);
// assert
success.ShouldBeTrue();
}
How to add a record
Shown below, the key is HASHBYTES('SHA2_512', @Password).
Note that the second query returns the key to the newly added record.
public static int AddUser(User user)
{
using var cn = new SqlConnection(ConfigurationHelper.ConnectionString());
using var cmd = new SqlCommand() { Connection = cn };
cmd.CommandText = "INSERT INTO Users1 (UserName, [Password]) VALUES (@UserName, HASHBYTES('SHA2_512', @Password));" +
"SELECT CAST(scope_identity() AS int);";
cmd.Parameters.Add("@UserName", SqlDbType.NChar).Value = user.Name;
cmd.Parameters.Add("@Password", SqlDbType.VarChar).Value = user.Password.ToUnSecureString();
cn.Open();
return Convert.ToInt32(cmd.ExecuteScalar());
}
Summary
With the information provided a coder/developer need not have to store passwords as clear text which compromises user information.
Source code
See the following GitHub repository which includes
- A class project for
- Validating user name/password
- Adding a new user
- Console project to test code from above and a script to create the database
- A simple unit test project for validating a login.
Top comments (0)