Xkcd Style Sql Injection Hack in Python

You must have seen this XKCD comic:

Source: https://xkcd.com/327/

We are going to replicate this exact hack using Python and SQlite.

Learning Goal: To see how easy it is to destroy your database and all its data, if you don’t follow simple rules. Also, pay homage to Little Bobby Tables!

Before we start, download the DB browser for SqLite.

Create a simple database

Let’s create the database first:

We are using sqlite3, which comes inbuilt with Python.

The name of our database is students.db.

We connect to our students database. SqLite will create a database file for you, if it doesn’t exist.

We create a Table students with 2 values: Name and age.

The execute() command runns the SQL instruction, while the commit() writes it to the database. You don’t need to commit after every instruction, you can do it at the end, if you want.

We create some dummy data, and write it into our database. The executemany() function allows you to add multiple values in one go.

And we commit the database, and close it.

Open up the DB browser you downloaded, and open the database we just created.

On the main tab, you should see our table:

sqli_15

And if you go to the Browse Data tab, you should see our data too:

sqli_15

So far, so good. We have created a simple database. Now let’s hack it.

Xkcd style hack on our database

We are just opening the database we created.

First, we will do a normal read:

We select all students whose name is Robert, and fetch the results, and print them.

We got the correct result- remember, this was the data we entered. So far, our code is working as expected.

Now, let’s hack it.

We have created the name exactly as per the xkcd script. To see why it works, we also print the exact SQL statement that will be executed.

If you look at the SQL command above, you see we end the SQL instruction with a ;. That means that DROP TABLE students; is now a new instruction. The drop command will delete our table. The – –  is a comment in SQL, and is needed to comment out the last quote symbol in our instruction.

Now that we know how the instruction works, let’s run it:

This time we get an empty result. Why is that? Open up our database in the browser.

sqli_15

sqli_15

We see the database is empty! Our hack has deleted everything.

I hope you learnt to sanitise your database input, little Bobby Tables!

Fixing our hack

Create the database again, and check it exists by opening it in the browser.

sqli_15

So how do you prevent SQL injection hacks?

Well, you read the bloody documentation, don’t you?

Right in the documentation, it tells you what not to do:

What is the difference? In the first one, we are using the Python %s formatter to create the SQL instruction. That is because Python doesn’t know about SQL injection, and that allows our hack to work.

SqLite does. Which is why you use the ? character (instead of %s) to pass in values. This way, SqLite will escape any special characters we put in.

Let’s give it a roll, and write the fixed code. The first half of the code is the same as before:

Now to the relevant part:

And this time, we will use the syntax recommended by the SqLite documentation:

If this works, our database should not be deleted. Open up the browser to check:

sqli_15

Nope, still there. SqLite escaped the name, so it no longer runs as a SQL instruction.

To see why this works, let’s try to add this name to our database:

This is the same code as the one we used earlier, except we are using our hacky name now. Run this code, and open the DB browser:

sqli_19

As you can see, our injection code is now just treated as a normal string.

Which means Bobby Tables will be bullied at school for having such a loser name.

This is an extract from my book, Python For Hackers.

* indicates required
I will never spam you. Unsubscribe anytime.

2 thoughts on “Xkcd Style Sql Injection Hack in Python”

  1. Hi! There’s a couple of Python 2 “print” statements that won’t work in Python 3:
    print result
    should be
    print(result)

    print c.fetchone()
    should be
    print(c.fetchone())

    Nice tutorial!

Leave a Reply