Link to home
Start Free TrialLog in
Avatar of jsvb1977

asked on

SQL Injection Prevention Best Practices

    <asp:Label ID="lblEmailAddressConfirm" runat="server" CssClass="mainlabel">Confirm Email</asp:Label>
    <asp:TextBox ID="txbEmailAddressConfirm" runat="server" CssClass="maintextbox"></asp:TextBox><br />
    <hr class="hrclear" />
    <p class="instructions">Confirm your email address in the field above.</p>    
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image

Step 1. Constrain input.
Step 2. Use parameters with stored procedures.
Step 3. Use parameters with dynamic SQL.

one subject line and 5 lines of code without explanation does not mean too much...
Also limit the permissions of the web server user(s) to the minimum possible level for example only grant EXEC on the stored procedures that website/page should be able to call. Don't add the user as a member of any of the predefined server or database roles. This will mean that if someone does manage to execute arbitrary code on your SQL Server at least they won't be able to do much.
Avatar of jsvb1977


Here is a test post. I am having trouble submitting and posting for some reason.

That is why the question is truncated / incomplete.
Here is an attempt at posting the meat of my question:

I have identified, through googling, the following methods for preventing sql injection attacks:

1. Replace single quote with two single quotes
2. Allow only 'safe' characters and a few common special characters
3. Deny Dangerous strings, Such as EXEC, UPDATE, DROP
4. Lock Down SQL User Permissions
5. Pass variables through to Stored Procedures via SQL Parameters

The last item in my list above is what my question is about.

Isn't the code below the same thing as passing variables through to a stored procedure as parameters?

Dim SSN as String = Request.QueryString("SSN")

Dim cmd As new SqlCommand("SELECT au_lname, au_fname FROM authors WHERE au_id = @au_id")
Dim param = new SqlParameter("au_id", SqlDbType.VarChar)
param.Value = SSN

Open in new window

1. If not, what is the difference?
2. Is this just a .net Code Behind version of a stored procedure parameter?
3. Should one method be used over another, or should both be used in tandem?
Grrrr... for some reason I am unable to submit the rest of my question. I contacted EE Support and they say there is nothing wrong on their side -- must be me.

Anyway -- the last part of my question is not a question, but rather examples of the routines I am using to check the strings for unsafe code.

I will try to post it again in a few minutes.
I will try attaching the routines instead of embedding them:

    Public Function fncReplaceQuote(ByVal strToReplace As String) As String

        strToReplace.Replace("'", "''")

        Return strToReplace
    End Function

    Public Function fncAllowedCharacters(ByVal strToCheck As String) As Boolean

        Dim strAllowedChar As String = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'- ,.?!@$_"

        For i = 1 To Len(strToCheck)
            Dim c As String = Mid(strToCheck, i, 1)
            If (InStr(strAllowedChar, c) = 0) Then
                Return False
            End If

        Return True
    End Function

    Public Function fncDeniedStrings(ByVal strToCheck As String) As Boolean

        Dim i As Integer
        strToCheck = strToCheck.ToUpper
        ' The following strings are not needed because they contain characters which we already to not allow via the fncAllowedCharacters

        Dim arrDeniedStrings() As String = {"INSERT ", "UPDATE ", "CREATE ", "DROP ", "DELETE ", "SELECT ", "--", "SCRIPT ", "EXEC ", "EXECUTE ", "TRUNCATE ", "SET ", "0x", "@@", "DECLARE", "VARCHAR", "SP_", "XP_"}

        For i = 0 To 20
            If strToCheck.IndexOf(arrDeniedStrings(i)) > -1 Then
                Return False
            End If

        Return True
    End Function

    Public Function fncCheckEmailAddressFormat(ByVal strToCheck As String) As Boolean

        Dim pattern As String = "^[a-zA-Z][\w\.-]*[a-zA-Z0-9]@[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]$"
        Dim emailAddressMatch As Match = Regex.Match(strToCheck, pattern)

        If emailAddressMatch.Success Then
            Return True
        End If

        Return False
    End Function

Open in new window

Avatar of Chris-Chambers
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
jsvb1977:  your paramater post was fine, you just need a small correction:

'ADDED @ symbol.
Dim param = new SqlParameter("@au_id", SqlDbType.VarChar)
Further post:

Agreed with Chris regarding the quicker portion of things.     Stored procedures can be quicker as they can be compiled as long as they don't make use of the wildcard.  Also the statements are NOT the same.  (see article at end).

from an execution point of view
select * from tbl

will execute the same in sql and stored_proc as the column list needs to be built at run time.

A good article here:

select id, test from tbl
will execute faster in stored_proc because it knows the return types once compiled.