What Bart Simpson Can Teach Us about SQL Injection - and How You Can Prevent It

Bart Simpson is one of my favorite cartoon characters and arguably the most lovable prankster in television history. For the past 25 years, he has demonstrated ways of duping people, tricking the system, finding loopholes and challenging authority.

Most importantly, I would argue that by observing Bart, we can learn a thing or two about data security. You see, Bart's most storied pastime is to make prank calls to Moe's Tavern. Each time, the caper follows the same blueprint.

Step 1: Bart calls Moe's and asks Moe to perform a common and seemingly innocent task: Check if a certain person is in attendance at his establishment.

Step 2: Bart then provides a malformed first and last name of the person he is "seeking." For example, Bart may say: "Is Seymour there? Last name Butts, first name Seymour."

Step 3: Moe then calls out to the patrons, looking for the requested person. But instead of doing it the way Bart phrased it, Moe uses the common way a name is spoken: first name and then last. By doing this, the crotchety barkeep assembles the appellation exactly how Bart intends - and announces something embarrassing like: "Phone call for Al, Al Coholic. Is there an Al Coholic in here?" Or "Hey guys, I'm looking for a Jacques Strap." The list goes on

Essentially what Bart does is execute an injection attack against Moe. The tavern owner takes input that was carefully crafted by Bart and injects it directly into his own statement.

Yet while we enjoy watching Bart bamboozle and torment Moe, we do not stop to realize that what he's doing for a laugh isn't all that different from what attackers do to us on a daily basis.

The truth is a lot of people working in software development fall for Bart's prank over and over again, same as Moe did. In computer security, this type of attack is known as an injection attack, and the most common variation of injection attack is SQL injection. SQL injection is well-known, decade-and-a-half-old attack that still plagues applications of all calibers. In fact, just last month, Trustwave SpiderLabs researchers unearthed a major SQL injection vulnerability in Joomla, a popular content management system.

SQL injection remains such a popular form of attack against websites because there are very few conditions that need to be satisfied for the flaw to appear. It only requires the use of relational databases by a web application, the use of user input to construct an SQL query within the application, and security being an afterthought during the application development process. The majority of applications satisfy these conditions.

During an SQL injection, an attacker manipulates an input string - such as in a login page - to insert an SQL snippet directly into the query that is to be executed. This can be achieved when an application developer feeds "unsanitized" user input directly into constructing an SQL query.

Here's a benign example:

query = "SELECT bank_account_info FROM client_data WHERE user_name = '" + user_name_from_input + "'"; executeQuery(query);

If an attacker supplies "' or '1' = '1" through user_name_from_input, then the following query would be executed:

SELECT bank_account_info FROM client_data WHERE user_name = '' or '1' = '1'

In this scenario, instead of gaining access to only his own account, the attacker would be able to get their hands on a full list of bank accounts from all clients. A successful attack could also result in the intruder using commands to achieve access to sensitive information and core database functionality, and manipulating (editing/adding/deleting) data. In certain situations, it may even allow an intruder to obtain complete control over an operating system.

So how can you protect against and prevent SQL injection attacks. Here are the three primary options:

1. Parameterized queries

Parameterized queries are the preferred and simplest way to ensure that code does not contain SQL injection flaws. This technique clearly separates an SQL query from data. Even if an attacker supplies input that is manipulated to look like an SQL query, after being passed as a parameter it will not be able to subvert the query to perform in an unintended way.

2. Stored procedures

This technique is very similar to parameterized queries, but instead of implementing parameterization in a code it is done on the database side. Stored procedures are created to contain SQL queries and accept user input as parameters of those stored procedures. It should be noted that stored procedures protect from SQL injection only when they are implemented without unsafe dynamic SQL generation.

The downside of using stored procedures to prevent SQL injection is that an application may require a privileged database user to execute them. This may dramatically increase the potential impact if an attacker takes control of the account.

Another downside of the stored procedures strategy is that some of the application development logic moves from code into the database. Often, this change can slow the development process. In a very dynamic environment, the slowdown of development can result in overall abandonment of secure practices.

3. Escaping user-supplied input

Escaping user-supplied input is the least reliable way to prevent SQL injection flaws. This technique relies on replacing special characters (such as single quotes) in data supplied by the user with an escaped form of the character, before using it within the SQL query. What makes this technique less reliable than others is that special characters and the ways to properly escape them are highly database specific and can be vastly different in different situations. It is fairly easy to forget to escape some unusual input, omit some of the special characters or escape them incorrectly - and therefore expose your application to an SQL injection attack.

I would advise software developers to think about Bart and Moe the next time they dynamically create an SQL query. Don't let Moe suffer in vain, and please use safe coding practices.

Ay caramba!

Vladimir Zakharevich is a security researcher on the SpiderLabs team at Trustwave.

banner-app-security-halfheight 

Trustwave reserves the right to review all comments in the discussion below. Please note that for security and other reasons, we may not approve comments containing links.