Before you can stop an SQL injection attack, you need to know what one is. In an SQL injection attack, a hacker enters a value into a field that a program then uses as part of a SQL statement. By adding unexpected characters to the value, the hacker can fool the database into doing something you didn't intend.

For example, suppose you have a database with a Passwords table that contains UserName and Password fields. To log on, the user enters a user name and password in text boxes. The program uses code similar to the following to count the Passwords records with the matching user name and password. If a record matches, the user is allowed to log in.

Private Sub cmdUnsafe_Click()
Dim user_name As String
Dim password As String
Dim query As String
Dim rs As DAO.Recordset

    ' Get the user name and password.
    user_name = txtUserName.Text
    password = txtPassword.Text

    ' Compose the query.
    query = "SELECT COUNT (*) FROM Passwords " & _
        "WHERE UserName='" & user_name & "'" & _
        "  AND Password='" & password & "'"
    txtQuery.Text = query

    ' Execute the query.
    On Error Resume Next
    Set rs = m_DB.OpenRecordset(query, dbOpenSnapshot)
    If Err.Number <> 0 Then
        lblValid.Caption = "Invalid Query"
    ElseIf (CInt(rs.Fields(0)) > 0) Then
        lblValid.Caption = "Valid"
    Else
        lblValid.Caption = "Invalid"
    End If

    rs.Close
End Sub

    SELECT COUNT (*) FROM Passwords
    WHERE UserName='Ann'
    AND Password='Star'

This works just fine. If Ann/Star is in the Passwords table, then the query returns 1 and the user is logged in.

But suppose the hacker enters "X' OR 'a'='a" for a user name and "Y' OR 'b'='b" for a password. Then the SQL statement becomes this:

    SELECT COUNT (*) FROM Passwords
    WHERE UserName='X' OR 'a'='a'
    AND Password='Y' OR 'b'='b'

The WHERE clause evaluates to true if (UserName='X' OR 'a'='a') AND (Password='Y' OR 'b'='b'). But 'a'='a' and 'b'='b' are always true so the WHERE clause is always true. The COUNT (*) statement returns the number of records in the Passwords table. If that number is greater than 0 (it would be pointless if it were zero), the user is granted access.

The problem here is that the hacker entered single quotes that terminated the quotes added by the program. It then added another unexpected statement such as "OR 'a'='a'" to the query.

One way to stop this attack is to replace any single quotes in the user name and password with pairs of single quotes. That makes the database consider those quotes are part of the query. The following code is the same as the previous version except it replaces single quotes with two instances of single quotes in the input strings.

Private Sub cmdSafe_Click()
Dim user_name As String
Dim password As String
Dim query As String
Dim rs As DAO.Recordset

    ' Get the user name and password.
    user_name = Replace$(txtUserName.Text, "'", "''")
    password = Replace$(txtPassword.Text, "'", "''")

    ' Compose the query.
    query = "SELECT COUNT (*) FROM Passwords " & _
        "WHERE UserName='" & user_name & "'" & _
        "  AND Password='" & password & "'"
    txtQuery.Text = query

    ' Execute the query.
    On Error Resume Next
    Set rs = m_DB.OpenRecordset(query, dbOpenSnapshot)
    If Err.Number <> 0 Then
        lblValid.Caption = "Invalid Query"
    ElseIf (CInt(rs.Fields(0)) > 0) Then
        lblValid.Caption = "Valid"
    Else
        lblValid.Caption = "Invalid"
    End If

    rs.Close
End Sub

    SELECT COUNT (*) FROM Passwords
    WHERE UserName='X'' OR ''a''=''a'
    AND Password='Y'' OR ''b''=''b'

This query looks for records where:

    UserName:    X' OR 'a'='a
    Password:    Y' OR 'b'='b

It's pretty unlikely that those values are in the database.

(As I mentioned before, I think this spoils the attack. Let me know if you discover another attack that works.)

An alternative is to build the query as a parameterized command and set the user-entered values as parameters. Then the database includes any quotes that the user entered in the field values.

A second alternative is to simply remove any single quotes from the user name and password before using them. That is a bit simpler but prevents the user from using quotes in the user name and password.

The same techniques apply to every field that you will use in an SQL statement. For example, suppose a legitimate user is logged in and wants to find the record for a customer named Sean O'Grady. The single quote will mess up the query giving something like this:

    SELECT * FROM Customers WHERE Name='Sean O'Grady'

The database thinks the name is "Sean O" and gets confused by the "Grady'" that comes next.

If you double the quotes, you get this:

    SELECT * FROM Customers WHERE Name='Sean O''Grady'

The database knows that two single quotes in a row mean to include a quote in the text value so this query will work.