When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Stump the SQL Guru!
XML Info
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
Published: Wednesday, November 27, 2002

Data Sanitization - Reducing Security Holes in an ASP Web Site

By Craig Atkins

ASP is a great Web development technology that allows for rapid development of data-driven Web sites. However, problems can arise, however, when developers "go live" with code that contains potential security holes due to rushing out poor code, or not knowing the exploits that exist. In this article we will examine two common security holes that can be exploited by knowledgeable hackers: SQL Injection and Cross Site Scripting.

- continued -

It is surprising (and alarming) how many production Web applications/Web sites contain some major security holes based around these exploits. Furthermore, the amount of source code published on "educational" ASP sites (such as 4Guys, even) that suffers from these two common security pitfalls is shocking.

What is SQL Injection?
For data-driven Web sites, commonly the database queries issued are based, in part, on user input. For example, if your site has user accounts, requiring users to log in with the username and password, on the login page you likely have the user input their credentials, which you then use in a SQL statement to see if the user's supplied username and password match up. SQL Injection is a method of exploiting a Web application that takes a clients input data (such as a user's entered username and password) and uses it to form part of an SQL statement that is passed directly to a database.

An SQL Injection attack can be used for numerous nasty purposes depending on the hacker's skills and patience, such as logging into a website, stealing credit card details, deleting entire databases, or in some cases (where SQL server is used, and certain installation conditions are met) gaining access to the Web server's file system. Any SQL statement that uses unsanitized user input data is potentially vulnerable to an SQL Injection attack.

For example, imagine that we have a login page as previously described that accepts the user's username and password as input, and then checks the database to see if the credentials are correct. The VBScript code for this may look as follows (the below script shows the code that authenticates the user - it assumes that it is being called from a form where the TextBox for the user's username was give the name UserName, and the password TextBox was given the name Password):

'Create our ADO objects
dim db, rs
set db = server.createobject("ADODB.Connection")

'open the database
db.open myDSN

'Query the database to see if we have a record that matches 
'the username and password that have been submitted
set rs = db.execute("SELECT * FROM tblUSERS WHERE UserName = '" & _
                    request.form("UserName") + "' AND Password = '" & _
                    request.form("Password") + "'")

if rs.eof then
	'The user is not authenticated
	'The user is authenticated
end if

If the user enters in the login page a username/password pair of "Craig" and "foobar", then the SQL query:

SELECT * FROM tblUSERS WHERE UserName = 'Craig' AND Password = 'foobar'

will be executed. If there is such a user, then the Recordset rs will contain a row with information from the correct user from the tblUSERS database table (assuming there aren't multiple users with the same username/password). Great! That seems to work fine, and is nice and simple. But, imagine for a moment, what would happen if the user enters his username or password as:

' OR '' = '

When a user provides such a username the following SQL query will be issued:

SELECT * FROM tblUSERS WHERE UserName = '' OR '' = '' AND Password = 'password entered'

This SQL statement will return all matching records from tblUSERS. Why? Because the query has now been altered to disregard the users input, and compare nothing ('') to nothing, which will always equal true. (Other variations on this might be using 1=1 or 1 != 0 or other "always true" boolean expressions.) Our friendly hacker has just gained access to the "secure" area of our Web application... not good is it?

How do we Protect Against SQL Injection?
The best way to protect against SQL Injection is to sanitize the user's input data before placing it within a SQL query. Sanitizing data is the act of stripping out any characters that we don't need from the data we are supplied. Returning to our username/password example, the username field, say, should only contain alphanumeric characters (and maybe spaces, underscores, etc. depending on your configuration). Importantly, username values (and password values, for that matter) should not contain apostrophes. Sanitizing user input, then, ensures that these user inputs contain only the valid characters. By requiring that the username and password being passed to the database does not contain any invalid characters, we can protect ourselves against a SQL Injection attack.

The easiest way to sanitize your data is to simply replace all apostrophes with two consecutive apostrophes. In fact, in the 4Guys article Protecting Yourself from SQL Injection Attacks by Ross Overstreet, Ross shows how to use the VBScript Replace function to perform this task. Personally, I prefer to use regular expressions to strip any characters outside of the predefined "legal" characters. (As discussed in the last paragraph, such legal characters for a username may be alphanumeric characters and underscores.)

What are Regular Expressions?
Regular expressions are a powerful set of tools designed for string parsing and pattern matching. To learn more about regular expressions, start with Scott Mitchell's An Introduction To Regular Expressions.

Therefore, in order to protect our earlier ASP page example from a SQL Injection attack we will need to add the following code to clean our username so it only contains alphanumeric characters.

'Create a regular expression object
	Dim regEx
	Set regEx = New RegExp

'The global property tells the RegExp engine to find ALL matching 
'substrings, instead of just the first instance. We need this to be true.
	regEx.Global = true

'Our pattern tells us what to find in the string... In this case, we find 
'anything that isn't a numerical character, or a lowercase or 
'uppercase alphabetic character
	regEx.Pattern = "[^0-9a-zA-Z]"

'Use the replace function of RegExp to clean the username. The replace 
'function takes the string to search (using the Pattern above as the 
'search criteria), and the string to replace any found strings with. 
'In this case, we want to replace our matches with nothing (''), 
'as the matching characters will be the ones we don't want in our username.
	dim username
	username = regEx.Replace(request.form("UserName"), "") 

With the above code in place, if the user entered his username as user'';';#'.#'.'name, the regular expression would strip the extraneous characters and set the variable username to the value "username". We should use this method of sanitizing data on all of the code that comes from a users browser (such as the password entered by the user). In fact, data sanitization should be applied to data from form fields, hidden fields, disabled fields and cookies as well. Never assume that client side validation is working correctly, as hackers can circumvent this, and never assume that cookies cannot be edited, as they can, so their data should always be treated as unclean.

Now that we've examined the SQL Injection attack, let's turn our attention to the Cross Site Scripting attack, which, while different, is related to the SQL Injection attack in that its vulnerability stems from unsanitized data. We'll examine the Cross Site Scripting security hole in Part 2.

  • Read Part 2!

  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article