Protect Against SQL Injection in ASP.Net – SecureLayer7

SQL Injection Attacks – How to Prevent ASP.Net Database Applications

A lot of resources are available for SQL Injection attack, however  I will be focusing on the SQL injection protection sample codes in ASP.net. Wherever I meet developers, they are totally aware of SQL injection. On the other hand, they are not aware about how to fix the SQL injection as per the standard methodology.

Before going further, I would like to recommend you to read about SQL Injection attack, Let me search for you on Google. This post will be focused on the how to protect from SQL Injection in ASP.NET.

Recently, I have been performing  source code audit on ASP.NET application and I found that a lot of developers have been missing to place a protection against a well known vulnerability, i.e. the SQL injection. As you can see below, I have mentioned how the developers are writing vulnerable code. The given code is the code commonly used by the developers.

In-secured programming example:

Example 1:

userID = string.Format(“userID={0}”, id); // ID is might be GET or POST parameter
string sqlStatement = “SELECT * FROM users”;
if (userID .Length > 0) sqlStatement += ” WHERE ” + userID;
cmd.CommandText = sqlStatement;

Example 2

var sqlString = “SELECT * FROM Product WHERE ProductCategoryID = ” + productCategoryId; // productCategoryId  is might be GET or POST parameter

As we can see in the examples above, developers accept the untrusted user inputs and append inputs variables to the SQL statement. Consequently, this action will result in a SQL injection vulnerable application.

Hence, the very first concern of the developers will be, how to mitigate the SQL injection vulnerability from the application by using the standard methodology ?

The answer is very simple. Simply follow the steps given below:

1. 1. White Listing User Input

The white listing is notably one of the trickiest part of the fixing of SQL injection. Programmer have to understand in detail the input parameter requirement and set proper regx to the input. For example: the Text property will be accepting only Alphanumeric values. Another example input will accept only INT value like:

if(!int.TryParse(productCategoryId,outid))

Also, programmer can replace the single quote (‘) from the input.

• Parameterized SQL query
  Following SQL Statement demonstrates the parameterized Query Examples to prevent SQL injection.

string sql = "SELECT * FROM Customers WHERE CustomerId = @CustomerId";
SqlCommand command = new SqlCommand(sql);
command.Parameters.Add(new SqlParameter("@CustomerId", System.Data.SqlDbType.Int));
command.Parameters["@CustomerId"].Value = 1;

In this case, the @CustomerID parameter is treated as a literal value and not as an executable code.

•  Using Stored Procedure

The case given below is applicable if developer is using stored procedure. The following way can be helpful to patch the SQL injection. Beware, it is mandatory to set the CommandType to StoredProcedure, because default type is CommandType.Text.

 varsqlString ="GetProducts";
 command.CommandType =CommandType.StoredProcedure;
 command.Parameters.Add("@ProductCategoryID", SqlDbType.VarChar).Value = productCategoryId;

Below given is the recommended fix:

Code by Larry
Public Class CommandTextValidator
  Public Shared Sub ValidateStatement(ByVal commandText As String, ByVal authorizedStatements As StatementTypes)
  'Construct Regular Expression To Find Text Blocks, Statement Breaks & SQL Statement Headers
  Dim regExText As String = "('(''|[^'])*')|(;)|(\b(ALTER|CREATE|DELETE|DROP|EXEC(UTE){0,1}|INSERT( +INTO){0,1}|MERGE|SELECT|UPDATE|UNION( +ALL){0,1})\b)"
 
  'Remove Authorized Options
  If (authorizedStatements And StatementTypes.Batch) = StatementTypes.Batch Then regExText = regExText.Replace("(;)", String.Empty)
  If (authorizedStatements And StatementTypes.Alter) = StatementTypes.Alter Then regExText = regExText.Replace("ALTER", String.Empty)
  If (authorizedStatements And StatementTypes.Create) = StatementTypes.Create Then regExText = regExText.Replace("CREATE", String.Empty)
  If (authorizedStatements And StatementTypes.Delete) = StatementTypes.Delete Then regExText = regExText.Replace("DELETE", String.Empty)
  If (authorizedStatements And StatementTypes.Delete) = StatementTypes.Delete Then regExText = regExText.Replace("DELETETREE", String.Empty)
  If (authorizedStatements And StatementTypes.Drop) = StatementTypes.Drop Then regExText = regExText.Replace("DROP", String.Empty)
  If (authorizedStatements And StatementTypes.Execute) = StatementTypes.Execute Then regExText = regExText.Replace("EXEC(UTE){0,1}", String.Empty)
  If (authorizedStatements And StatementTypes.Insert) = StatementTypes.Insert Then regExText = regExText.Replace("INSERT( +INTO){0,1}", String.Empty)
  If (authorizedStatements And StatementTypes.Merge) = StatementTypes.Merge Then regExText = regExText.Replace("MERGE", String.Empty)
  If (authorizedStatements And StatementTypes.Select) = StatementTypes.Select Then regExText = regExText.Replace("SELECT", String.Empty)
  If (authorizedStatements And StatementTypes.Union) = StatementTypes.Union Then regExText = regExText.Replace("UNION", String.Empty)
  If (authorizedStatements And StatementTypes.Update) = StatementTypes.Update Then regExText = regExText.Replace("UPDATE", String.Empty)
 
  'Remove extra separators
  Dim regExOptions As RegexOptions = regExOptions.IgnoreCase Or regExOptions.Multiline
  regExText = Regex.Replace(regExText, "\(\|", "(", regExOptions)
  regExText = Regex.Replace(regExText, "\|{2,}", "|", regExOptions)
  regExText = Regex.Replace(regExText, "\|\)", ")", regExOptions)
 
  'Check for errors
  Dim patternMatchList As MatchCollection = Regex.Matches(testText, regExText, regExOptions)
  For patternIndex As Integer = patternMatchList.Count - 1 To 0 Step -1
    Dim value As String = patternMatchList.Item(patternIndex).Value.Trim
    If String.IsNullOrWhiteSpace(value) Then
      'Continue - Not an error.
    ElseIf value.StartsWith("'") AndAlso value.EndsWith("'") Then
      'Continue - Text Block
    ElseIf value.Trim = ";" Then
      Throw New System.UnauthorizedAccessException("Batch statements not authorized:" & vbCrLf & commandText)
    Else
      Throw New System.UnauthorizedAccessException(value.Substring(0, 1).ToUpper & value.Substring(1).ToLower & " statements not authorized:" & vbCrLf & commandText)
    End If
  Next
 End Sub
End Class

In conclusion, I hope this will help you fix the SQL Injection vulnerability. In case if you are looking to fix the SQL injection vulnerability in your application, shoot an email to [email protected].