#SQL Injection

##Definition

SQL injection (SQLi) is a web security vulnerability that allows an attacker to interfere with the queries that an application makes to its database. This can allow an attacker to view data that they are not normally able to retrieve.

##SQLi Detection

You can detect SQL injection manually using a systematic set of tests against every entry point in the application. Alternatively, you can find the majority of SQL injection vulnerabilities quickly and reliably using 'Burp Scanner'.

##SQLi Keywords

Most SQL injection vulnerabilities occur within the

WHERE

SELECT

UPDATE

INSERT

WHERE

ORDER BY

##Classic SQLi

The most common SQL Injection technique, is to use the comment marker (--) to bypass certain queries.

Example - For a login form where we input 'username' and 'password'.

    SELECT * FROM users WHERE username = 'username' AND password = 'password'
    
    Here, we can make use of the comment marker(--) to bypass into it without needing the password.

    SELECT * FROM users WHERE username = 'administrator'--' AND password = ''

    Here, after the `--` marker, ' AND password = '' get commented out and we login into the system.

##Union Based SQLi

When an application is vulnerable to SQL injection, and the results of the query are returned within the application's responses, you can use the

UNION

Example -

    SELECT a, b FROM table1 UNION SELECT c, d FROM table2

    This SQL query returns a single result set with two columns, containing values from columns a and b in table1 and columns c and d in table2.

For a UNION query to work, two key requirements must be met:

• >

  The individual queries must return the

  same number of columns

  .

• >

  The

  data types in each column must be compatible

  between the individual queries.

###Determining Number of Columns

When you perform a SQL injection UNION attack, there are two effective methods to determine how many columns are being returned from the original query.

• >One method involves injecting a series of ORDER BY clauses and incrementing the specified column index until an error occurs.

For example - If the injection point is a quoted string within the WHERE clause of the original query, you would submit:

    ' ORDER BY 1--
    ' ORDER BY 2--
    ' ORDER BY 3--  
    etc.

This uses the column number you provide as the argument to ORDER BY the output table. So, if you input a table number that doesn't exist it'll throw you an error and hence we can be sure that the number of columns were (current - 1).

Example Error -

The ORDER BY position number 3 is out of range of the number of items in the select list.

This means that number of columns = 2.

• >

  The second method involves submitting a series of UNION SELECT payloads specifying a different number of null values:

   ' UNION SELECT NULL--
   ' UNION SELECT NULL,NULL--
   ' UNION SELECT NULL,NULL,NULL--
   etc.

If the number of nulls does not match the number of columns, the database returns an error.

Example Error -

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

###Database-specific syntax

On Oracle, every SELECT query must use the FROM keyword and specify a valid table. There is a built-in table on Oracle called dual which can be used for this purpose. So the injected queries on Oracle would need to look like:

' UNION SELECT NULL FROM DUAL--

###Finding column Data Type

A SQL injection UNION attack enables you to retrieve the results from an injected query. The interesting data that you want to retrieve is normally in string form. This means you need to find one or more columns in the original query results whose data type is, or is compatible with, string data.

After you determine the number of required columns, you can probe each column to test whether it can hold string data. You can submit a series of

UNION SELECT

Example -

            ' UNION SELECT 'a',NULL,NULL,NULL--
            ' UNION SELECT NULL,'a',NULL,NULL--
            ' UNION SELECT NULL,NULL,'a',NULL--
            ' UNION SELECT NULL,NULL,NULL,'a'--

If the column data type is not compatible with string data type, it'll return an error like -

Conversion failed when converting the varchar value 'a' to data type int.

If no error is returned, then we can be sure that the target column is suitable for retrieving string data.

###Using SQL UNION Injection to retrieve Interesting Data

When we've determined -

• >The Number Of Columns
• >Data Type Of Columns
• >Database Name
• >Column Name

Example Payload -

' UNION SELECT username, password FROM users--

