In this article we will look at several methods of validating user input. Why is user input validation necessary? It is necessary for both security reasons and also for the smooth running of your Microsoft Access application. Since Access databases are now commonly used as database backend for websites, it is even more critical to look at security since we don’t want unauthorized users to get access to information contained in a database or for users to input data that is not correct.
Assume we have an application that only allows access for particular users. How are we going to check that a user is actually allowed to use the application? In VBA we simply create a login form and ask for a users name and password and match it against a password that is stored in a database, if it matches then the user can access the application otherwise the user will not be allowed. Let’s take a practical look at this very simple method of authentication. Create a table called users with the following structure:
As you can see the table has two fields the first one is called uName, it takes the user name and the second is called uPass and it takes the password. Note the field size limitation of the password field. It is set to six. This basically means that any password that a user enters which is greater than six letters will be invalid. When we write the VBA code we will automatically check for this limit.
Now insert a sample user with the uname as jane and the password (upass) as sunday.
I’ve created a (login) form with the following design:
The idea is that when a user tries to access the application, they will be confronted with the above screen that will check if they are actually allowed to use your application. So the user enters their login info and clicks on the submit button which runs code that looks something like this:
Dim err As Boolean Dim con1 As ADODB.Connection Dim recSet1 As ADODB.Recordset Dim strSQL As String 'check that the user enter something txtuName.SetFocus If txtuName.Text = "" Then MsgBox "Please enter all required information." err = True End If 'check that the user entered valid data i.e non-numerical txtUpass.SetFocus If IsNumeric(txtUpass) Then MsgBox "Invalid format" txtUpass.SetFocus err = True End If txtUpass.SetFocus If Len(txtUpass) > 6 Then MsgBox "Please check your password and try again" err = True End If 'run sql query to verify that the user exist txtuName.SetFocus If Not err Then sql = "SELECT uName, uPass FROM users WHERE uName=" & txtuName.Text Set con = CurrentProject.Connection Set recset = New ADODB.Recordset recset.Open sql, con If recset.RecordCount > 0 Then 'user does exist in database MsgBox "You can access the application" Else 'user does not exist MsgBox "Your login details do not match" End If 'recordcount recset.Close con.Close Set con = Nothing Set recset = Nothing End If
As you can see in the code, the first check that is made is to see if the user has entered data into the required fields and that the data that they entered is of the right type for instance the username should not be alphanumeric or numeric it should be text. So a check is made to ensure that the username is indeed text:
'check that the user entered valid data i.e non-numerical txtUpass.SetFocus If IsNumeric(txtUpass) Then MsgBox "Invalid format" txtUpass.SetFocus err = True End If
The length of the password is also checked to make sure that it does not exceed 6 characters:
txtUpass.SetFocus If Len(txtUpass) > 6 Then MsgBox "Please check your password and try again" err = True End If
A common mistake that programmers make in this kind of check is to actually state the expected length of a password in the error message box. This is a security risk because it tells the potential attacker or hacker the actual length of the password, which will make it considerably easier for them to break into your database. A vague error message such as the one in the code above is sufficient.
Microsoft Access offers other automated methods for securing database and even VBA code, that I think is excellent. The methods that I covered here are generic and can easily be adjusted to use on the web.