Generate secure user passwords using the MD5 hash class module

[topher23]

I've seen a lot of questions about how to make secure database passwords. I'm going to go over a method of encrypting a password using the MD5 encryption algorithm for maximum security.

First, you will need to download the attached class module (clsMD5.txt) and import it into your database. This class module is the core of what we're about to go over. Thanks to Robert Hubley for writing it - you're my hero!

Next, your database needs a users table. We'll use this one as an example.

tblUsers

Field           Type
UserID          AutoNum  PK
strLastName     Text
strFirstName    Text
strMI           Text
strUserName     Text    (no duplicates)
strPassword     Text    (encrypted)
strPermissions  Text    (access permissions)

Now, you will need some type of "edit user options" form for a user to create a password in. Build your form, and add an UNBOUND text box control for the password. We'll call it txtTempPassword . Make sure you set the Input Mask to Password so no one can spy on the password.

So, we have the following:

Object      Name             Bound To
  
Form        frmUserOptions   tblUsers
Text Box    txtTempPassword  Unbound

Now, on the AfterUpdate Event of txtTempPassword , use the following code:

strPassword = DigestStrToHexStr(Me.txtTempPassword)

The DigestStrToHexS tr() function does the work of turning your plain-text password into a secure, MD5 encrypted mess of hexadecimal gibbersih.

Now, what do you do when the user tries to log into your custom login form?

Your login form will probably follow this basic structure:

Object      Name             Bound To
--------------------------------------------------- 
Form        frmLogin         Unbound

Text Box    txtUsername      Unbound
Text Box    txtPassword      Unbound
Button      cmdLogin

In the OnClick event of your button, you'll have the code that checks the text of the entered username and password against your table to see if there's a match. Obviously, with the password now encrypted, there won't be. The solution is to encrypt the search string using the same DigestStrToHexS tr() function. Consider the following:

Private Sub cmdLogin_Click()
Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT UserID, strUsername, strPassword FROM tblUsers", dbOpenSnapshot)
    'first, see if the username is valid
    rs.FindFirst "strUsername = '" & Me.txtUsername & "'"
    If rs.NoMatch Then GoTo ErrorOut
    'next, check the password
    If rs!strPassword = DigestStrToHexStr(Me.txtPassword) Then
        DoCmd.Close
        'open switchboard and run any other code
    End If
    Exit Sub
    'close the app if the login was incorrect
ErrorOut:
    MsgBox "Username/password combination is invalid." & _
    vbCrLf & vbCrLf & "Exiting application.", vbCritical
    Application.Quit
End Sub

You will obviously need to personalize this code to your use, but that's the idea. Have fun making databases with secure MD5 encrypted passwords!

[NeoPa]

For the checking code Topher, why not use a Domain Aggregate function instead? There is no need to loop then, and the interface is pretty straightforward .

Private Sub cmdLogin_Click()
    Dim strPW As String, strMsg As String

    strPW = DigestStrToHexStr(Me.txtPassword)
    strWhere = "[strUserName]='%U' AND [strPassword]='%P'"
    strWhere = Replace(strWhere, "%U", .txtUserName)
    strWhere = Replace(strWhere, "%P", DigestStrToHexStr(Me.txtPassword))
    If DCount("*", "[tblUsers]", strWhere) = 0 Then
        'Close the app if the login was incorrect
        strMsg = "Username/password combination is invalid." & _
                 vbCrLf & vbCrLf & "Exiting application."
        Call MsgBox(strMsg, vbCritical)
        Call Application.Quit
    End If
    Call DoCmd.Close
    'Open switchboard and/or run any other code
End Sub

[topher23]

There is no need to loop then...

I looked over and over my code looking for a loop, and then realized you probably meant this:

    If rs.NoMatch Then GoTo ErrorOut

Of course, the GoTo could easily be eliminated by rewriting the code to put the ErrorOut block into an If-Then-Else Statement.

Regarding why I choose to use recordsets over domain aggregate functions; preference more than anything. I find them to be more intuitive to use and, at least on older systems, they tend to run faster than domain aggregate functions.

The major problem with the code I posted is that there is no error-handling. If the code breaks, the recordset stays in memory, which is the only thing I dislike about recordsets. Rewriting the code, then, to add error-checking and eliminate the GoTo, I'd do something like this:

Private Sub cmdLogin_Click() 
Dim rs As DAO.Recordset 
    Set rs = CurrentDb.OpenRecordset("SELECT UserID, strUsername, strPassword FROM tblUsers", dbOpenSnapshot) 
    'first, see if the username is valid 
    rs.FindFirst "strUsername = '" & Me.txtUsername & "'" 
    If Not rs.NoMatch Then
        'next, check the password 
        If rs!strPassword = DigestStrToHexStr(Me.txtPassword) Then 
            DoCmd.Close 
            'open switchboard and run any other code 
            Exit Sub        
        End If 
    End If
    'close the app if the login was incorrect 
    MsgBox "Username/password combination is invalid." & _ 
    vbCrLf & vbCrLf & "Exiting application.", vbCritical 
    Application.Quit 


ErrorTrap:
    MsgBox "Error #" & Err & ", " & Err.Description & _
    vbCrLf & vbCrLf & "Please report error code to your System Administrator."
    'in a normal case, we'd just put in the following lines:
    rs.Close
    Set rs = Nothing
    'However, since we don't want anyone sneaking into our system 
    'by forcing an error, we'll quit the application, which will
    'clear the memory buffer anyway
    Application.Quit
End Sub

[topher23]

Also by way of clarification, I generally have different messages display for when the username is incorrect and when the password is incorrect, which is why they're evaluated separately. The single message about the username/password combination in this example was for simplicity's sake.

I also allow users three chances before I log the failed login into a table and kick them off. But again, that increases the complexity of the code for the example. I just wanted to show the MD5 hash in action, not do a full article on securing databases.

[NeoPa]

There is no need to loop then...

I looked over and over my code looking for a loop, and then realized you probably meant this:

    If rs.NoMatch Then GoTo ErrorOut

You're absolutely right there. I did misread your code. As you say, this simply comes down to preference, and while mine may be different from yours, I certainly wouldn't see your choice as wrong in any way whatsoever.

I think my brain wasn't properly kicked in at the time (Not that it should be kicked in in a literal sense of course).

[patjones]

I just want to vouch for the concept. I have used the MD5 algorithm in a number of projects for password protection and once for encryption of Social Security numbers. Generally I don't like to put Social Security numbers in my databases but on one occasion it was called for, and the MD5 hash really helped out.

Thanks for posting this!