We can move forward and start extracting meaningful data from the Database. But, for this to happen we need to know the details about the structure of the database and it's name. All modern databases provide ways to examine the database structure, and determine what tables and columns they contain.

###Retrieving multiple values within a single column

You can retrieve multiple values together within this single column by concatenating the values together. You can include a separator to let you distinguish the combined values.

Example -

' UNION SELECT username || '~' || password FROM users--

But, we need to take care of the number of columns bring returned and add consecutive NULL placeholders accordingly.

##Examining the database in SQL injection attacks

To exploit SQL injection vulnerabilities, it's often necessary to find information about the database. This includes:

###The ``type

and

version` of the database software.

You can potentially identify both the database type and version by injecting provider-specific queries. The following are some queries to determine the database version for some popular database types:

Microsoft, MySQL - SELECT @@version Oracle - SELECT * FROM v$version PostgreSQL - SELECT version()

Example -

' UNION SELECT @@version--

###The

tables

and

columns

that the database contains.

Most database types (except Oracle) have a set of views called the information schema. This provides information about the database. You can query

information_schema.tables

Example -

SELECT * FROM information_schema.tables

' UNION SELECT table_name, NULL FROM information_schema.tables--

Additionally, You can then query

information_schema.columns

Example -

SELECT * FROM information_schema.columns WHERE table_name = 'Users'

' UNION SELECT column_name, NULL FROM information_schema.columns WHERE table_name='users_ixbnuw'--

After this, we can simply select the column_name1,column_name2 from the table_name we discovered.

##Blind SQL injection

Blind SQL injection occurs when an application is vulnerable to SQL injection, but its HTTP responses do not contain the results of the relevant SQL query or the details of any database errors. This can make it hard for the attacker to exploit as there is no feedback.

Techniques like

UNION

###Triggering Conditional Response

Let's say an application tracks it's users based on TrackingID, and requests contain a header like the this -

Cookie: TrackingId=u5YD3PapBcR4lN3e7Tj4

Now, the database would check if this value exists in the database beforehand or not, and if it does it'd return a

Welcome Back

Invalid Tracking ID

Example -

SELECT TrackingId FROM TrackedUsers WHERE TrackingId = 'u5YD3PapBcR4lN3e7Tj4'

Suppose there is a table called

Users

Username

Password

Administrator

one character at a time

We can start with -

xyz' AND SUBSTRING((SELECT Password FROM Users WHERE Username = 'Administrator'), 1, 1) > 'm

This returns the

Welcome back

true

first character of the password is greater than m

Next, we send -

xyz' AND SUBSTRING((SELECT Password FROM Users WHERE Username = 'Administrator'), 1, 1) > 't

This does not return Welcome Back indicating that the injected condition is

false

first character of the password is not greater than t

And so on, we can try and test to narrow the character down and find the first characters, after that we can re-iterate the same process to find each character and construct the password!

###Error-based SQL injection

Error-based SQL injection refers to cases where you're able to use error messages to either extract or infer sensitive data from the database, even in blind contexts.

The possibilities depend on the configuration of the database and the types of errors you're able to trigger:

• >

  You may be able to induce the application to return a

  specific error response

  based on the result of a

  boolean expression

  . You can exploit this in the same way as the conditional responses.

• >

  You may be able to trigger

  error messages that output the data returned by the query

  . This effectively turns otherwise blind SQL injection vulnerabilities into visible ones.

Exploiting Blind SQL injection by triggering conditional errors

Some applications carry out SQL queries but their

behavior doesn't change

no variation in application's response

It's often possible to induce the application to

return a different response depending on whether a SQL error

database error

true

error message

Let's take an example to understand this, say we inject the following to string turn by turn to a vulnerable application,

            xyz' AND (SELECT CASE WHEN (1=2) THEN 1/0 ELSE 'a' END)='a
            xyz' AND (SELECT CASE WHEN (1=1) THEN 1/0 ELSE 'a' END)='a

Here, the

CASE

So, for the first input the condition

1=2

false

1=1

true

DivideByZero Error

This causes, difference in the response from the HTTP Server, and hence we can determine whether the injected query was

True

            xyz' AND (SELECT CASE WHEN (Username = 'Administrator' AND SUBSTRING(Password, 1, 1) > 'm') THEN 1/0 ELSE 'a' END FROM Users)='a

Extracting sensitive data via verbose SQL error messages

Misconfiguration of the database sometimes results in verbose error messages.

Example Error -

Unterminated string literal started at position 52 in SQL SELECT * FROM tracking WHERE id = '''. Expected char

This shows us the whole query constructed by the application, along with the error. This could be used to exploit the queries.

Occasionally, you may be able to induce the application to generate an error message that contains some of the data that is returned by the query. This effectively turns an otherwise blind SQL injection vulnerability into a visible one.

You can use the CAST() function to achieve this. It enables you to convert one data type to another.

Example -

            CAST((SELECT example_column FROM example_table) AS int)

            would return, ERROR: invalid input syntax for type integer: "Example data"

###Exploiting blind SQL injection by triggering time delays

If the application catches database errors when the

SQL query is executed and handles them gracefully

conditional errors will not work

blind SQL injection vulnerability by triggering time delays

true

false

The techniques for triggering a time delay are specific to the type of database being used.For example, on Microsoft SQL Server, you can use the following to test a condition and trigger a delay depending on whether the expression is true:

                    '; IF (1=2) WAITFOR DELAY '0:0:10'--
                    '; IF (1=1) WAITFOR DELAY '0:0:10'--

Here, the first condition doesn't trigger a delay as

1=2

false

###Exploiting blind SQL injection using out-of-band (OAST) techniques

An application might carry out the same SQL query as the previous example but do it

asynchronously

original thread

another thread

execute a SQL query

doesn't depend

query returning any data

a database error

time taken

In this situation, it is often possible to

exploit

triggering out-of-band network interactions

system that you control

infer information one piece at a time

Out-of-band (OAST) techniques are a powerful way to detect and exploit blind SQL injection, due to the high chance of success and the ability to directly exfiltrate data within the out-of-band channel. For this reason, OAST techniques are often preferable even in situations where other techniques for blind exploitation do work.

##SQL injection in different contexts

Up until now, we used the query string to inject our malicious SQL payload. However, we can perform SQL injection attacks using any controllable input that is processed as a SQL query by the application. For example, some websites take input in JSON or XML format and use this to query the database.

##Second-order SQL injection

First-order SQL injection occurs when the application processes user input from an

HTTP request

incorporates the input into a SQL query in an unsafe way

Second-order SQL injection occurs when the application takes user input from an

HTTP request

stores it for future use

second-order SQL injection

stored SQL injection

##How to prevent SQL injection

You can prevent most instances of SQL injection using parameterized queries instead of string concatenation within the query. These parameterized queries are also know as "prepared statements".

The following code is vulnerable to SQL injection because the user input is concatenated directly into the query:

            String query = "SELECT * FROM products WHERE category = '"+ input + "'";
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(query);

You can rewrite this code in a way that prevents the user input from interfering with the query structure:

            PreparedStatement statement = connection.prepareStatement("SELECT * FROM products WHERE category = ?");
            statement.setString(1, input);
            ResultSet resultSet = statement.executeQuery();

You can use parameterized queries for any situation where untrusted input appears as data within the query, including the WHERE clause and values in an INSERT or UPDATE statement. They can't be used to handle untrusted input in other parts of the query, such as table or column names, or the ORDER BY clause. Application functionality that places untrusted data into these parts of the query needs to take a different approach, such as:

• >Whitelisting permitted input values.
• >Using different logic to deliver the required behavior.

#Cheatsheet - SQL-Injection Cheatsheet