XKCD SQL injection - please explain

Question

Just looking at:

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?

Answer 1

It drops the students table.

The original query in the school's program probably looks something like

var query = "SELECT * FROM Students WHERE (Name = '" + tbName.Text + "')";

This is the naive way to add user text to a query, and is evil. Since the student's name is "Robert'); DROP TABLE STUDENTS; --" the resulting query (after concatenation) is

SELECT * FROM Students WHERE (Name = 'Robert'); DROP TABLE STUDENTS; --')

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

Get everything from the Students table where the student's name is Robert.

and

Delete the Students table and ignore everything else I say from this point on ') and any other query-breaking junk.

The ' in the student's name is not a comment, it's the string delimeter. Since the student's name is a string, it's needed to complete the hypothetical query (i.e., Name = ' ). Injection attacks only work when the SQL query they inject results in good SQL (good being very relative in this case).

Answer 2

The '); ends the query, it doesn't start a comment. Then it drops the students table and comments the rest of the query that was supposed to be executed.

Answer 3

The ' character in SQL is used for string constants. In this case it is used for ending the string constant and not for comment.

Answer 4

The writer of the database probably did a

sql = "SELECT * FROM STUDENTS WHERE (STUDENT_NAME = '" + student_name + "') AND other stuff";
execute(sql);

If student_name is the one given, that does the selection with the name "Robert" and then drops the table. The "-- " part changes the rest of the given query into a comment.

Answer 5

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

INSERT INTO Students VALUES ( '$Name' )

What you get is:

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

The -- only comments the remainder of the line.

Answer 6

In this case, ' is not a comment character. It's used to delimit string literals. The comic artist is banking on the idea that the school in question has dynamic sql somewhere that looks something like this:

$sql = "INSERT INTO `Students` (FirstName, LastName) VALUES ('" + $fname + "', '" + $lname + "')";

So now the ' character ends the string literal before the programmer was expecting it. Combined with the ; character to end the statement, an attacker can now add whatever sql they want. The -- comment at the end is to make sure any remaining sql in the original statement does not prevent the query from compiling on the server.

Answer 7

Say you naively wrote a student creation method like this:

void createStudent(String name) {
    database.execute("INSERT INTO students (name) VALUES ('" + name + "')");
}

And someone enters the name Robert'); DROP TABLE STUDENTS; --

What gets run on the database is this query:

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

The semicolon ends the insert command and starts another; the -- comments out the rest of the line. The DROP TABLE command is executed...

This is why bind parameters are a good thing.

Answer 8

If you listen to the most recent blog.stackoverflow podcast, they actually discuss this.

Answer 9

No, ' isn't a comment in SQL, but a delimiter.

Mom supposed the database programmer made a request looking like:

INSERT INTO 'students' ('first_name', 'last_name') VALUES ('$firstName', '$lastName');

(for example) to add the new student, where the $xxx variable contents was taken directly out of an HTML form, without checking format nor escaping special characters.

So if $firstName contains Robert'); DROP TABLE students; -- the database program will execute directly on the DB the request:

INSERT INTO 'students' ('first_name', 'last_name') VALUES ('Robert'); DROP TABLE students; --', 'XKCD');

ie. it will terminate early the insert statement, execute whatever malicious code the cracker wants, then comment out whatever remainder of code there might be.

Mmm, I am too slow, I see already 8 answers before mine in the orange band... :-) A popular topic, it seems.

Answer 10

A single quote is the start and end of a string. A semicolon is the end of a statement. So if they were doing a select like this:

Select * From Students Where (Name = '')

The SQL would become:

Select * From Students Where (Name = 'Robert'); DROP TABLE STUDENTS; --'

On some systems, the select would get ran first followed by the drop statement! The message is: DONT EMBED VALUES INTO YOUR SQL. Instead use parameters!