Live Cyber Attack Lab 🎯 Watch our IR team detect & respond to a rogue insider trying to steal data! Choose a Session

X

Understanding SQL Injection, Identification and Prevention

Data Security

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.

hackertyper

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-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.

sqlitelogo

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

sql_injection_-_google_docs

The same information stored in a database table

sql_injection_-_google_docs

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

websec


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

NodeJS/Express

Python/SQL Alchemy

Django

Laravel

ASP.Net

What’s vulnerable to SQL injection attacks?

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

tag) have been the domain of big web applications. Sites that you interact with by opening up Chrome and typing some URL in the “everything bar” and being on your way.

Since those early days of the web a couple important developments have happened: one technological and one user focused.

The user development was simply that a billion people have now gotten online and while some still Google for “Facebook.com” to check their “E-Messages” – most people are more comfortable with browsers and general web concepts than any other generalized form of computing interface.

The technological advancement was the development of SQLite (which you may have used earlier in the article) as an absolutely crackerjack, free portable database – and this is where ice should chill your spine – suitable for use in things like mobile app development, Internet of Things Devices, Networking Equipment and any other geegaw that would previously have made use of a poorly formatted text file as a storage mechanism.

Together, these two advancements are responsible for:

  • Everything having a web configuration interface backed by a database.
  • Everything being susceptible to SQL injection attacks.

Which is why we now live in a world where:

Smart Home Hubs

Smart home hubs can be compromised. Their credentials overridden, people could spy on you from your own security system.

Network Equipment

Once cracked, network devices like routers and switches offer a tremendous foothold for launching more in depth attacks deeper into a network.

Fancy Electric Sport Cars

tesla

Have to date mostly only had their accounts compromised, but it’s not unthinkable that things like remotely disabling the car (a modern anti-theft feature), or other more dangerous features could be implemented. Other manufacturers have already been remotely hacked.

Android Apps

Once compromised your private pictures and contacts are vulnerable.

iOS Apps

The interconnectedness of mobile apps and the APIs they depend upon offer a new entry point for many SQL Injection attacks.

What can web servers do to help?

Apache

ModSecurity (which also works with NGINX and IIS) provides a default coreset of rules that will filter basic SQL Injection attacks.

Nginx

Try Naxsi – an open source web application firewall that acts as a 3rd party module to Ngnix blocking many of the tell tale characteristics of SQL Injection attacks.

For example, Naxi default SQL Injection rules would prevent url parameters of `—-` (the SQL Comment string often used to piggyback attacks).

Internet Information Server

IIS v7.0+ (so literally any version of IIS you should be using in production) have the ability to filter inbound http requests.

The official IIS blog has directions for adding first level HTTP filters:

Is NoSQL Safe from SQL Injection?

NoSQL is the catch all term for a variety of storage technologies that can complement or replace traditional Relational Data Base Management Systems (Servers that store data that have tables that are related to one another and that you query with SQL). Sometimes referred to as “Document Databases” or Key/Value stores they offer a simplified storage system (no need to define tables ahead of time) and at different storage points may be faster in data reads and writes.

It’s interesting and useful to think about how broad NoSQL and Key/Value storage solutions like MongoDB, Redis, Memsql, Cassandra, etc. deal with attacks that are similar, if not strictly “SQL Injection” attacks.

Earlier in this article, we discussed some of the “weird” things that can be done with SQL since it’s such a powerful and long lived platform. Most of the NoSQL solutions are much newer and their focus and lack of feature cruft naturally reduces the surface area of attack.

mongodb

A solid example of this in action is MongoDB, where the only method to query data is via Binary JSON (BSON) request objects. You literally can’t just pass in some slapped together string riddled with SQL injection statements.

But here’s the thing. You 100% can pass in executable Javascript as part of a BSON query object, so choose your poison wisely.

https://docs.mongodb.com/manual/faq/fundamentals/#javascript

Which isn’t a point for or against RDBMS anytime you have a web connected service where the programmer’s intent must be implemented through multiple layers of app, storage and encoding abstractions will always introduce cracks and ambiguities that can be exploited.

So, while in the general case, you aren’t going to see basic SQL Select statements exploited with concatenated subqueries, you ARE going to see other similar issues and which you will need to defend against.

Defense in Depth Checklist for SQL Injection Attack?

checklistDownload this checklist as a PDF

Like anything else computer security related, the only true protection is defense in depth from attacks, multiple layers of complementary defensive measures that together provide an overarching layer of protection:

The following checklist is intended to help you trace an application query execution path and to identify where you can add additional security layers:

Database

  • Sufficient and appropriate database user permissions set
  • Extraneous or unused database features disabled
  • Database logging enabled
  • Database backup / restore procedure
  • Database connection filtering procedures enabled (example: MySQL has options to prevent execution of multiple SQL statements in a single query)
  • Database drivers up to date

Application

  • Using filtering options
  • Using parameterization options
  • Using DB calls only when needed? (Could you use a static site generator?)
  • Code lint/checks for potential SQL injection points
  • Manual check for SQL Injection prone points
  • Application logging

Web Server / Web Firewall

  • Use WAF SQL Injection pre-filters
  • Rate limit to prevent mass SQL Injection attempts
  • Alert on SQL Injection pattern attempts

Did we miss something from the checklist or from the article?

Tell us on Twitter @varonis.com. We get everything right? Tell us that too. Feeling kind of ‘meh’ on this? Follow us and we promise to post something you’ll like in the very near future.

Learning More

SQL Injection is just one of the many frightening vulnerabilities that far too many web applications have. Learn more about XSS, CSRF and all the rest with our free course by HaveIBeenPwned creator Troy Hunt.

websec

Michael Buckbee

Michael Buckbee

Michael has worked as a syadmin and software developer for Silicon Valley startups to the US Navy and everything in between.

 

Does your cybersecurity start at the heart?

Get a highly customized data risk assessment run by engineers who are obsessed with data security.