Hashing Password in ASP.NET and Resolving it In SQL Procedure

Question

I'm developing an ASP.NET application using SQL Server Stored Procedures. I need to hash my login password and resolve it in my sp_LoginCheck Stored Procedure.

Any suggestions?

I have already inserted data in the database.

For example:

UserName/Password

ABC/123456

DEF/987654

I want to encrypt or hash whatsoever the password then decrypt it in the stored procedure and query the table so that I can acquire the data.

Answer 1

A very simple aproach is to use a MD5 hash.

public class MD5
{
    public static string Hash(string message)
    {
        // step 1, calculate MD5 hash from input
        System.Security.Cryptography.MD5 md5 = System.Security.Cryptography.MD5.Create();
        byte[] inputBytes = System.Text.Encoding.ASCII.GetBytes(message);
        byte[] hash = md5.ComputeHash(inputBytes);

        // step 2, convert byte array to hex string
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < hash.Length; i++)
        {
            sb.Append(hash[i].ToString("X2"));
        }
        return sb.ToString().ToUpper();
    }
}

Then in your application

You say

var password = MD5.hash(passwordField);

And store that in the DB.

When validating the password you just say something like

db.Account.Where(w => w.email == emailField && w.password == MD5.hash(passwordField)

to see if you have a matching record.

As @zulq said there are better systems something that has a salt etc, however for basic password hashing as you requested, this will work.

However if you wish to do all this in a stored procedure. You can use the following HASHBYTES function in SQL

HASHBYTES('md5', 'your password')

So same again when calling the stored procedure, you pass it the plain text password it hashes and stores

When validating you pass a stored procedure the username / password it validates and returns true or false or a row.

Answer 2

I think you need to be more specific on what exactly you want. However there's a full answer below. Stored Procedure:

USE [YourDB]
GO
CREATE PROCEDURE [dbo].[sp_LoginCheck] @UserID varchar(25), @password varchar(25)
AS
SELECT username, user_password FROM UserPassword 
WHERE username = @UserID and user_password=@password
GO

VB Code:

  Public Function validateUser(username As String, password As String) as Boolean
       Using sqlCon = new SqlConnection(yourConStr)
          Dim cmd = new SqlCommand("sp_LoginCheck",sqlCon)
          cmd.CommandType = CommandType.StoredProcedure
          cmd.Parameters.AddWithValue("@UserID",username)
          cmd.Parameters.AddWithValue("@password",GetMd5Hash(password))
          sqlCon.Open()
          Dim dr As SqlDataReader = cmd.ExecuteReader()
          If dr.Read() Then
           Return True
          Else
           Return False
       End Using
   End Function

Note, you should enclose the code in try catch and do the password hashing. You should store the hashed password and when comparing compare with the hashed to the db. Refer to the other answer for hashing.

For your convenience straight from MSDN and slightly modified:

Shared Function GetMd5Hash(ByVal input As String) As String 
    Dim md5Hash As MD5 = MD5.Create()        
    Dim data As Byte() = md5Hash.ComputeHash(Encoding.UTF8.GetBytes(input))            
    Dim sBuilder As New StringBuilder()
    Dim i As Integer 
    For i = 0 To data.Length - 1
        sBuilder.Append(data(i).ToString("x2"))
    Next i    
    Return sBuilder.ToString()    
End Function