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.
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.
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
GetMd5Hash(password) to password where you add the parameter for the password. But the best thing is to hash and salt all your passwords in the DB.