Fun with SQL Injection

In case you're not familiar with SQL injection, you'll find a crash course below. Then, we'll have some fun coming up with examples to demonstrate the various ways that your data could be open to an SQL injection attack.

The SQL Injection Attack

SQL injection is essentially the act of providing input to a website or application which contains SQL commands, with the intent of altering or obtain data from a database in a way not intended by the application.

As an example, let's have a piece of code that uses a query string parameter to pull data from a database.

http://www.mysite.com/getWage.php?employee_code=A8CNDU37

SELECT name, wage
FROM employee
WHERE employee_code = 'A8CNDU37';

The employee_code is random enough that we're not to worried about people guessing it, and hence are comfortable pulling a value from the query string.

But, let's say we used this query string:

http://www.mysite.com/getWage.php?employee_code=A8CNDU37' OR 'dummy' = 'dummy

SELECT name, wage
FROM employee
WHERE employee_code = 'A8CNDU37' OR 'dummy' = 'dummy';

Spoofer’s Delight

A good place to start is with server variables. I’m talking about PHP's $_SERVER['x'], ColdFusions's CGI.X, Python's REQUEST.get('x') or any variable passed down from the web server for use in your script.

We tend to place a bit more trust in the validity of the data sent from our web server and, in turn, might feel more comfortable inserting this data directly into our database.

Don't get too cozy with server variables, as many of them depend on the information passed by the web browser. One such variable, the HTTP_USER_AGENT.

You might be tempted to run a query like this, using PHP as an example:

$sql = sprintf("INSERT INTO contact_form (user_id, email, browser)
                VALUES (%d, '%s', '%s')",
                $_SESSION['user_id'],
                mysql->real_escape_string($_POST['email'],
                $_SERVER['HTTP_USER_AGENT'])

But what happens if a funny guy decides to alter the user agent string sent by his browser (using any of a myriad tools available to do so)?

HTTP_USER_AGENT = Mozilla '); DELETE FROM contact_form WHERE '1' NOT IN ('

You can delete all your data yourself, or you can wait until a human or bot to spoof it’s user agent string and do it for you!

Man on the Inside

You may also think that data coming out of your database is safe, and there's little need to check it for SQL injection. But, in a typical web scenario, who is putting data into your database? Users!

If an even funnier guy decides to create a username on your website:

jerryseinfeld2' OR
username IS NOT NULL OR 'x' = '

Inside this user's account is a nice interface where it lists previous quotes and estimates provided for the account.

SELECT quote_id, material_list, total_estimate
FROM quotes
WHERE username = 'jerryseinfeld2' OR username IS NOT NULL OR 'x' = ''

It may be a pain in the neck for this guy to log in to your site next time, but he doesn’t care. He just found out not only what materials his competitor is requesting quotes on, but also that his competitor is getting a lower rate!

The Free Pass

The SQL Union statement struts around disguised as an innocent way to combine the results of two select statements. But take off that mask and you have what amounts to a free pass for any hacker to retrieve data from any table in your database!

Here's how a real miserable hacker might attempt to pull protected data from a table completely unrelated to the task at hand.

Let's build off of the example above, where the hacker supplies a username which contains SQL statements:

x193ajx832jx' UNION ALL SELECT social_security_number, '', '' FROM users WHERE username = 'competitor

Again, using the same example as above, only this time we're going to attack the users table. We want to grab the social security number of any username we choose.

Because we provide a totally random value for username in the first select statement, we're guaranteed not to get a record from it. This means that we'll get the first record from the second select statement, which is targeted at a specific username.

SELECT quote_id, material_list, total_estimate
FROM quotes
WHERE username = 'x193ajx832jx' 

UNION ALL 

SELECT social_security_number, '', '' FROM users WHERE username = 'competitor'

How to Prevent SQL Injection

Your Language Has You Covered

Thieves have been around as long as their has been something to steel, and SQL injection has been around as long as SQL. This means that just about any language has methods for protecting your queries against SQL injection.

• PHP has prepared statements and the real_escape_string function.
• ColdFusion has the <cfqueryparam> tag.
• Python has formatting built directly into the execute function.

A more complete list of languages along with their SQL injection protection mechanisms can be found here: http://bobby-tables.com/asp.html

Your Database Can Help

In many situations, it can be helpful to have three different database user accounts with different privilege levels. One account can only SELECT, the other account can only INSERT, UPDATE, the third can INSERT, UPDATE or DELETE.

Then, when using a server-side script to query your database, use the account with the most minimal set of privileges that will get the job done.

For instance, if you are displaying a list of products, query with the SELECT-only user.

If you are adding a product review, execute the statement with the INSERT,UPDATE-only user.

Some Languages Take it up a Notch

Check your language for features that offer more specific protection. ColdFusion, for instance, disables the ability to run multiple SQL queries in one statement. This feature can be disabled/enabled at will, but it is there to protect you if you need it.