Preventing SQL Injection Attacks in Classic ASP

The term "SQL Injection" means to inject malicious SQL statement(s) in an otherwise regular SQL to get access to secure data on a website. Most hackers who attempt this attack use SQL injection in querystring variables in a URL or in form contents being posted back to a web page. In this article, we will examine what SQL injection attacks are and what programming techniques can be used to prevent them. While we will assume classic ASP pages and the backend SQL Server database to demonstrate the method and means of prevention, the concept is equally applicable to other web programming technologies and database systems.

How a SQL Injection Attack is Carried Out?
Any time you generate a SQL statement dynamically in an ASP page using data retrieved from a querystring, form content, or even a cookie, you expose yourself to a SQL injection attack. To demonstrate this, have a look at the following insecure code which creates a dynamic SQL statement by retrieving data form a querystring:

Dim sql
	sql = "SELECT [Title], [Description] FROM [Articles] WHERE [ArticleID] = " & _
		Request.QueryString("articleId")

This code expects a querystring parameter with the name of articleId. While the developer may have provided a valid integer in the URLs on his website for this piece of code to work as expected, a hacker when he sees this URL will try to exploit this weakness by injecting his own SQL statement(s) to view, update or delete the content in the developer's database.

For example, all the hacker has to do to view the contents of [dbo].[sysobjects] table is to set the value of querystring to following:

0 UNION SELECT [name], [xtype] FROM [dbo].[sysobjects] --

The 0 at the start effectively removes any rows being returned for the original query while the second statement usign a UNION keyword appends secret data from [dbo].[sysobjects] table to the returning recordset. The last "--" keyword comments out any other SQL statement(s) appended to this statement by the developer. Thus the complete SQL statement that will get executed becomes the following:

SELECT [Title], [Description] FROM [Articles] WHERE [ID] = 0 UNION
SELECT [name], [xtype] FROM [dbo].[sysobjects] --

Now that you have got the idea what SQL injection attack is and how it is carried out, we will focus on what measures can be taken by an ASP developer to prevent such an attack from happening.

Programming Techniques Necessary to Prevent SQL Injection Attacks
I am going to describe two techniques which will effectively prevent most SQL injection attacks from happening on your website. I use them extensively at Stardeveloper.com. They are:

• Use CLng() when retrieving non-string values
• Use Replace(str, "'", "''") when retrieving string values

Using CLng() to Prevent SQL Injection in non-String Values
When appending non-string values like numbers in a dynamic SQL statement, use CLng() to convert that value to a number. CLng() will internally check if the parameter can actually be converted to a number. If the parameter to CLng() contains malicious strings like apostrophe, CLng() will throw an error.

Dim articleId
	articleId = CLng(Request.QueryString("articleId"))

In our example of SQL injection attack, had we used CLng() to convert the querystring value to a number first, the SQL injection attack would have failed. So remember this, always use CLng() for all non-string values that you want to append to a dynamic SQL statement.

Using Replace(str, "'", "''") to Prevent SQL Injection in String Values
When retrieving String values, convert all apostrophe (') characters to double apostrophe ('') characters. This will effectively remove the special status of apostrophe character in a SQL statement and will be treated by the SQL Server as a regular string character.

Dim userName
	userName = Replace(Request.Form("userName"), "'", "''")

Remember, always use Replace(str, "'", "''") for all String values that you want to append in a SQL statement.

Creating a Utility Routine in VBScript to do the Conversion for Us
Instead of hand coding CLng() and Replace(str, "'", "''") every time you have a non-string or String value, respectively; we can create a Function in VBScript and use it every time we retrieve values from querystrings, forms and cookies.

' Author: Faisal Khan (Stardeveloper.com)
Function GetSecureVal(param)
	If IsEmpty(param) Or param = "" Then
		GetSecureVal = param
		Exit Function
	End If
	
	If IsNumeric(param) Then
		GetSecureVal = CLng(param)
	Else
		GetSecureVal = Replace(CStr(param), "'", "''")
	End If
End Function

Now each time you have to retrieve values, just use GetSecureVal() like this:

' Retrieving values from a form
Dim firstName, lastName, email, age
	firstName = GetSecureVal(Request.Form("firstName"))
	lastName = GetSecureVal(Request.Form("lastName"))
	email = GetSecureVal(Request.Form("email"))
	age = GetSecureVal(Request.Form("age"))

Dim sql
	sql = "INSERT INTO [Users] ([FirstName], [LastName], [Email], [Age]) " & _
		"VALUES ('" & firstName & "', '" & lastName & "', '" & email & _
		"', " & age & ")"

Use GetSecureVal() for Values retrieved even from Cookies
Do not underestimate hackers, they can change the cookies and enter their malicious string to inject SQL statements. So, even when retrieving values from cookies and using them in SQL statements, always use GetSecureVal() to convert the apostrophe charaters (') to double apostrophe characters ('').

Dim userNameFromCookie
	userNameFromCookie = GetSecureVal(Request.Cookies("userName"))

Summary of Programming Techniques
Always use CLng() for numeric values and Replace(str, "'", "''") for String values. I have provided you a good generic function which will convert the parameter to appropriate type and remove any chances of SQL injection.

I hope you found the content in this article uesful, and will use the programming techniques mentioned to secure your SQL statements against malicious injection.