Following are some of the recommendations to protect the application against SQL injection attacks. These recommendations are very generic in nature and independent of database platform so they can be applied to any database such as SQL Server 2000/2005, Oracle, MySQL or DB2

Sanitization Input:Protecting SQL queries by implementing sanitization techniques for all input received from any ASP.NET request object. Check all the input sources such as Request.Cookies, Form Variables, Query String parameters, Request.ServerVariables etc. Sanitization routines will vary based on your DBMS.

Avoid disclosing database error information. In the event of database errors, make sure you do not disclose detailed error messages to the user. Never display debug or detailed information to the user containing SQL queries.

Check SQL meta-characters (single-quote(‘), semi-colon(;) or double-dash(–), or multi-line comments (/* */) etc) and command keywords (Select, Union, Delete, Drop, etc) from the input. Always check and filter these keywords in the input fields. Always perform these checks on server-side. Do not rely on client-side validation alone.

Escape/Quotesafe the input, such as by replacing all single quotes with two single quotes.

Constrain and sanitize input data. Check for known good data by validating for type, length, format, and range. Validation should be done on both server side and client side.

Remove unused extended stored procedures, like xp_cmdshell and xp_grantlogin, and other user-defined functions.

Limit the permissions granted to the database user account used by the Web application. Most of the cases, only “EXEC” permission is required for stored procs. Remove DBO privileges to the application account.

Avoid Dynamic Queries: Always use stored procedures to communicate with database. Avoid using dynamic queries or SQL statements embedded in the program code.

Use type-safe SQL parameters for data access. You can use these parameters with stored procedures or dynamically constructed SQL command strings. Parameter collections such as SqlParameterCollection provide type checking and length validation. If you use a parameters collection, input is treated as a literal value, and SQL Server does not treat it as executable code. An additional benefit of using a parameters collection is that you can enforce type and length checks. Values outside of the range trigger an exception.

Following is a sample VB.NET routine which will check for invalid characters in the data input.

 
Public Function TestForSQLInjection(ByVal psSQL As String) As String
 
	Dim rexSql As Regex = New Regex("/exec(\s|\+)+(s|x)p\w+/ix")
 
	Dim rexSql2 As Regex = New Regex("(\-\-)")
 
	If Not rexSql.Match(psSQL).Success And Not rexSql2.Match(psSQL).Success Then
 
		Return psSQL
 
	End If
 
Throw New Exception("SQL Injection attack possible with:" & psSQL)
 
End Function

Tags: Database, Security