SQL Injection — A Complete Guide

A deep dive inside theoretical knowledge of SQL injection like what is SQLi, How to find SQLi, How to exploit SQLi, and finally the remediation or preventive measures.

SQL Injection

What is SQL Injection (SQLi)?

SQL injection is a vulnerability that consists of an attacker interfering with the SQL Queries that an application makes to the database.

So if you as an attacker can manage to interact and change the SQL Query by inserting SQL characters or SQL code! in an input vector of an application, then the application is definitely vulnerable to SQLi.

Let us understand using an example:

So consider an application that web-server hosts have a login functionality and it has 2 input vectors username and password field respectively and they talk to the backend Database using SQL Queries and the username field is vulnerable to SQL injection.

What it means is when we pass some of the SQL characters along with the username they are also incl. in the backend Query and an attacker can use this flaw to exploit the login functionality to access the backend database and retrieve info like hashed passwords from the user’s table or credit card info and N no of things.

So what is happening over here is the attacker is passing admin’ — in the username field and what this ‘ — character is doing is closing and commenting out the rest of the query and the backend database does notchecks for the password field anymore and allows the attacker to get access to the admin account.

What is the impact of a successful SQL Injection Attack?

So it depends upon the context of the SQL Injection vulnerability that you are exploiting some of them allow you to only view data while some allow you to modify data as well.

CIA Triad

Types of SQL Injection

In-Band SQLi — Here, An Attacker uses the same communication channel to both launch an attack & gather the results of the attack.

It is further divided into 2 types:-

a. Error-based SQLi — In this case, we force the database to provide an error which ends up giving us more information about how things operate at the backend.

For example: — Breaking the background query by putting a SQL character in an input vector which is vulnerable to SQLi & gives us an error usually an internal server error and with the response we sometimes get things like the version of a Database and if we are lucky enough the exact SQL Query which makes our life so much easier from an attacker’s perspective.

b. Union-based SQLi — This vulnerability, leverages the UNION operator to combine the Results of two Queries into a single result set i.e., to output the result of our query with the query that application makes to the backend database.

ofcourse the union operator has certain condition for it to work and which makes this type of SQLi much more difficult and it’s not like we can output or run any query we want, we’ll discuss this in much more detail and learn about those conditions and how to fulfill them in exploitation section of this article.

Inferential (Blind) SQL Injection — Here there is no actual transfer of data via the application, You don’t see the results in the application itself instead we are stuck asking the application TRUE & FALSE questions.

It is also bifurcated into 2 branches:-

a. Boolean-based SQLi — It is a type of blind SQLi technique that uses the boolean conditions to return a different result depending upon whether the query returns a TRUE or FALSE result.

Let us consider a scenario, here we are given an id parameter which is vulnerable to boolean based SQLi so we ll try to force different payloads one true and another false and compare their results, if the 2 payloads end up giving different results, then in one case title will be displayed and in another it won’t and thus id parameter is vulnerable to boolean based blind SQLi, now you must be wondering how we can leverage that to extract info from the backend database, DON’T WORRRY I GOT YOU!!!

suppose there is a login functionality and inside the user’s table there exists admin’s hashed password so in order to extract that we can use this vulnerable id field and with the help of SUBSTRING function we are saying the string to extract from is the result of this query which is the hashed password “select password from user’s table where username is administrator” and another parameter it takes is position here it is 1st character and last parameter is no of character’s so we have chosen and the result of this query we are going to check with all the alpha-numerica characters and if we found our character then title will product with id = 1 will be displayed else it wont and this whole thing can be quite repititive and cumbersome if done mnually so what you can do it automate it using a Python script.

b. Time-based Blind SQLi — It relies on the database pausing for a specified amount of time, then returning the results, indicating a successful SQL Query execution.

Out of Band SQL Injection — This occurs when an attacker is unable to use the same channel to gather the results of the attack, It Relies on the ability of an application to make a Network Connection. For eg: DNS, HTTP Req. to deliver data to the attacker.

How to exploit SQLi?

There are majorly 2 ways to go about testing for any Web app vulnerability, in general, these are: -

• Black Box Web App Pentesting: little to no info apart from URL & Rules of engagement is given i.e., we are mimicking an ext. attack scenario.
• White Box Web App Pentesting: The tester has access to everything incl. the Source code of the application.

Black-Box testing methodology:-

• Mapping the application:- It includes performing things like visiting URLs, walkthrough pages that are accessible within the user’s context, making a note of all the input vectors that potentially talk to the backend, trying to understand the logic that is how the application works, finding subdomains, enumerating directories, and pages and while doing all this don't forget to capture all these requests with your burp proxy.
• Fuzzing the Application:- Submitting SQL-specific characters such as ‘ or “, and looking for errors or other anomalies, 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 Out of Band Application Security Testing (OAST) Payloads designed to trigger an out-of-band network interaction when executed within a SQL Query, and monitor for any resulting interaction.

