Understanding SQL Injection, Identification and Prevention

A Word of Caution

When you think of a website getting hacked, you might picture someone in a hoodie in a high tech bunker (or their mom’s basement), wailing on a keyboard, controlling thousands of remote machines in coordinated attacks, while output that looks like http://hackertyper.com/ scrolls past in a blur.

You probably aren’t thinking: “I added a couple characters onto the end of a URL in my browser, now I’m committing felony unlawful access to a computer system.”

Which is why we’re taking this point in time to point out that SQL Injection attacks are one of those situations where the outcome can be wildly disproportionate to the amount of effort that went into executing it.

Please approach practical aspects of this with the same seriousness as you would the new IT staff member telling you: “It’s just one command, how much damage could it possibly do?”

In particular, if you’re a sysadmin in any moderately sized organization, there are probably a half dozen internal applications that your company depends upon day in and day out which:

• Are presumed to be internal, so security isn’t a big priority.
• Were developed a decade or more ago when some security development practices weren’t as ingrained.
• Will quite likely crash if you run even an “innocuous” SQL injection attack against them. For instance, you can often grind a database and web server to a halt simply by requesting all of the records in the database instead of the 1 record that the application page would typically load.

Why SQL Injection Matters

Standardized query language (SQL) is, in one form or another, still the dominant method of inserting, filtering and retrieving information from a database. Loads of SQL queries will be coursing through your web applications on almost every page load – regardless of if it’s a tiny toy website with a tiny SQLite file, or a popular ecommerce site with millions of visits per hour requiring a massive cluster of database servers from Enterprise Database Vendor of choice.

And so, armed with literally nothing but a web browser, some basic SQL knowledge and an internet connection, an attacker can exploit flaws in your web application – extracting user data, discovering or resetting credentials and using it as a launch point for deeper assaults on your network.

We’re going to get to work our way up to SQL injection attacks and the reason they are scarier than a clown who lives in a drainpipe. But in order to understand injection/vulnerabilities, we need to take a step back and review that basic SQL knowledge first, which you may not have needed until this point in your role as a sysadmin.

SQL and Sysadmins

If you were interviewing a Sysadmin for a job setting up a new Windows Domain Server and some file servers and they said: “You know, I haven’t really worked with files before”, you’d throw their resume in the trash.

If an applicant said they were comfortable with setting up an Exchange server, but later confessed that “I haven’t really sent too many emails.”, you’d throw their resume in the trash and douse it with a strongest acid that HR allows in the workplace.

Unfortunately, it’s still fairly common for sysadmins to be tasked with the setup and administration of SQL Servers with little or no practical knowledge of how to actually craft queries and manipulate the data with raw SQL commands.

It’s completely necessary to have some SQL Basics firmly in mind when we’re discussing SQL Injection attacks: ultimately, it will help identify vulnerabilities your own applications.

What is SQL?

If you haven’t written anything with SQL before, it’s easy to fall into the trap of “oh, SQL is that thing for getting data out of a database” and that underestimation of it’s power and complexity directly leads to many of the security issues that occur when you put a web application in front of a SQL database.

It’s not usually regarded this way, but SQL is a full programming language unto itself. Every ridiculous stunt or weirdness that some ill advised programmer has thought of has been done with some version of SQL.

In fact, it’s probably better if you start mentally substituting the word “command” every time you hear “query”. In particular if you’re trying to convince an executive or someone about the potential seriousness of SQL Injection attacks, saying: “This flaw lets attackers execute arbitrary commands on our server.” Sounds much more frightening than something that otherwise sounds like: “They can run reports on our data.”

To illustrate this point, here are a couple of their “weird” things that SQL has been coerced into doing.

SQL Pie Charts: http://code.openark.org/blog/mysql/sql-pie-chart

SQL Queries that are a web browser: https://github.com/pramsey/pgsql-http

SQL Queries to browse the server filesystem: http://hubpages.com/technology/Using-xp_cmdshell-to-Query-the-Windows-File-System

I bring these examples up because of the discrepancy between what the word “query” means in everyday conversation versus what it means in the context of a SQL Injection attack. It’s natural to think of queries like questions: “Hey, can you throw me that ball?”.

