SQL Injection Attacks - Are You Safe?

The database is the heart of most Web applications: it stores the data needed for the Websites and applications to "survive". It stores user credentials and sensitive financial information. It stores preferences, invoices, payments, inventory data, etc. It is through the combination of a database and Web scripting language that we as developers can produce sites that keep clients happy, pay the bills, and -- most importantly -- run our businesses.

But what happens when you realize that your critical data may not be safe? What happens when you realize that a new security bug has just been found? Most likely you either patch it or upgrade your database server to a later, bug-free version. Security flaws and patches are found all the time in both databases and programming languages, but I bet 9 out of 10 of you have never heard of SQL injection attacks...

In this article I'll attempt to shed some light on this under-documented attack, explaining what an SQL injection attack is and how you can prevent one from occurring within your company. By the end of this article you'll be able to identify situations where an SQL injection attack may allow unauthorized persons to penetrate your system, and you'll learn ways to fix existing code to prevent an SQL injection attack.

What is an SQL Injection Attack?

As you may know, SQL stands for Structured Query Language. It comes in many different dialects, most of which are based on the SQL-92 ANSI standard. An SQL query comprises one or more SQL commands, such as SELECT, UPDATE or INSERT. For SELECT queries, each query typically has a clause by which it returns data, for example:

SELECT * FROM Users WHERE userName = 'justin';

The clause in the SQL query above is WHERE username = 'justin', meaning that we only want the rows from the Users table returned where the userName field is equal to the string value of Justin.

It's these types of queries that make the SQL language so popular and flexible... it's also what makes it open to SQL injection attacks. As the name suggests, an SQL injection attack "injects" or manipulates SQL code. By adding unexpected SQL to a query, it is possible to manipulate a database in many unanticipated ways.

One of the most popular ways to validate a user on a Website is to provide them with an HTML form through which they can enter their username and password. Let's assume that we have the following simple HTML form:

<form name="frmLogin" action="login.asp" method="post">
Username: <input type="text" name="userName">
Password: <input type="text" name="password">
<input type="submit">
</form>

When the form is submitted, the contents of the username and password fields are passed to the login.asp script, and are available to that script through the Request.Form collection. The easiest way to validate this user would be to build an SQL query, and then check that query against the database to see whether that user exists. We could create a login.asp script like this:

<%

dim userName, password, query
dim conn, rS

userName = Request.Form("userName")
password = Request.Form("password")

set conn = server.createObject("ADODB.Connection")
set rs = server.createObject("ADODB.Recordset")

query = "select count(*) from users where userName='" &  
userName & "' and userPass='" & password & "'"

conn.Open "Provider=SQLOLEDB; Data Source=(local);  
Initial Catalog=myDB; User Id=sa; Password="
rs.activeConnection = conn
rs.open query

if not rs.eof then
response.write "Logged In"
else
response.write "Bad Credentials"
end if

%>

In the example above, the user either sees "Logged In" if their credentials matched a record in the database, or "Bad Credentials" if they didn't. Before we continue, let's create the database that we have queried in the sample code.