In this day and age it is hard to imagine that SQL injections are still successful.  An SQL injection attack occurs when a Web user enters SQL structure into an input field on a Web page, subsequently causing data loss,  modification, or theft of private information.  According to Robert McMillan’s article, a U.S. Army Web Site was modified by an SQL injection attack.  Of all the places, a Federal Government Web site was successfully attacked this way? This is a perfect example where mountains of security bureaucracy cannot save you—organizations still need talented, knowledgeable people designing their “world-facing” infrastructure.  

The easiest ways to prevent an SQL injection attack are to use parameters or stored procedures.  With this approach a Web site’s form fields cannot be used to build an SQL command.  A stored procedure can run under a security context that is only allowed limited permissions.  In this way, if a stored procedure was compromised—it couldn’t do anything harmful.  

You will see the term “build an SQL statement” around when researching injection attacks.  Typically novice developers will do something like this when creating a data input form:

insert into logged_in_customer (first,  last, userid)

select  acct_first, acct_last, acct_userid

from customer_accounts

where username=”’ +  username.Text + ”’

The “building” part is in bold and occurs when developers use external data to create or complete an SQL query.  In this example, the where clause is built with a user entered value from a textbox as the criteria.  We would hope that this textbox is being validated for injection on both the client and server side, but lets assume it’s not. What if the user entered the following into the text box:

’ or 1=1 —

This seems pretty innocuous right? Well this simple example would cause every user to be entered into the “logged_in_customer” table.  Let’s break it down:

In username=”’ ,using two single quotes in the where clause creates an escape sequence for the char/varchar data type .  The last single quote terminates the SQL  text string so that we can add (inject) the username.Text.   The trailing set of single quotes follow the same rules.  Can you see what happens here when a bad user enters the first single quote into the textbox?  Yep, the first ‘ in ’ or 1=1 —    causes the sql string to be terminated giving complete control over the where clause. So, just with the first single quote, we effectively have username=” , or the username equals an empty value.  In itself, this wouldn’t cause any harm—most likely no user account would be returned.  The or gives us an alternative input value to accept in order for the query to be successful. The 1=1 is always a true statement, therefore the where is always successful and every user would be entered in the logged_in_customer table. In username.Text + ”’ , what do we do with the last two single quotes that are in our original query that are suppose to be escape characters for our desired single quote?  That is where the — comes into play.  In MS SQL Server, two single dashes designate a comment, in which case the database compiler ignores everything following the two dashes until a newline is encountered. Using the comment at the end of our SQL injection prevents the SQL error that would have been returned by the final single quote.   

My example is overly simplistic, but lets you would be amazed at the code that prestigious organizations unknowingly have deployed on their Web sited. Let’s look at possible horror story that could happen from this simple example.  What if a Web site used the logged_in_customer table to prevent more than one user session from being logged in at one time?  This would effectively cause a denial of service to any customer that attempted to log in.