Encrypting Database Passwords using MD5

Today, I will show you a very basic technique to encrypt database passwords for storage using MD5 algorithm. MD5 is a one-way algorithm; decrypting is almost next to impossible. One very important characteristic of this algorithm is that a particular text value would always result in the same encrypted value. Therefore, even though the encrypted value cannot be decrypted directly, we do have a way to verify such passwords.

Let us get down straight to code and see for ourselves:

 1 Dim connectionString As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=D:\rafay\projects\certification\encrypt\App_Data\members.mdf;Integrated Security=True;User Instance=True"
 2 
 3         Dim sqlStatement As String = "INSERT INTO users VALUES(@myuser,@mypassword)"
 4         Using objConn As SqlConnection = New SqlConnection(connectionString)
 5             Using objComm As SqlCommand = New SqlCommand(sqlStatement, objConn)
 6                 Dim userNameParameter As New SqlParameter("@myuser", Data.SqlDbType.NVarChar, 15)
 7                 userNameParameter.Value = "Test"
 8 
 9                 Dim passwordParameter As New SqlParameter("@mypassword", Data.SqlDbType.Binary, 16)
10                 passwordParameter.Value = encryptPasswordWithMD5("TestAccountPassword")
11 
12                 Dim objCommParameterArray() As SqlParameter = {userNameParameter, passwordParameter}
13                 objComm.Parameters.AddRange(objCommParameterArray)
14 
15                 objConn.Open()
16                 Dim result As Integer = objComm.ExecuteNonQuery()
17             End Using
18         End Using
19 

This is our very basic example of connecting to SQL Server Express database file called members.mdf. We set up a sql statement and two parameters for username and password. The important part to look at is that the password is first encrypted using our helper function called encryptPasswordWithMD5. Another thing to note is that the database field for password is defined as a binary type of length 16. Once the parameters are defined and their values set, a parameter array is formed and then added to the SQLCommand object. The record is finally inserted with a call to ExecuteNonQuery.

Let us now have a look at our encryptPasswordWithMD5 function.

 1 Private Function encryptPasswordWithMD5(ByVal stringToEncrypt As String) As Byte()
 2         Dim MD5Encrypter As New MD5CryptoServiceProvider()
 3         Dim stringBytes() As Byte = convertStringToBytes(stringToEncrypt)
 4         Dim bufferToHoldEncryptedData() As Byte = Nothing
 5 
 6         bufferToHoldEncryptedData = MD5Encrypter.ComputeHash(stringBytes)
 7 
 8         Return bufferToHoldEncryptedData
 9     End Function
10 
11     Private Function convertStringToBytes(ByVal stringToConvert As String) As Byte()
12         Return Text.Encoding.UTF8.GetBytes(stringToConvert)
13     End Function

Line 3 of the code above is of particular interest. Since the ComputeHash method of MD5CryptoServiceProvider class takes in a byte value, our very first task is to convert the password string into a byte. This is accomplished using our second helper function convertStringToBytes (also defined in the listing above). What convertStringToBytes does is to take a string value and return the bytes using GetBytes method of the Encoding class in System.Text namespace. Once this is done, we calculate the hash and store in a buffer and return this buffer.

So, how do we actually verify the credentials since there is no way to decrypt? Well, since MD5 always results in the same hash for the same text, we can encode the text again and compare this against the value in the database. Code sample follows:

 1 Public Function checkPassword(ByVal userName As String, ByVal myPassword As String) As Boolean
 2         Dim connectionString As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=D:\rafay\projects\certification\encrypt\App_Data\members.mdf;Integrated Security=True;User Instance=True"
 3         Dim result As Integer = 0
 4         Dim sqlStatement As String = "SELECT count(*) from users Where myuser=@myuser AND mypassword=@mypassword"
 5         Using objConn As SqlConnection = New SqlConnection(connectionString)
 6             Using objComm As SqlCommand = New SqlCommand(sqlStatement, objConn)
 7                 Dim userNameParameter As New SqlParameter("@myuser", Data.SqlDbType.NVarChar, 15)
 8                 userNameParameter.Value = userName
 9 
10                 Dim passwordParameter As New SqlParameter("@mypassword", Data.SqlDbType.Binary, 16)
11                 passwordParameter.Value = encryptPasswordWithMD5(myPassword)
12 
13                 Dim objCommParameterArray() As SqlParameter = {userNameParameter, passwordParameter}
14                 objComm.Parameters.AddRange(objCommParameterArray)
15 
16                 objConn.Open()
17                 result = CInt(objComm.ExecuteScalar())
18             End Using
19         End Using
20         If result > 0 Then
21             Return True
22         Else
23             Return False
24         End If
25     End Function
26 End Class

In the first code above, I have hardcoded the username and password values. These would naturally need to be changed and the source of input would be probably be a CreateUserWizard or another composite control with various textboxes. The values were hardcoded for simplicity.