Guide 01 in Sarah’s Welcome to Web Security Series #

In this series, Sarah discusses some common vulnerability classes found in web security and how you can find and exploit them. Today’s focus is SQL injection, one of the easiest and best-known types of vulnerability.

Introduction #

SQL Injection, also known as SQLi, is probably one of the best-known web security vulnerabilities. Like a number of other web security vulnerabilities, it is chiefly concerned with the execution of unexpected code. As one would expect, it targets systems that use SQL databases and it is often used to extract or modify sensitive information from a database, though it is less commonly used to issue commands to the database or even the operating system. It can also be used to bypass basic login systems. If you are not familiar with SQL, it is essentially a language that is good at sorting and retrieving data from databases. The syntax is fairly intuitive, so don’t worry if you haven’t done FIT2094/FIT3171!

Finding SQLi Vulnerabilities #

One of the best places to start looking for SQL vulnerabilities is either in a login page or a catalogue search function. Why? Well, basic login systems will often simply check user input (in this case, their login credentials) against some entry in a database. Consider some login page that has a ‘username’ and ‘password’ field (Figure 1).

Now, we would have to guess what the query that retrieves the credentials might look like. A very basic statement would look something like this:

SELECT * FROM users WHERE username=’myUsername’ AND password=’123’

In normal-speak, this command essentially says “log me in if my username matches the associated password” and vice versa, which makes sense. This is why you may see SQLi vulnerabilities in very basic login pages. Moreover, a similar system might be used to retrieve information about products in an online store that uses a SQL database. Imagine you wanted to retrieve all items tagged with “stationery”. The query that might be sent to the database could look something like this:

SELECT * FROM items WHERE tag=’stationery’

This statement would also be susceptible to SQLi. However, not all such login pages or search functions will be vulnerable, for a variety of reasons. There are a few quick ways to check if they are potentially weak though:

1. In SQL, single quotes (‘) are used to demarcate user input. In unprotected systems, if you entered just a ‘, the database might respond with an error (Figure 2) or an unusual response, indicating that it is vulnerable to SQLi because it executed that quote as if it were a piece of code (rather than treating it as a plain old character).
2. Entering SQL-specific syntax, such as --, which acts as a comment, can also cause unexpected behaviour if the system is SQLi-vulnerable.
3. SQL uses Boolean conditions often (such as OR 1=1) and a SQLi-vulnerable system may show unusual behaviour if such a condition were to be entered.
4. One can also inject time-delay syntax and compare the response time of the server. For example, if MySQL was the database being used, you could enter SELECT SLEEP(10) into one of the fields and if the server took 10 seconds to respond, it indicates that the system is indeed vulnerable to SQLi.

So now that we know how to look for SQLi-vulnerable systems, how can we exploit them?

Exploiting SQLi Vulnerabilities #

The short of it is, we have to craft a suitable payload. Firstly, we have to decide what we want to do. Do we want to leak some data? Do we want to bypass a login? With that in mind, we then have to work out what kind of query is being issued to the server. This is unfortunately a process of trial and error. By seeing what kind of payloads or commands work, we can try and guess what query is being sent. Once we have established what the query (roughly) looks like, we can craft a suitable payload.

There are a few different ‘types’ of SQLi (and a few different ways to sort them) but PortSwigger splits them into the following categories:¹

• Retrieving hidden data: Manipulate a query to return more information that you would normally be able to see
  • Example: You could target an online shopping platform and view unreleased items by crafting a payload that returned everything in a table
• Subverting application logic: Alter a query to disrupt the application’s intended logic
  • Example: You could bypass a login page by manipulating the username field so that it never even checks the password
• UNION attacks: Use the SQL command UNION to retrieve data from a different table in the same database, provided certain conditions are met
  • Example: If you had a website that had two tables, one for items and one for user details, you could potentially use a UNION attack to retrieve login credentials
• Blind SQL: Use inference to attack a system. In such vulnerabilities, the results of the SQL query are not returned in the application’s response, though it might affect other elements of the site.
  • Generally, there are two types of blind attacks: Boolean-based attacks and time-based attacks, though both of these are more advanced SQLi methods

Note that ‘retrieving hidden data’ and ‘subverting application logic’ are often lumped into the one category of ‘error-based SQL’. Despite this, the process to craft a payload is more or less the same. It is simply a case of finding what works as there are hundreds of possible payloads. Fortunately, there are also a large number of ‘cheat sheets’, which list possible payloads. Remember, different database systems may have slightly different syntax, so make sure you know what kind of database you are targeting! Here are some good cheat sheets:

• PortSwigger: https://portswigger.net/web-security/sql-injection/cheat-sheet
• Invicti: https://www.invicti.com/blog/web-security/sql-injection-cheat-sheet/
• Pentestmonkey: https://pentestmonkey.net/cheat-sheet/sql-injection/mysql-sql-injection-cheat-sheet

As a simple example, let us return to our login page in Figure 1. Let us also say that we know the exact command that is being issued to the server (the one listed above). Knowing this, how can we craft a payload? Well, what if we made the server simply ignore the last half the command:

SELECT * FROM users WHERE username=’myUsername’ --AND password=’123’ 

If we could do this, then we could simply put in a username and have no need for a password to log in! This can be achieved by ‘commenting out’ the last half of the command. We can do this by escaping out of the input and then adding the syntax for a comment at the end of our username like so:

We can put whatever we want in the password field because the database will never read it! If we logged in now, the following query would be sent to the database:

SELECT * FROM users WHERE username=’admin’--’AND password=’a’

Everything after the -- is ignored by the database, allowing us to login as an admin without the password.

Defending against SQLi #

Fortunately, there are a number of ways of defending against SQLi. OWASP even has a detailed cheat sheet on how to prevent SQLi. According to it, there are four main ways of protecting against SQLi, though one of them is “strongly discouraged”.

Method one is the use of prepared statements with parameterised queries.² This essentially means “do not execute any user input just chuck it together as a big string”. Method two, stored procedures, is very similar to method one except that the stored procedures are in the database itself. Method three is to use allow-list input validation, which means that the user can only use a set of specifically permitted values and everything else is prohibited. Method four, which is strongly discouraged, is to just escape all user input. However, it is practically difficult to escape absolutely everything, hence it is not the preferred method.

There are also some other defences, like the concept of ‘least privilege’, which can be applied universally (i.e. not just to counter SQLi) to increase your security. However, each of the methods above have language-specific implementation, so if you are looking to secure your system, make sure you review the appropriate documentation first.

Conclusion #

SQLi is generally more common in applications that use PHP or ASP and rarer in newer interfaces such as J2EE or ASP.NET. However, databases are still ubiquitous, therefore by sheer force of numbers, they remain a common threat. In fact, in 2021, injections were ranked number three in the OWASP Top 10,³ a list of the year’s most significant web vulnerabilities. Therefore, it is important for up-and-coming cybersecurity professionals to be familiar with SQLi and to know both how to exploit it and how to protect against it.

Credit to PortSwigger’s WebSecurity Academy for the images. If you are interested in learning more about SQLi and web security, the WebSecurity Academy is a fantastic resource. You can create a free account and explore their labs here.