Xkcd Style Sql Injection Hack in Python

You must have seen this XKCD comic:

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:

#!/usr/bin/python3
import sqlite3

We are using sqlite3, which comes inbuilt with Python.

db = "./students.db"

The name of our database is students.db.

conn = sqlite3.connect(db)
c = conn.cursor()

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

cmd = "CREATE TABLE students (Name TEXT, Age INT)"
c.execute(cmd)
conn.commit()

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.

data = [("Robert", 10), ("Sally", 15), ("Matthew", 7)]

c.executemany("INSERT INTO students VALUES (?,?)", data)

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

conn.commit()

conn.close()

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:

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

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

Xkcd style hack on our database

#!/usr/bin/python3
import sqlite3

db = "./students.db"
conn = sqlite3.connect(db)
c = conn.cursor()

We are just opening the database we created.

First, we will do a normal read:

print("Without Hack: \n")

c.execute("SELECT * from students WHERE Name='Robert'")
result = c.fetchall()
print(result)

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

Without Hack:
[('Robert', 10)]

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.

print("With Hack: \n")
Name = "Robert'; DROP TABLE students;--"
print("SELECT * from students WHERE Name='%s'" % Name)

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.

SELECT * from students WHERE Name='Robert'; DROP TABLE students;--'

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:

c.executescript("SELECT * from students WHERE Name='%s'" % Name)

result = c.fetchall()
print(result)

[]

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

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.

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:

# Never do this -- insecure!
symbol = 'RHAT'
c.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)

# Do this instead
t = ('RHAT',)
c.execute('SELECT * FROM stocks WHERE symbol=?', t)
print(c.fetchone())

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:

#!/usr/bin/python3
import sqlite3

db = "./students.db"
conn = sqlite3.connect(db)
c = conn.cursor()

print("Without Hack: \n")

c.execute("SELECT * from students WHERE Name='Robert'")
result = c.fetchall()
print(result)

Now to the relevant part:

Name = "Robert'; DROP TABLE students;--"
Name_to_use = (Name,)
print("Name to use:", Name_to_use)

Name to use: ("Robert'; DROP TABLE students;--",)

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

c.execute("SELECT * from students WHERE Name=(?)" , Name_to_use)

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

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:

data = [("Robert'; DROP TABLE students;--", 10)]
c.executemany("INSERT INTO students VALUES (?,?)", data)
conn.commit()

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:

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.