Home
Search
 
What's New
Index
Books
Links
Q & A
Newsletter
Banners
 
Feedback
Tip Jar
 
C# Helper...
 
XML RSS Feed
Follow VBHelper on Twitter
 
 
 
MSDN Visual Basic Community
 
 
 
 
 
TitleDeter an SQL injection attack
DescriptionThis example shows how to deter an SQL injection attack in Visual Basic 6.
KeywordsSQL injection, attack, hacker, data, safe
CategoriesSoftware Engineering, Database
 
Note: I don't claim that this method is proof against all attacks. I just don't know of a way to beat this technique. If you know of a way, please let me know.

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
 
Normally the user enters something like "Ann" for a user name and "Star" for the password, so the SQL statement is:

    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
 
Now suppose the hacker enters the same user name and password as before. Then the SQL statement becomes this:

    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.

 
 
Copyright © 1997-2010 Rocky Mountain Computer Consulting, Inc.   All rights reserved.
  Updated