However in SQL terms a “query” might be more like: “I’m throwing a 100mph fastball at your head.”

Follow Along

One of the really remarkable things about SQL is that the basics of the language haven’t changed much since it was first invented in the early 1970s. While some of the more esoteric commands differ between the major database vendors and open source options, the basics are the same across almost everything.

With that in mind, if you want to follow along with the steps below, here’s an SQLite database of all the data referenced in this article – as well as a link to DB Browser for SQLite – a cross platform, open source query tool to use with the example file.

Example SQLite Database File – Download YeOldeCheeseShoppe.zip
DB Browser for SQLite – http://sqlitebrowser.org/

While SQLite is just about the smallest database you’ll find in everyday use (anything smaller and people just start mapping files to memory), the SQL language itself is extremely similar across systems.

What’s a Database Table?

If (in the midst of your other IT duties) you were suddenly tasked with opening up a ‘Ye Olde Cheese Shoppe’ booth at the local farmers market and weren’t allowed to use computers, you’d probably fall back on making lists of what you had on hand, what prices the items were selling for, etc.

In a database, these lists are called tables – and they’re the fundamental building block of how data is structured in a database. At its core, a table is simply a list of information.

I find that it’s sometimes helpful to think of database tables like sheets in a spreadsheet. Each sheet is the list and the columns in that sheet are the attributes of the items in that list.

A spreadsheet representing the data for our store

The same information stored in a database table

We’re going to use the structure of the above products table for the examples below.

What are the basic SQL Commands?

Data doesn’t magically get into a database (unless you took that one elective offered at Hogwarts). You use SQL commands to Create, Read, Update and Delete (CRUD) the information inside of your tables. Most web applications and frameworks revolve around these same principles, building out forms to manipulate the underlying data which is stored in the database.

It’s important to know these commands as SQL Injection attacks are built upon the notion of altering what you would expect to happen and slipping past your defenses.

A note about SQL Syntax
A common convention when writing SQL statements is to make the SQL command words capitalized and to lowercase the words that would change statement to statement (table names, options, etc.)

How to create and update data with SQL?

Data is loaded into a table with the SQL Insert command. Generically it’s structured like:

INSERT INTO
"your-table-name"
(column_name_1, column_name_2)
VALUES
(value_for_column_name_1, value_for_column_name_2)

If we were to add a new product to our products table (which is structured like the spreadsheet above), we’d do the following:

INSERT INTO
Products
(name, sku, units, weight, price)
VALUES
(‘Alsace’, 303403, 3, 1.2, 300)

To add an additional product we’d run the insert command again, this time changing the values to reflect the new product:

INSERT INTO
Products
(name, sku, units, weight, price)
VALUES
(‘Bravino’, 409504, 6, 3.7, 250)

The SQL Update works very similarly:

UPDATE 
Products
SET 
column_name_2 = new_value
WHERE 
Column_name_1 = some_value

For example we might be wanting to rename a cheese:

UPDATE
Products
SET
Name = ‘Zutacular’
WHERE 
Name = ‘Bravino’

How to read data with SQL?

Data from tables is pulled out with the SELECT command.

SELECT
(column_name_1, column_name_2)
FROM
your-table-name

Making a SELECT against our products table would look like:

SELECT
(name, sku, units, weight, price)
FROM
products

This returns all of the data in the products table.

How to delete data with SQL?

DELETE FROM
your-table-name
WHERE 
column-name_1 = your_value

The following deletes everything in the products table

DELETE FROM 
Products
WHERE
Name = name

How To Select Data?

Select statements have lots of options and can get really complex, but the most important thing to know about is filtering (adding conditions to get only the rows you really want in a table) – this is where the most common type of SQL injection attack occurs.

It’s extremely unlikely that on every page of the Cheese Shoppes website they need the data for Every Single Cheese, so you filter the data with a WHERE clause.

The general use of the WHERE clause looks like this (appended to our previous SQL statement)

SELECT
(column_name_1, column_name_2)
FROM
your-table-name
WHERE
column_name_1 = ?

Any kind of comparison can be done in the WHERE clause, which is what makes it really powerful.

Adapting the query to our table we’d could write something like:

