SQL Injection and Little Bobby Tables: How to Protect Your CMS

SQL injection attacks are a common kind of security flaw, but are subtle enough that it’s hard for regular folks to understand what’s at risk.

In this article we’ll walk you through the basics: what “SQL injection” means, what risk it presents to you, and what you can do about it.

SQL and Databases

SQL (short for “Structured Query Language”) is the lingua franca of relational databases. It’s the standardized way across the world to write queries that retrieve information from a large, complex database. Oracle, SQL Server, MySQL, Postgres, Sybase, Informix — programmers use the same language to retrieve data from all of these systems.

SQL queries are written in an English-like syntax. Imagine a magazine publisher which keeps a list of subscribers in a database table, sorted by first name, last name, address, subscription expiration date and probably a bunch of other data.

The magazine’s intranet might let users look up a subscriber’s address in the database by filling out a FIRST_NAME field and LAST_NAME field and then issuing this query:

When the user enters “John” for the FIRST_NAME field and “Doe” for the LAST_NAME field, the server substitutes those names in the SQL command and returns all of the addresses for subscribers named John Doe.

Lethal Injection

There’s a very serious problem with the above example. A clever user will notice that the contents of the FIRST_NAME and LAST_NAME fields get copied into the SQL code itself.

What happens if a user doesn’t enter a name in that field, but actually inserts some SQL code of their own? Look at the command that gets issued if the user enters this odd-looking string for the LAST_NAME field:

That instructs the server to retrieve all fields from the database where either the subscriber’s name is “John Doe” or where the equation “1=1” is true—and since that’s true no matter what, it’ll return a list of every subscriber in the database.

That’s why this sort of attack is known as SQL injection: the user’s input is injected into code that the computer runs. If the server hasn’t been careful to “sanitize” this input, then it can be tricked into running code that the author never put there in the first place, and bypasses any security protection.

Here’s a more destructive example. The SQL command “DROP TABLE” instructs a database server to delete an entire table of data. By adding a semicolon (“;”) into the injected code, an attacker could trick the server into running multiple SQL commands:

and just like that, the server returns the subscriber record for John Doe … small consolation, though, because it also subsequently deletes all of the subscriber records.

(This sort of attack is popularly known among geeks as “Little Bobby Tables,” after the XKCD cartoon Exploits of a Mom. The cartoon, a panel of which is shown at left, introduced readers to a security engineer and her son, Little Bobby Tables.

The lesson of the cartoon is, of course, to always sanitize your database inputs. There’s even a website dedicated entirely to helping people fix SQL injection attacks — named, of course, bobby-tables.com.)

SQL injection attacks allow a malicious user potentially unlimited access to the content in your application’s database, no matter what security the application has tried to enforce.

A vandal could destroy the database contents and leave you scrambling for backups. A thief could obtain any sensitive data from the database they like, from users’ home phone numbers to their credit card accounts.

What Can You Do?

The bad news is that SQL injection attacks are very common. The good news is that they’re almost always easy to fix. Securing an application against SQL injection is a matter of making sure that every SQL query has been “sanitized” so that user-submitted data can’t be executed as code, and sanitizing data is a solid, well-understood process.

Site owners and administrators should:

Stay up-to-date on security releases for public-facing software. Once a security alert has been issued for your CMS, you should assume that attackers are trying to exploit that vulnerability right now, and make the security upgrade a priority.

Wherever possible, don’t use a privileged account to access the application database. If the application gives you a way to run without access to a database administrator account, make sure you’ve done that. Applying the principle of least privilege means that you’ll at least minimize the damage that can be done if someone does exploit a vulnerability.