What is a SQL injection attack?

Have any of your websites ever been a victim of a SQL injection attack? Well, one of my sites recently was so unfortunate and a lot of data was deleted from the database (Hooray for regular backups ). So, I looked around for a couple of solutions on how to prevent this, and wrote the function below which you can call in VB.NET to remove harmful code from any value passed to the database.

Example:
Note how the single brackets are used on both sides of the SafeSqlLiteral function:
‘” & SafeSqlLiteral(txtInput.Text, 2) & ”’

Code:
strQuery = “SELECT * FROM tablename WHERE name = ‘” & SafeSqlLiteral(txtInput.Text, 2) & ”’”
Namespaces imported

Code:
<%@ import Namespace="System.Text.RegularExpressions" %>
And the function to call

Code:
Function SafeSqlLiteral(strValue, intLevel) As String
    
    '*** Written by user CWA, CoolWebAwards.com Forums. 2 February 2010
    '*** http://forum.coolwebawards.com/threads/11-Preventing-SQL-injection-attacks-using-VB-NET
   
    ' intLevel represent how thorough the value will be checked for dangerous code
    ' intLevel (1) - Do just the basic. This level will already counter most of the SQL injection attacks
    ' intLevel (2) - &nbsp; (non breaking space) will be added to most words used in SQL queries to prevent unauthorized access to the database. Safe to be printed back into HTML code. Don't use for usernames or passwords
    
    If Not IsDbNull(strValue) Then
        If intLevel > 0 then
            strValue = replace(strValue, "'", "''") ' Most important one! This line alone can prevent most injection attacks
            strValue = replace(strValue, "--", "")
            strValue = replace(strValue, "[", "[[]")
            strValue = replace(strValue, "%", "[%]")
        End If

        If intLevel > 1 then
            Dim myArray AS Array 
            myArray = Split("xp_ ;update ;insert ;select ;drop ;alter ;create ;rename ;delete ;replace ", ";")
            Dim i, i2, intLenghtLeft as Integer
            For i = LBound(myArray) TO UBound(myArray)
                Dim rx As New Regex(myArray(i), RegexOptions.Compiled Or RegexOptions.IgnoreCase)
                Dim matches As MatchCollection = rx.Matches(strValue)
                i2 = 0
                For Each match As Match In matches
                    Dim groups As GroupCollection = match.Groups
                    intLenghtLeft = groups.Item(0).Index + len(myArray(i)) + i2
                    strValue = Left(strValue, intLenghtLeft - 1) & "&nbsp;" & right(strValue, len(strValue) - intLenghtLeft)
                    i2 += 5
                Next
            Next
        End If
        
        'strValue = replace(strValue, ";", ";&nbsp;")
        'strValue = replace(strValue, "_", "[_]")
        
        return strValue
    Else
        return strValue
    End If
    
End Function