databasedev.co.uk - database solutions and downloads for microsoft access

Input validation with VBA

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.

Conclusion

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.