Protecting Yourself from SQL Injection AttacksBy 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.
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:
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:
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:
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:
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
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:
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 (
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
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
Replace function, like so:
In the second example, the script was expecting a variable (
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:
If the user tries to pass in a string, the
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
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.
There are many great resources on this topic available on the Internet. Below is a list of a few I found to be helpful.
- SQL Injection FAQ
- Advanced SQL Injection White Paper
- Preventing SQL Injection
- Designing Secure Web-Based Applications for Microsoft Windows 2000, a helpful book