RockYou, the FTC, and Little Bobby Tables

Today the FTC announced that it has settled a complaint against RockYou, on charges that the company’s inadequate security led to a breach of consumer data, and that the company collected personal information from children it knew to be under 13 without parental consent.

RockYou had several security problems.  First, they collected unnecessary information, including the passwords to users’ email accounts.  (RockYou asked users to enter a new password after users confirmed their accounts, but research suggests some consumers re-use their email passwords.)  Second, the company’s website had several vulnerabilities, including SQL injection and several cross-site scripting (XSS) problems.  Third, internal data management practices were weak: passwords were stored in the clear, and data was not separated internally.

I don’t have space to write about all of these problems, but to give a flavor of the kind of thing that can go wrong if adequate precautions aren’t taken, let’s talk about what an “SQL injection vulnerability” is.

SQL is a computer language that is used to send queries to databases.   Suppose a school has a database of students, and you want to get information about the student named Bobby Tables.   You can do this by sending the following SQL command to the school’s database:

SELECT * FROM students WHERE name='Bobby Tables'

The database will respond by sending the record it has for the student named Bobby Tables.

Now suppose you want to look up the record of a student whose name will be provided by a user.   An easy way to do this (but insecure, as we’ll see) would be to build an SQL command by pasting together a prefix (SELECT * FROM students WHERE name=’), followed by the name that your user provided, followed by a single-quote character.   If the user provides the name Joe Blogs, then the resulting command will be:

SELECT * FROM students WHERE name='Joe Blogs'

This will get you the information for Joe Blogs, which is just what you wanted.

This might look okay, but there is a problem lurking.   Suppose you connect this to a web page, so that arbitrary people on the Internet can supply student names that your web server will then look up in your database.   Now a malicious person can attack you.  Rather than supplying the name of a real student, they give you a deviously chosen “student name” that looks like this:

Robert'; DROP TABLE students; --

When you paste together your command, according to the recipe laid out above (prefix plus name plus single-quote), you’ll get this:

SELECT * FROM students WHERE name='Robert'; DROP TABLE students; --'

This will give you a nasty surprise.  The database, knowing that SQL uses semicolons to separate commands, will see this as a sequence of three commands separated by semicolons.  The first command (SELECT * FROM students WHERE name=’Robert’) gets information for a student named Robert–which is harmless enough.   But the second command (DROP TABLE students) will delete all of the student records in the database!  The third command is harmless but that isn’t much consolation now that all of the student records are gone.

(The example is based on a classic XKCD strip.   For those not in the know, XKCD seems to be every techie’s favorite webcomic.)

This is an SQL injection attack.  In general, the attacker can do things other than delete your data–he can leak your data, change password information in the database to give him access to user accounts, and so on.

The moral of this story is simple: don’t ever take input from an untrusted user and paste it directly into an SQL command that you’re going to send to an important database.   If you’re going to use untrusted input to build an SQL command, you have to take care to do so safely–and there are known methods for doing that.

This is a basic rule of secure website programming, which RockYou apparently failed to follow.  That, plus some other failures, led to a data breach that affected millions of consumers–and to an FTC case.

Note: This blog post was reposted from the former Tech @ FTC blog. Comments are now closed for this post.

Original Comments to “RockYou, the FTC, and Little Bobby Tables.”

cosmiclint
March 27, 2012 at 12:56 pm

What implementation of SQL uses GET instead of SELECT statements to retrieve records from the database?

Ed Felten
March 27, 2012 at 4:35 pm

Oops, my mistake. I should have written SELECT instead of GET. I have edited the post to fix this.

cosmiclint
March 27, 2012 at 6:23 pm

I figured it was probably just a typo, but you had me scrambling for my SQL In A Nutshell desktop reference :)

christi parks
January 30, 2013 at 2:49 am

Hello all,I am new and I would like to ask that what are the benefits of sql training, what all topics should be covered and it is kinda bothering me … and has anyone studies from this course wiziq.com/course/125-comprehensive-introduction-to-sql of SQL tutorial online?? or tell me any other guidance…
would really appreciate help… and Also i would like to thank for all the information you are providing on sql.

The author’s views are his or her own, and do not necessarily represent the views of the Commission or any Commissioner.