How to Block SQL Injection Hackers with ASP Validation

Protect your database-driven ASP website from hacker attacks with powerful input validation. This will slow down hackers who use methods like SQL injection attacks and XSS (cross-site scripting) via the URL querystring and form inputs. These methods are simple enough that you can do it yourself with only basic coding knowledge.
Step 1
The first goal of a hacker is to repeatedly try to break a website, causing it to display a variety of valuable errors that give away private database details. In this way, he can gain insight into the structure of the database and ultimately create a map or footprint of all its tables and columns. The second goal of the hacker is to actually manipulate the database by executing scripts in malicious ways. With control over the database, the hacker may possibly steal credit card numbers, erase data or infect it with viruses, among other nasty things. In essence, the URL querystring and textbox are the two backdoors into a database. Getting errors and manipulating the backdoors are the two methods used by hackers to ultimately destroy a database. For more details, see my companion article in the resources section or at http://www.ehow.com/how_4434719_protect-website-hacker-attacks.html .
Step 2
Block input containing malicious code.The number one way to block a hacker from manipulating the URL querstrying and textboxes is to block their input. But, how do you determine who they are, what they will input and whether or not it is safe? Unfortunately, you cannot know. So, you must assume that all user input could be potentially dangerous. A common saying in the programming world is that ALL INPUT IS EVIL. Thus, it must be treated with caution. Everything from everybody should be checked every time to ensure dangerous code does not slip in. This is accomplished by checking all input that is submitted via a querystring or form and then rejecting or removing unsafe characters before it ever reaches the database. If this sounds like a lot of trouble, you are right. But, it is the price we pay to protect our websites and databases from the wrath of hackers. It is your responsibility as the webmaster to ensure that only clean, safe input is allowed to enter your database.
Step 3
Input validation.To check if the input entered into the URL querystring or textbox is safe, we can use input validation rules. In other words, using ASP code on a web page can validate the input collected from the querystring or form to make sure it contains only safe characters. Once the input is deemed safe, it can be stored in a new variable, inserted into the SQL string and sent to the database.
Step 4
The wash and rinse cycle.Input validation should be a two-part process, like a wash and rinse cycle. We want to thoroughly clean all input by first checking for safe characters and second by checking for bad strings. See the resources at the end of this article for a more in depth discussion on this method.
Step 5
Only allow safe characters.Part one of the validation process is to reject all input unless it contains safe characters. This is the strictest and most effective form of input validation. It only allows input that is known to be good. Essentially, letters and numbers can be trusted. Special characters are the real culprits which give hackers their power and should be avoided. This extreme measure may not be feasible for all types of input, but try to restrict as many special characters as possible. See the resources at the end of this article for a more in depth discussion on this method.
Step6
Following is an ASP example that could be used for a login or search input field. It allows only a-z, A-Z, 0-9 and an apostrophe, hyphen and space. You can modify the function to include characters as you see fit. %'validation allows only good charactersfunction valGoodChars(input)good_chars = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'- "valGoodChars = truefor i = 1 to len(input)c = mid(input, i, 1)if (InStr(good_chars, c) = 0) thenvalGoodChars = falseexit functionend ifnextend function'collect the form inputsearchInput = Request.QueryString("searchKeyword")‘if an unsafe input is entered the user is asked to try againif (not valGoodChars(searchInput)) thenresponse.redirect("sorryTryAgain.asp")end if%>Remember to use a solution that best fits your website or consult a professional.
Step 7
Part two of the validation process is to reject all input if it contains bad strings. After you have collected good input with the method above, you should check it again for input that is known to be bad. Dangerous things could happen if the good character function allowed an apostrophe and hyphen, or other letter combinations like SCRIPT, SELECT, UPDATE, DELETE, etc. That is why the bad string function should be used in conjunction with the good character function. See the resources at the end of this article for a more in depth discussion on this method.
Step 8
Following is an ASP example that rejects bad characters and should be used after checking for good characters. Bad characters could include a pair of hyphens and the word script, among other things. You can modify the function to include the bad strings as you see fit. |%'validation disallows bad stringsfunction valBadStrings( input )bad_strings = Array( "--", "script" )for each i in bad_stringsif ( InStr( input, i ) <> 0 ) thenvalBadStrings = falseexit functionend ifnextvalBadStrings = trueend function‘if an unsafe input is entered the user is asked to try againif (not valBadStrings(searchInput)) thenresponse.redirect("sorryTryAgain.asp")end if%>Remember to use a solution that best fits your website or consult a professional.
Step 9
Safely query the database.Now that the input has been laundered through the good and bad functions, it is ready to be inserted into the SQL query and executed by the database. Following is sample ASP code that inserts the searchInput. queries the database for a match, then displays the results.<%'check the database for a matchSet myRecordSet = Connect.Execute ("SELECT * FROM dbo.myTable WHERE myColumn LIKE '%" & searchInput & "%'")'display the resultsif myRecordSet.EOF then'display message no results foundelse'display resultsend if end if%>By validating the input before sending it to the database you have greatly reduced the risk of your database being compromised by hackers.
Step 10
Filter characters.Another method that can be used in conjunction with the above two functions, but is considered to be very weak when used alone, is to sanitize the input by filtering or escaping. A well-known threat is the single quote or apostrophe because it breaks the SQL statement. Following is an ASP example that renders the single quote harmless, by replacing it with two single quotes.'doubleup single quotesnewSafeString = replace(searchInput, "'", "''")Other variations for the replace function include stripping out the script tag and replacing it with a space. Or, filter out characters such as the dollar sign $ quotation mark “ semi-colon ; and apostrophe ‘ the left and right angle brackets <> the left and right parentheses ( ) the pound sign # and the ampersand &. Or convert these characters to their HTML entities.Remember to use a solution that best fits your website or consult a professional.
Step 11
If you would like to pursue more advanced security techniques, please see the resources at the end of this article. Topics discussed include, password policies, buffer overrun, creative table and column names, table name aliases, set and check data types, .bak files, stored procedures with parameters, and log files.