White-Box testing methodology:-

• enable web server logging, enable database logging
• Map the application:- Visible functionality in the application, Regex search on all instances in the code that talk to the database.
• Code Review:- follow the code path for all input vectors.
• Test any Potential SQLi Vulnerabilities.

Exploiting Error-based SQLI:-

• Submit SQL-specific characters such as ‘ or “, and look for errors or other anomalies.
• Different characters can give you different errors.

Exploiting Union-based SQLI:-

• There are two rules for combining the results sets of two queries by using UNION:-
  -> The number and the order of the columns must be the same in all queries
  -> The data types must be compatible
• Exploitation:-
  -> Figure out the no of columns that the query is making
  -> Figure the data types of the columns (mainly interested in string data)
  -> Use the UNION operator to output info from the DB.
• Determining the no.of columns req in a SQL injection UNION attack using ORDER BY:

select title, cost from product where id=1 order by 1;

• Incrementally inject a series of ORDER BY clauses until you get an error or observe a different behavior in the App.

order by 1--
order by 2--
order by 3--

• The ORDER BY position number 3 is out-of-range of the no. of items in the select list.
• Determining the no.of columns req in a SQL Injection UNION Attacks using NULL Values:

select title, cost from product where id=1 UNION select NULL--

• Incrementally inject a series of UNION SELECT payloads specifying a different no.of all null values until you no longer get an error.

'UNION select null--

• All Queries combined using a UNION, INTERSECT or EXCEPT must have an equal no. of expressions in their target lists.

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

• Finding columns with a useful data type in an SQL injection UNION attack:
  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--

• conversion failed when converting the varchar value ‘a’ to data type int

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

Exploiting Boolean-based Blind SQLI

• Submit a Boolean condition that evaluates to False and note the response
• Submit a Boolean condition that evaluates to True and note the response

If the response is different then you have a Boolean-based Blind SQLi Vuln.

Write a program that uses conditional statements to ask the database a series of True/False questions and monitor response.

Exploiting Time-based Blind SQLi

• Submit a payload that pauses the application for a specified period of time

Write a program that uses conditional statements to ask the Database a series of True/False questions and monitor response time

Exploiting Out of Band SQLi

• Submit OAST (Out of band Application Security Testing) payloads designed to trigger an out-of-band network interaction when executed within an SQL Query, and monitor for any resulting interactions
• Depending on SQL injection use different methods to exfil data

Automated Exploitation Tools

1. Sqlmap

• used to find SQLi vuln
• open source
• customizable
• parameters, vigorousity can be customized
• could also get us shell in certain cases

2. Vulnerability Scanners

• Burp Suite
• Arachni
• Wapiti
• Acunctix
• w3af

3. Static Analysis Tools ( for White box perspective )

How to Prevent SQLi

Primary Defense —

• Use of Prepared Statements ( Parameterized Queries )
• Use of Stored Procedures ( Partial )
• Whitelist Input Validation ( Partial )
• Escaping All User-Supplied Input ( Partial )

Additional Defenses —

• Also: Enforcing the Least Privilege
• Also: Performing Whitelist Input Validation as a Secondary Defense

Use of Prepared Statements

code vulnerable to SQLi

String Query = "SELECT account_balance FROM user_data 
                WHERE user_name="+request.getParameter("CustomerName");

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

User-supplied input “CustomerName” is embedded directly into the SQL statement

The construction of the SQL statement is performed in two steps:

• The application specifies the query’s structure with placeholders for each user input
• The application specifies the content of each placeholder

code not vulnerable to SQLi

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

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

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

Partial Options

Option 2: Use of Stored Procedures

• A stored procedure is a batch of statements grouped together and stored in the DB
• Not always safe from SQL Injection, still need to be called in a parametrized way

Option 3: Whitelist input validation

• Defining what values are authorized, Everything else is considered unauthorized
• Useful for values that cannot be specified as parameter placeholders, such as the table name

Option 4: Escaping All user-supplied input

• Should be only used as a last Resort

Additional Defenses

1. Least Privilege

• The application should use the lowest possible levels of privileges when accessing the DB
• Any unnecessary default functionality in the DB should be removed or disabled
• Ensure the CIS benchmark for the database in use is applied
• All vendor-issued security patches should be applied in a timely fashion

2. Whitelist Input Validation

I would like to give the whole credit to Rana Khalil, this is just a mere imitation of her fantastic teaching, I just wanted to write all this stuff down just for the sake of note-taking, I don't have any intention of plagiarism.

Resources:

What is SQL Injection? Tutorial & Examples | Web Security Academy

SQL Injection

My Socials

If you like my write-ups and want to know more about me and Cybersecurity in General feel free to ping me here:

JavaScript is not available.

https://www.linkedin.com/in/yadavdipanshu/