Technique of the Week: SQL Code Injection

Oct 11, 2022 | Cybersecurity Awareness Month, Cybersecurity & Risk Management, Technology, Transformation

Cybersecurity Awareness Month is here and in celebration, the Mariner Vulnerability Assessment and Penetration Testing team are showcasing some of their favorite hacking techniques. Each week in October, they will explain a vulnerability, demonstrate how they might exploit it, describe its potential impact, and maybe even share some tips on how you can protect yourself.

 

Mariner penetration testing services span a wide range of domains – web and mobile applications, internal and external infrastructures, phishing simulations, wireless security assessments, and more. Testing is performed by combining real-world techniques with tried-and-true standards to provide a realistic picture of your organization’s security posture. Vulnerabilities are identified, analyzed, and broken down with prioritized remedial recommendations to help your IT teams strengthen defences where you need it most.

Check out the first in our series: Stealing User Credentials with Open Redirects

SQL Injection, also known simply by SQLi, is likely one of the most discussed and well-known attacks to applications, even more so in the context of web applications. Even though it was first publicly discussed late 1990s (Phrack Magazine #54), SQLi is quite often misunderstood and still present in applications today.

What is SQLi?

SQL (short for Structured Query Language) refers to a programming language that was specifically designed to manage data in a relational database, where data is organized in the form of tables. A ‘query’ in this context is: any command sent to the database. For example, a SELECT query will perform a search in the data. A query using the statements INSERT, UPDATE, or DELETE would add, modify, or remove data from the database.

There are several database systems that are based on SQL, such as Microsoft SQL Server, Oracle, MySQL, MariaDB, PostgreSQL, and many more.

As the name hints, a SQL Injection is a vulnerability that allows an attacker to inject SQL code into queries that an application makes to a database.

One of the most common examples of SQL injection is a login page, where the user can supply a username and a password, and the application verifies if the credentials are valid. Such application can be implemented by searching for the user information and returning the password (likely obscured by a hashing algorithm) and comparing it with the password provided by the user.

The following is a code snippet (lines are numbered for easy reference) in Python that can do just that:

Notice that in line 6, the query statement is built by concatenating the command with the username and passwords provided, and it includes single quotes around the username and password. This is to tell the SQL server that the data (username) is a string, i.e., a sequence of readable characters, and to avoid confusion with other parts of the command.

If the user typed admin as the username and as the password, the command, or query, to be executed by the SQL server would be something like the following:

However, an attacker could try to add a single quote to the input supplied in the username field. For example, if they typed admin’ (note the single quote at the end of the word) as the username, the code sent to the database server would be something like the following:

The SQL command now has two single quotes after the word admin; the database server would interpret the remaining of the command, up to the next single quote, as part of another string (notice that the strings are depicted in red). This query would ultimately result in an error.

By improving on this, the attacker could simply add – – (two hyphens) as part of the username, which now becomes admin’- –. This would result in the following command being sent to the database server.

The database would interpret anything after as a comment (depicted above in blue), and simply ignore it. In this case, the database would search for a user called admin, regardless of what their password really is. So, if there is a user named admin, our attacker would successfully be granted access to the application.

 

Some misconceptions

Before we move on with our discussion about SQL injections, it is important to elucidate some common misconceptions around it, as they tend to downplay the risks associated with SQLi, often hindering the efforts in identifying and mitigating SQLi.

“It only affects Web applications.”

Lately, the vast majority of applications that are supported by an SQL database do happen to be web applications. However, our consultants at Mariner have also encountered several desktop and mobile applications that were affected by a SQLi vulnerability.

Any application that relies on a SQL database can potentially be affected by this vulnerability, regardless of how it is deployed.

“Using dropdown and buttons in web forms protects my code from SQL Injection.”

Many developers believe that only fields in which the user can directly type some data are potentially affected by SQLi and that by restricting the data entry in the user interface, they can protect their systems.

However, attackers are not confined to the user interface. Quite often, attackers (and pen-testers alike) would attack the communication protocol directly, bypassing the user interface to send data directly to the application server. This is why it is important to always perform input validation on the server side and not only on the client side.

“A SQLi attack may affect your database, which can result in a privacy breach or even allowing an attacker to delete all your information”.

This statement is in fact technically true; an SQLi vulnerability can potentially give to an attacker the ability to access to all the data stored in the database and even to delete all of your data with a single command. There is, however, a limiting aspect to what the statement implies. Many practitioners interpret that it describes the worst possible outcome.

In reality, the resulting impact of an SQLi vulnerability can be much more severe. Depending on which database is in use and how it is configured, it is not uncommon for a SQLi vulnerability to lead to remote code execution (RCE) at the operating system level, which essentially gives an attacker total control over the computer hosting the database server. So, an SQLi vulnerability does not ONLY affect your database, it can also potentially affect all of your systems, serving as an entry point for an attacker to infect your entire network.

 

Going beyond the database

In the following example, we present one approach an attacker may use to take over your systems.

Meet our vulnerable application

For demonstration purposes, let us assume a web application for a rental company that allows a user to search for items based on their current status (available or rented). This application is running on a LAMP environment. The acronym LAMP stands for “Linux, Apache, MySQL, PHP”, and it describes the infrastructure that supports the application.

 

After selecting one of the options and clicking on search, the application will search in the database and return all items with the status that was chosen by the user. For example, if a user chose “Rented”, they would have the following page in return.

 

Looking at the URL (http://example.com/rental/search.php?status=Rented), we can notice that page being executed (“search.php”) and that it includes an argument (status=Rented). Arguments in the URL are easily identified because they follow a question mark.

Looking at the code of the application, in the file search.php we can find the following statement (lines numbered for easy reference):

This code is, unsurprisingly, vulnerable to SQLi. But how would an attacker exploit it? After all, the only way to enter the data is by selecting one of the options in the dropdown, right?

As mentioned previously, an attacker does not always need to follow the user interface. In this case, they can attempt to directly send arguments to the URL seen above. For example, an attacker could simply place the following URL on their browser to obtain a complete list of items, regardless of their status.

Now, to an actual and more impactful attack.

Attacking the application

Assuming that an attacker has already identified the vulnerability and the infrastructure used, they could send the following to obtain information about system configuration.

 

Under the header ‘Title’ we find the contents of @@secure_file_priv, which is a global variable that indicates where the database can securely save files, in this case, there is no information, so it may be anywhere.

The attacker then would attempt to store a file in the web folder. The default for many Linux distributions is /var/www/html. Our attacker added the application folder (rental), using the following URL.

After that, the attacker can test whether it worked, trying to access the file they created.

Success! With a few small changes, they can execute any command in the vulnerable system.

First, they create a malicious program to execute any program at the operating system level.

Then, they finally can execute any command under the user that is running the web service (in our case, www-data).

 

Mitigation

First and foremost, make sure that, during the development of your application, your developers use parameterized queries (also known as prepared statements) to avoid string concatenation for all user-provided input.  Feel free to contact us if you need additional recommendations for secure code development, particularly when string concatenation cannot be avoided.

Outside the realm of development, other security measures are also recommended:

  • Proper configuration and service isolation. Ensure that the database service cannot reach folders where the application is stored. Likewise, the application service should not be able to directly reach the files where the data is stored, only via the database server. Ideally, the application service and the database service should be hosted in different machines (even if only different virtual machines/containers).
  • Avoid running services (database and application) as a privileged user With that, if an attacker eventually injects code and is able to take control of the operating system layer, they would be limited to an unprivileged user. So, the attacker would not have full control over your machine and would need additional steps to escalate privileges, likely by identifying and exploiting other vulnerabilities. This would actually give you time to detect and respond to the attack.

If you are not sure whether your application is vulnerable to SQL injections or any other vulnerabilities, reach out to Mariner today!

 


Daniel de Castro, Senior Cybersecurity Consultant
Let’s Connect

 

 

 

 

Categories

Tags

Share This