?
Solved

SQL Injection Prevention Best Practices

Posted on 2011-02-10
10
Medium Priority
?
452 Views
Last Modified: 2012-05-11
   <tr>
    <td>
    <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>    
    </td>
    </tr>
0
Comment
Question by:jsvb1977
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 34865895
http://msdn.microsoft.com/en-us/library/ff648339.aspx

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

0
 
LVL 57

Expert Comment

by:HainKurt
ID: 34865945
one subject line and 5 lines of code without explanation does not mean too much...
0
 
LVL 8

Expert Comment

by:gothamite
ID: 34865970
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.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:jsvb1977
ID: 34866169
Here is a test post. I am having trouble submitting and posting for some reason.

That is why the question is truncated / incomplete.
0
 

Author Comment

by:jsvb1977
ID: 34866174
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
cmd.Parameters.Add(param) 

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?
0
 

Author Comment

by:jsvb1977
ID: 34866192
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.
Jason
0
 

Author Comment

by:jsvb1977
ID: 34866261
I will try attaching the routines instead of embedding them:


[code]
    Public Function fncReplaceQuote(ByVal strToReplace As String) As String

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

        Return strToReplace
    End Function
[/code]

[code]
    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
        Next

        Return True
    End Function
[/code]

[code]
    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
        ' "EXEC(", "/SCRIPT", "<SCRIPT", "CAST(", "INSERT INTO", "DELETE FROM", "DROP TABLE",

        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
        Next

        Return True
    End Function
[/code]

[code]
    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
[/code]

Open in new window

0
 
LVL 7

Accepted Solution

by:
Chris-Chambers earned 2000 total points
ID: 34866677
Hi,

The answer to:

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

is no, but using an 'SqlParameter' as you have done is generally almost as safe in terms of sql injection as using a stored procedure with parameters.
The real reason everyone recommends stored procedures over dynamic sql is more than just sql injection safety. They are much easier to maintain, much quicker to execute, and once you've done a few, they are quicker to write.

Hope this helps,

Chris.
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 34874049
jsvb1977:  your paramater post was fine, you just need a small correction:

'ADDED @ symbol.
Dim param = new SqlParameter("@au_id", SqlDbType.VarChar)
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 34874100
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:
http://blogs.msdn.com/b/raulga/archive/2007/01/04/dynamic-sql-sql-injection.aspx


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

0

Featured Post

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question