SELECT
(name, sku, units, weight, price)
FROM
products
WHERE
name = ‘Bravino` 

Better in Video Form?

If you’re finding this useful, you’re going to love the free video course on Web Security Fundamentals by https://haveibeenpwned.com/ creator Troy Hunt

Why is string concatenation the root of all evil?

Programmers refer to a sequence of non numeric characters as a “string”. Nobody knows exactly why this is the case, but it’s a fun topic to argue about on Stack Overflow:

http://stackoverflow.com/questions/880195/the-history-behind-the-definition-of-a-string

What is known is that string concatenation (the fancy programmers word for smashing smaller strings together into one larger string) is a quick and easy way to build SQL statements.

Maybe you’re a new programmer. You’ve been slamming Red Bulls and watching The Social Network on loop and you’re ready to write the ever loving bits out of The Next Great Ye Olde Cheese Shoppe website.

You have the web app setup with just two template pages:

1. A home (index) page listing all of the cheeses
2. A show_me_the_cheese page that gets passed the ID of the cheese when someone clicks the link from the homepage

URL: /show_me_the_cheese?id=1

That ID value of ‘1’ gets passed into the web app and a SQL query is built by putting together the portion of the command you know you need to run along with the id value that will change on every page url.

sql_string = "SELECT * FROM products WHERE id = " + id

After this, the sql_string variable will be:

SELECT * FROM products WHERE id = 1

The sql_string value then gets passed into the database library gets the cheese in question and returns it so the web page can be displayed. (This is the web application equivalent of you copying and pasting the full SQL query above into your SQL Client and running it against the database)

result = DB.run_query(sql_string) 

Now, you may be looking at this and saying to yourself: “This doesn’t look particularly evil.” Which is where you’re wrong and everything goes pear-shaped: it’s evil because it’s too easy and doing string concatenation of SQL statements is the fastest road to having your site and application owned.

String concatenation doesn’t care what you pass into it. It doesn’t know what an “id” is supposed to look like, so when a malicious trickster changes the “id” value in the URL from a 1 to:

URL: /show_me_the_cheese?id=(UPDATE products SET price = 0.1 WHERE ID = 1)

The same web application code will be executed as before:

sql_string = “SELECT * FROM products WHERE id = ” + id

Except this time the sql_string will have a value of:

SELECT * FROM products WHERE id =
 (UPDATE products
 SET price = 0.1
 WHERE ID = 1)

That’s a 100% valid SQL statement. The subquery that was swapped in place of the id of 1 will execute first and will let anyone order as much Bravino as they want for a dime.

How do Web Frameworks Prevent SQL Injection?

While web frameworks are typically thought of to be productivity enhancers, most incorporate best security practices for their programming language. Security (and in particular web security) is a complex topic and it’s exceedingly difficult to cover all the angles that you’d need to on your own.

In general, web frameworks prevent SQL injection attacks by providing easy methods of data querying so that developers aren’t seduced into writing hideously vulnerable SQL string concatenation statements.

They perform two important tasks:

First, they offer specific user input sanitization countermeasures to defeat common SQL Injection patterns: the framework will strip NULL characters, line breaks, single quotes, etc. that are often used to piggyback additional SQL commands into an intended query.

Second, they provide a syntax for declaring what a SQL statement is supposed to look like before actually trying to execute it. Depending on what framework you’re using, the name may vary, but the intent is the same: make sure that the form of the SQL statement that you want to execute is correct prior to running it.

With Rails, this is the difference between:

# SQL String Concatention BAD
Model.where(“id = “ + id)

And

# Parametized Query Good
Model.where(“id = ?”, id)

The second (and correct) form listed above will automatically sanitize the id value passed in from the URL and build the correct and safe SQL Query. This same pattern is present in virtually every web framework:

Rails

• SQL Injection Guide

NodeJS/Express

• Escaping Query values with MySQL

Python/SQL Alchemy

• Prevent SQL injections by avoiding string interpolations

Django

• Querysets perform filtering and parameterization

Laravel

• Filtering Bound Parameters

ASP.Net

• MSDN: How to prevent SQL Injection in ASP.NET

What’s vulnerable to SQL injection attacks?

Traditionally SQL Injection attacks (which have been around since the invention of the HTML