Protecting Yourself from SQL Injection Attacks

By Ross Overstreet

What is SQL Injection?
If you are designing a Web site, or already have an existing Web site, you may be worried about potential "attacks" from rogue users. Too often, Web site developers focus solely on the security issues of the chosen operating system and Web server the site will run on. While IIS security holes can allow for malicious attackers, IIS security is not the only item that should be on your security checklist. The code that is commonly written for data-driven Web sites is often as serious a hole as any IIS hole. Such a programming code hole that can be exploited has been dubbed the SQL injection attack.

- continued -

SQL Injection is a technique which enables an attacker to execute unauthorized SQL commands by taking advantage of unsanitized input opportunities in Web applications building dynamic SQL queries. A common example of this is as follows:

A form is used to collect a visitor's username and password for access to a secured section, and submits to an ASP processing script. The processing script builds a SQL query from the input to determine if the username/password combo is valid.

In such a scenario, one might construct two pages, a login HTML page and an ASP page (ExecLogin.asp) that does the actual validation (i.e., lookup of the visitor's username/password in a database). The code for these pages might be:

Login.htm <form action="ExecLogin.asp" method="post"> Username: <input type="text" name="txtUsername"><br> Password: <input type="password" name="txtPassword"><br> <input type="submit"> </form> ExecLogin.asp <% Dim p_strUsername, p_strPassword, objRS, strSQL p_strUsername = Request.Form("txtUsername") p_strPassword = Request.Form("txtPassword") strSQL = "SELECT * FROM tblUsers " & _ "WHERE Username='" & p_strUsername & _ "' and Password='" & p_strPassword & "'" Set objRS = Server.CreateObject("ADODB.Recordset") objRS.Open strSQL, "DSN=..." If (objRS.EOF) Then Response.Write "Invalid login." Else Response.Write "You are logged in as " & objRS("Username") End If Set objRS = Nothing %>

At first glance, the code in ExecLogin.asp does not appear to contain any security holes. The user cannot log in without a valid username/password combination. However, this code is not safe, and is primed for a SQL injection attack. Specifically, the vulnerability is contained in the fact that user input is used to directly build the SQL statement, in effect enabling an attacker to control the statement executed.

An example of the vulnerability would be if the following string was entered into the username/password fields: ' or ''='. The SQL statement would then be executed as:

SELECT * FROM tblUsers WHERE Username='' or ''='' and Password = '' or ''=''

This query will return all records from tblUsers, and the script will proceed to log the user in as the first user identified by the first record in the table.

Another variation of the SQL injection attack exists when receiving querystring parameters to generate dynamic pages. Below is an example of an ASP page that accepts an ID through the querystring, and dynamically generates the page's content based on the ID:

<% Dim p_lngID, objRS, strSQL p_lngID = Request("ID") strSQL = "SELECT * FROM tblArticles WHERE ID=" & p_lngID Set objRS = Server.CreateObject("ADODB.Recordset") objRS.Open strSQL, "DSN=..." If (Not objRS.EOF) Then Response.Write objRS("ArticleContent") Set objRS = Nothing %>

Under normal circumstances, this script would display the content of the article whose ID was passed as a querystring parameter. For example, the page might be called as: http://www.example.com/Article.asp?ID=1055, thereby showing the dynamic content for article 1055.

Like our login example, this code opens itself to a SQL injection attack. A malicious user could substitute the valid Article ID for an unauthorized SQL command by passing into the ID something like: 0 or 1=1 (i.e., http://www.example.com/Article.asp?ID=0 or 1=1).

The SQL query would return all articles from the table for it would be executed as:

SELECT * FROM tblArticles WHERE ID=0 or 1=1

Of course, this example may not appear to be highly dangerous, but the attacker would then be able to manipulate the application even further by inserting malicious commands, such as DELETE statements. All of this could be done by simply manipulating the querystring! For example, anyone could call the page using a querystring like: http://www.example.com/Article.asp?ID=1055; DELETE FROM tblArticles.

The Implications of SQL Injection
The full implications of this vulnerability vary greatly based on environment and configuration. If the database connection uses the security context of dbo, it is possible to drop all tables in the database, create new tables, etc. If the database connection uses the security context of sa, it is possible to control the entire SQL Server, and under the right configuration even create user accounts to take control of the Windows server hosting the database.

Protecting Applications from SQL Injection
The first thing to do is to protect SQL queries by implementing sanitization techniques for all input received from any ASP request object (Request, Request.QueryString, Request.Form, Request.Cookies, and Request.ServerVariables). Your sanitization routines will vary based on your DBMS, but examples for MS SQL Server are given below.

In the login page example, the script was expecting two variables (txtUserName, txtPassword) of type string to be passed. When a single quote is inserted in a parameter, it allows the user to manipulate the command being executed. To combat the threat of SQL injection, escape the single quotes using the Replace function, like so:

p_strUsername = Replace(Request.Form("txtUsername"), "'", "''") p_strPassword = Replace(Request.Form("txtPassword"), "'", "''")

In the second example, the script was expecting a variable (ID) of type long integer to be passed. Unauthorized SQL commands can be executed by appending SQL to the ID parameter. To combat this type of SQL injection, simply restrict the input to a long integer using CLng, like so:

p_lngID = CLng(Request("ID"))

If the user tries to pass in a string, the CLng function will generate an error.

To further reduce the risk of SQL injection, be sure to remove any technical information from client-delivered error messages. Error messages often reveal technical details that can enable an attacker to reveal vulnerable entry points. This includes any custom messages your application generates as well as IIS-generated errors. You can implement this by disabling detailed error messages in IIS and by creating non-technical custom error pages. (For more information on creating custom error pages in IIS be sure to read: Creating Custom ASP Error Pages.)

Finally, to limit the scope of a SQL injection attack, limit the permissions granted to the database user account the Web application is using. The application generally doesn't need dbo or sa permissions. The less permission granted to your database the better! Consider using a separate account for each component with data access capabilities to isolate vulnerabilities. For instance, a front-end public interface to your Web site needs more restricted DB access than an internal content management system.

More Information
There are many great resources on this topic available on the Internet. Below is a list of a few I found to be helpful.

Happy Programming!

By Ross Overstreet