In the spirit of reconciliation SEEK acknowledges the Traditional Custodians of Country throughout Australia and their connections to land, sea and community. We pay our respect to their Elders past and present and extend that respect to all Aboriginal and Torres Strait Islander peoples today.
What is SQL Injection?
SQL Injection is a common web security vulnerability and it is featured 3rd in the 2021 OWASP top 10 list of web application security risks alongside other injection related vulnerabilities.
At a high level, SQL Injection allows a threat actor to manipulate SQL queries that a web application makes to a backend relational database.
It can allow attackers to either view sensitive inforrmation, or interfere with the integrity by modifying or deleting information.
In certain circumstances it is also possible to gain control of the underlying server or other backend infrastructure, which can then allow a threat actor to pivot and move laterally across the network.
So it can be a pretty severe bug.
How does SQL Injection work?
So how does SQL injection work?
There are several ingredients usually required for a successful SQL injection:
- Firstly a web application needs to accept arbitrary user input
- The input field is then used to create dynamic SQL queries sent a relational database
- The application also does not have any protective mechanisms against SQL injection such as:
- input sanitisation that strips dangerous characters
- parameterised queries or prepared statements
Here we have provided three simplistic examples:
So in the first example we have an SQL query for authentication based on matching the username and password with existing entries in a database.
SELECT * FROM users WHERE username = ‘user_input' AND password = ‘user_input’;
The user input could be from a login page on the frontend, and since it accepts arbitrary input, we can simply break out of the existing SQL structure using a single quote, and inject an ‘OR 1=1’ to ensure the result is always TRUE, and then add a comment (–) to prevent errors in the statement. This effectively allows an authentication bypass.
Similarly, if we are retrieving let’s say product data
SELECT name, price FROM products WHERE category = ‘user_input’;
Perhaps we could combine the results of two tables together in a single response to access a sensitive table, otherwise known as a UNION-based SQL injection.
Or in certain circumstances we can terminate the original query and inject a completely new SQL statement altogether, otherwise known as a STACKED-query.
Impacts of SQL Injection
So what are the impacts of SQL injection?
From a technical perspective, we can gain unauthorized access to personally identifiable information and sensitive data.
A threat actor can also alter and delete data, thereby compromising the integrity of our databases.
If the SQL database is misconfigured or has elevated permissions, an attacker may be able to gain control of the underlying server and backend infrastructure.
It is also possible to interfere with application logic such as via the authentication bypass mentioned earlier.
From a business perspective, depending on the importance of the data, this can lead to varying degrees of reputational and brand damage, and also financial penalties in severe circumstances.
SQL injection is still a very common vulnerability even in 2024, researchers are still able to find significant attacks such as bypassing TSA security checks by authenticating as administrators in a login system that airlines use.
Or steal user records from vulnerable websites.
Types of SQL Injection
Types of SQL injections
There are three major types of SQL injections – the standard, otherwise known as in-band SQL injection, inferential SQL injection and out-of-band SQL injection.
An in-band SQL injection is where the attack and retrieving the result occur in the same place.
Effectively that means we can manipulate an input field, send the request and expect to see the database provide information in some way shape or form in the HTTP response.
This could be in the form of an error, where the application will provide an error in the database, which shows up in the HTTP response, which we can then use to exfiltrate information.
Or we can enumerate the number of columns of a particular SQL query, and then use a UNION to combine multiple SQL statements in a single HTTP response to exfiltrate information.
Inferential SQL injections do not provide any feedback in the HTTP response, however we can infer that what we sent to the database is TRUE or FALSE based on certain patterns.
For example - If we are able to craft two conditional statements using a vulnerable input field, one returning TRUE and the other returning FALSE, and we can see a noticeable difference in the HTTP response length, or status code, or potentially a difference in the actual response, then we leverage this difference to slowly exfiltrate information by asking the database a set of TRUE / FALSE statements. This is known as a Boolean-based blind SQL injection.
If there isn’t any noticeable difference in the response length or status code etc. but we can see that there is a difference in the response time from when the server responds to us, so for example I inject an SQL query that instructs the database to sleep for 3 seconds before responding, and the server takes over 3000 milliseconds to respond, then it is vulnerable to time-based blind SQL injection.
Finally, we have out of band SQL injection attacks, where it is not possible to retrieve information from the response, and the information must be obtained through a difference communication channel such as via DNS or HTTP requests to an outbound server.
How to Find and Exploit SQL Injection?
As a security engineer how do we find and exploit SQL injection?
Determine if the web application interacts with a relational database. Insert SQL sensitive characters (single quotes “’”, semi-colons “;”, comment delimiters “#, –” etc.) into various input fields. Observe for unusual responses from the web server (errors, status code, response times etc.). Attempt to enumerate the database structure (tables, columns etc.) manually or via automated tools (SQLmap)
Scan and identify all SQL interactions in the repository (SELECT, INSERT, UPDATE, DELETE statements). Review repositories for instances of dynamic SQL queries and string concatenation. Review repositories for unsafe input sanitization functions.
Introduction to SQLmap
Open Source and Automated CLI Penetration Testing Tool for SQL Injection Features:
- Detecting common SQL injections
- Enumerating database
- Dumping and exfiltrating data … and much more! Weaknesses:
- Can only detect common injection points
- need modifications for complex SQL queries
- Verbose and noisy
- can be identified easily by WAF and IDS/IPS systems
SEEK Examples - PostgreSQL Helper Function
In the past, we identified a vulnerability in a PostgreSQL helper function that allowed an unauthenticated GraphQL query to become vulnerable to inference-based SQL injection – in this case a time-based blind SQL injection attack.
The request contains a sort field which allows string values such as ascending (ASC) or descending (DESC), however if we append another PostgreSQL query instructing the server to sleep via the pg_sleep_for() function, it was possible to delay the response from the server by the designated interval.
This indicated that the application was vulnerable to time-based attacks.
Here I have to mention that traditional payloads used against the request were ineffective because of defensive mechanisms such as WAFs – for example if we used the standard pg_sleep() function it would be blocked, and if we used an integer for the time to sleep instead of the string it would also be blocked. Therefore some level of creativity was required to be able to exfiltrate data but it is still possible, which is why WAFs should only be used as a defense in depth mechanism against injection-based attacks.
Breaking down the code, we noticed that the sort field in the DataLoaderKey interface accepts an arbitrary string value, and this was passed as a parameter value into the pgHelper.getItems() function later on.
Intuitively based on the description it appears the getItems() function is used to select and retrieve data from a database table.
If we go to the actual function, we can see various constants that are defined that accept user input, such as the ORDER BY, LIMIT, OFFSET and GROUP BY constants. These are all later concatenated into a single query before passed to the underlying database.
The issue here is the effectively the concatenation, but only the ORDER BY sort clause was vulnerable since it accepted a string from the threat actor, which allowed the attacker to craft complex payloads to be executed here.
The fix was to implement prepared statements and prevent the use of dynamic queries as such.
Here we break down one of the many enumeration payloads we used to further exploit the application.
| Since it’s an inference-based SQL injection, we need to create a TRUE | FALSE condition to enumerate table names. The first part is to close off the existing PostgreSQL payload and append an OR ( | ) for valid syntactic output. |
Then we have our internal query to retrieve the first table from information.schema.tables. We can add OFFSET following the LIMIT if we want to enumerate subsequent table names. The surrounding substr function allows us to evaluate if the first character of the table begins with the letter ‘e’.
| We use the CASE WHEN for conditional logic, and we need the TRUE | FALSE condition, which in this case would be to pause the database for 2 seconds if the result is TRUE. Otherwise the database will not sleep if it is FALSE. |
The consequences arising from this issue was that we could enumerate database names and tables, exfiltrate information from the “public” databases, and retrieve database management system users.
SEEK Examples – Ruby ActiveRecord ORM
Another example of SQL injection here at SEEK involves the Ruby ActiveRecord ORM. ORM, or Object Relational Mapping creates an object layer in order to communicate with relational databases for object-oriented programming languages. ORMs usually have a set of SQL safe functions, however if we circumvent these functions and pass in raw SQL queries, or if these queries are not properly sanitized, then they can still be vulnerable to SQL injection attacks.
An example is ActiveRecord (which is the Ruby on Rails ORM), which has a sanitize_sql() function that is used to prevent SQL injection attacks. The problem with this function is that no operation is performed when the function is passed in a string, effectively allowing raw SQL input to be passed to be executed.
We can do a deeper dive and find the Sanitization ruby file in the Ruby on Rails repository and determine that the sanitize_sql_for_conditions function, which sanitize_sql is an alias of performs no operation when it is not passed an array.
In this code snippet example here, we use the sanitize_sql() function and pass in the job_ids variable that has been converted to an array, but then subsequently joins all the elements into a single string. As the sanitize_sql() function performs no operations against strings, this effectively leaves the job_ids values susceptible to SQL injection when it is eventually passed to the database.
Prevent SQL Injection
Avoid
- Dynamic Queries
- String Concatenation
- Escaping User Input (as the only protection)
- Raw SQL Queries in ORM frameworks
Use
- Prepared Statements with Parameterized Queries
- Properly Constructed Stored Procedures
- Use ORM frameworks
Defense in Depth
- Allow-list Input Validation
- Principle of Least Privilege