Categories
security sql-injection validation

How does the SQL injection from the “Bobby Tables” XKCD comic work?

1184

Just looking at:

XKCD Strip
(Source: https://xkcd.com/327/)

What does this SQL do:

Robert'); DROP TABLE STUDENTS; --

I know both ' and -- are for comments, but doesn’t the word DROP get commented as well since it is part of the same line?

9

1179

It drops the students table.

The original code in the school’s program probably looks something like

q = "INSERT INTO Students VALUES ('" + FNMName.Text + "', '" + LName.Text + "')";

This is the naive way to add text input into a query, and is very bad, as you will see.

After the values from the first name, middle name textbox FNMName.Text (which is Robert'); DROP TABLE STUDENTS; --) and the last name textbox LName.Text (let’s call it Derper) are concatenated with the rest of the query, the result is now actually two queries separated by the statement terminator (semicolon). The second query has been injected into the first. When the code executes this query against the database, it will look like this

INSERT INTO Students VALUES ('Robert'); DROP TABLE Students; --', 'Derper')

which, in plain English, roughly translates to the two queries:

Add a new record to the Students table with a Name value of ‘Robert’

and

Delete the Students table

Everything past the second query is marked as a comment: --', 'Derper')

The ' in the student’s name is not a comment, it’s the closing string delimiter. Since the student’s name is a string, it’s needed syntactically to complete the hypothetical query. Injection attacks only work when the SQL query they inject results in valid SQL.

Edited again as per dan04‘s astute comment

11

  • 3

    Mmm, the WHERE with parentheses around the arguments is rather unusual, but at least it avoids a syntax error… 🙂

    – PhiLho

    Dec 2, 2008 at 20:00

  • 64

    @PhiLho: If the original statement were an INSERT, then the parenthesis would make more sense. It would also explain why the database connection isn’t in read-only mode.

    – dan04

    Aug 10, 2010 at 4:02

  • 4

    As @dan04 explains, the parenthesis makes more sense with an INSERT. Thinking backwards, the SELECT would not run anyway as the Insert of the Little Bobby Tables in the table would have already dropped the table.

    Jan 21, 2013 at 21:41


  • 11

    Actually, in this example the first query (“add a new record…”) will fail because Students expects more than just the one column (the original/correct statement provided two columns). That said, the presence of the second column is helpful to show why commenting is required; and since one cannot change Bobby’s name, it’s probably best leaving as-is with little more than this observation as a footnote.

    – eggyal

    Apr 27, 2013 at 23:38

  • 8

    Bobby’s last name – or at least his mother’s, is Roberts, per Explain XKCD. I’m not sure that correcting that would improve the answer clarity, though.

    – WBT

    Aug 3, 2016 at 18:29

656

Let’s say the name was used in a variable, $Name. You then run this query:

INSERT INTO Students VALUES ( '$Name' )

The code is mistakenly placing anything the user supplied as the variable. You wanted the SQL to be:

INSERT INTO Students VALUES ( ‘Robert Tables` )

But a clever user can supply whatever they want:

INSERT INTO Students VALUES ( ‘Robert'); DROP TABLE Students; --‘ )

What you get is:

INSERT INTO Students VALUES ( 'Robert' );  DROP TABLE STUDENTS; --' )

The -- only comments the remainder of the line.

3

  • 94

    This is much better then the highest voted, because it explains the closing parenthesis.

    Aug 13, 2010 at 12:39

  • 1

    By the way, there is no way for the school director in the comics to be aware or the XSS since the student table is deleted, he can’t know who has done this.

    – xryl669

    Feb 28, 2020 at 15:00

  • 1

    @xryl669 Logs are very helpful in situations like this… Sometimes all queries are logged, and sometimes other logged info can help you deduce culprit.

    – inemanja

    Mar 5, 2020 at 20:25

172

+25

As everyone else has pointed out already, the '); closes the original statement and then a second statement follows. Most frameworks, including languages like PHP, have default security settings by now that don’t allow multiple statements in one SQL string. In PHP, for example, you can only run multiple statements in one SQL string by using the mysqli_multi_query function.

You can, however, manipulate an existing SQL statement via SQL injection without having to add a second statement. Let’s say you have a login system which checks a username and a password with this simple select:

$query="SELECT * FROM users WHERE username="" . $_REQUEST["user'] . "' and (password='".$_REQUEST['pass']."')";
$result=mysql_query($query);

If you provide peter as the username and secret as the password, the resulting SQL string would look like this:

SELECT * FROM users WHERE username="peter" and (password='secret')

Everything’s fine. Now imagine you provide this string as the password:

' OR '1'='1

Then the resulting SQL string would be this:

SELECT * FROM users WHERE username="peter" and (password='' OR '1'='1')

That would enable you to log in to any account without knowing the password. So you don’t need to be able to use two statements in order to use SQL injection, although you can do more destructive things if you are able to supply multiple statements.