By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,836 Members | 1,876 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Generate secure user passwords using the MD5 hash class module

topher23
Expert 100+
P: 234
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.
Expand|Select|Wrap|Line Numbers
  1. tblUsers
  2.  
  3. Field           Type
  4. UserID          AutoNum  PK
  5. strLastName     Text
  6. strFirstName    Text
  7. strMI           Text
  8. strUserName     Text    (no duplicates)
  9. strPassword     Text    (encrypted)
  10. strPermissions  Text    (access permissions)    
  11.  
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:

Expand|Select|Wrap|Line Numbers
  1. Object      Name             Bound To
  2.  
  3. Form        frmUserOptions   tblUsers
  4. Text Box    txtTempPassword  Unbound    
  5.  
Now, on the AfterUpdate Event of txtTempPassword, use the following code:
Expand|Select|Wrap|Line Numbers
  1. strPassword = DigestStrToHexStr(Me.txtTempPassword)
  2.  
The DigestStrToHexStr() 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:

Expand|Select|Wrap|Line Numbers
  1. Object      Name             Bound To
  2. --------------------------------------------------- 
  3. Form        frmLogin         Unbound
  4.  
  5. Text Box    txtUsername      Unbound
  6. Text Box    txtPassword      Unbound
  7. Button      cmdLogin
  8.  
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 DigestStrToHexStr() function. Consider the following:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdLogin_Click()
  2. Dim rs As DAO.Recordset
  3.     Set rs = CurrentDb.OpenRecordset("SELECT UserID, strUsername, strPassword FROM tblUsers", dbOpenSnapshot)
  4.     'first, see if the username is valid
  5.     rs.FindFirst "strUsername = '" & Me.txtUsername & "'"
  6.     If rs.NoMatch Then GoTo ErrorOut
  7.     'next, check the password
  8.     If rs!strPassword = DigestStrToHexStr(Me.txtPassword) Then
  9.         DoCmd.Close
  10.         'open switchboard and run any other code
  11.     End If
  12.     Exit Sub
  13.     'close the app if the login was incorrect
  14. ErrorOut:
  15.     MsgBox "Username/password combination is invalid." & _
  16.     vbCrLf & vbCrLf & "Exiting application.", vbCritical
  17.     Application.Quit
  18. 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!
Attached Files
File Type: txt clsMD5.txt (18.1 KB, 2053 views)
Dec 2 '09 #1
Share this Article
Share on Google+
5 Comments


NeoPa
Expert Mod 15k+
P: 31,306
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdLogin_Click()
  2.     Dim strPW As String, strMsg As String
  3.  
  4.     strPW = DigestStrToHexStr(Me.txtPassword)
  5.     strWhere = "[strUserName]='%U' AND [strPassword]='%P'"
  6.     strWhere = Replace(strWhere, "%U", .txtUserName)
  7.     strWhere = Replace(strWhere, "%P", DigestStrToHexStr(Me.txtPassword))
  8.     If DCount("*", "[tblUsers]", strWhere) = 0 Then
  9.         'Close the app if the login was incorrect
  10.         strMsg = "Username/password combination is invalid." & _
  11.                  vbCrLf & vbCrLf & "Exiting application."
  12.         Call MsgBox(strMsg, vbCritical)
  13.         Call Application.Quit
  14.     End If
  15.     Call DoCmd.Close
  16.     'Open switchboard and/or run any other code
  17. End Sub
Dec 9 '09 #2

topher23
Expert 100+
P: 234
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:
Expand|Select|Wrap|Line Numbers
  1.     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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdLogin_Click() 
  2. Dim rs As DAO.Recordset 
  3.     Set rs = CurrentDb.OpenRecordset("SELECT UserID, strUsername, strPassword FROM tblUsers", dbOpenSnapshot) 
  4.     'first, see if the username is valid 
  5.     rs.FindFirst "strUsername = '" & Me.txtUsername & "'" 
  6.     If Not rs.NoMatch Then
  7.         'next, check the password 
  8.         If rs!strPassword = DigestStrToHexStr(Me.txtPassword) Then 
  9.             DoCmd.Close 
  10.             'open switchboard and run any other code 
  11.             Exit Sub        
  12.         End If 
  13.     End If
  14.     'close the app if the login was incorrect 
  15.     MsgBox "Username/password combination is invalid." & _ 
  16.     vbCrLf & vbCrLf & "Exiting application.", vbCritical 
  17.     Application.Quit 
  18.  
  19.  
  20. ErrorTrap:
  21.     MsgBox "Error #" & Err & ", " & Err.Description & _
  22.     vbCrLf & vbCrLf & "Please report error code to your System Administrator."
  23.     'in a normal case, we'd just put in the following lines:
  24.     rs.Close
  25.     Set rs = Nothing
  26.     'However, since we don't want anyone sneaking into our system 
  27.     'by forcing an error, we'll quit the application, which will
  28.     'clear the memory buffer anyway
  29.     Application.Quit
  30. End Sub 
Dec 9 '09 #3

topher23
Expert 100+
P: 234
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.
Dec 9 '09 #4

NeoPa
Expert Mod 15k+
P: 31,306
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:
Expand|Select|Wrap|Line Numbers
  1.     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).
Dec 10 '09 #5

patjones
Expert 100+
P: 931
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!
Dec 11 '09 #6