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:
-
- 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 Steinle 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].
