What is SQL Injection?
SQL Injection is a classic database attack. Though it seems silly to talk about it in 2020, it’s still a very common problem. It involves passing malicious code to the database through improperly handled parameters. It’s typically performed on a website search function where the attacker can pass certain characters and statements through to the backend to manipulate return results or even damage the database. Additionally, if the user who accesses the database has elevated permissions, the attacker can do serious damage. There are many SQL Injection techniques; we’re going to look at some basic examples and how to protect against them.
SQL Injection in use
While this is normally performed on websites, there is nothing limiting it to that. I have created a very simple windows form app connecting to the AdventureWorks2014 database to showcase some SQL Injection examples and solutions. It consists of a text box to pass our search criteria, a button to search, a data grid to view the results, and finally, a label to display the query passed to the server. Normally, you (obviously) wouldn’t want users to know what the actual SQL statements are in use, but it is helpful to see exactly what is going on. Below is the initial code that allows SQL Injections.
It’s bad code. The main issue is how the SQL Statement is defined and passed to the backend. It’s a string concatenation based on user input from a text box. When the form’s button is clicked, it reads the contents of the text box and uses that in the search predicate. Without appropriate handling, attackers can take advantage of this to manipulate what is passed to the server. Let’s look at some examples.
This first image is a simple shot to show that the query does return normal results. Now for some injection.
The ‘Always True’ injection
This first attack is a classic ‘Always True’ or 1=1 attack. The result of this is expanding the result set to include more than the intended result. It works by taking advantage of the databases processing of the OR operator. Because OR will return true if either side is true, this effectively allows attackers to return the entire result set. As you can see in the image below, records that don’t fall into the initial search criteria are returned.
Executing multiple statements
By injecting characters to end the first statement and appending an entire second statement into the search string, the attacker may do considerably more damage. If the account that accesses the database has elevated permissions, a serious breach or considerable damage can occur.
The following example extracts the database schema from the attack. With this additional information, the attacker can continue to attempt to extract data from additional tables of their choosing. By utilizing UNION ALL you can string together multiple statements. In the image below, we’re able to pull back the schema using known system views.
Oh, there’s a credit card table!
Finally, if you have not locked down permissions the attacker can perform extremely damaging attacks—like dropping a database:
Or even more nefarious, the attacker can pass a series of commands to enable xp_cmdshell:
From there, they can execute OS commands (e.g. downloading rogue applications):
Followed by opening a reverse shell to another machine:
What can you do about it?
You can start with the basics. The principle of least privilege. Restrict this user to only the required permissions. That will at least limit the potential exposure. Only give the user the exact permissions they require. Explicitly deny system objects that can help limit exposure. As seen above, if your system isn’t locked down, you can easily lose control of your server. From there, the attacker can put in any manner of backdoors and potentially grab other user credentials. Soon enough, your entire environment is compromised. This can be accomplished on any system, regardless of criticality.
In this day and age, there’s no reason not to protect yourself against SQL Injection. Code that interacts with the database needs to have some form of input verification. Most client libraries have some form of parameterization, and this should absolutely be used. Never use dynamic SQL unless you absolutely must. For example, the following code replaces the dynamic SQL with parameters.
Now our simple always true attack fails:
In a world where cyber-attacks are becoming increasingly common, it’s critical that you secure your environment at every facet. SQL Injections are attacks that potentially leak critical data and can even easily lead to system take-over. Thankfully, they are simple enough to mitigate through best practices and appropriate security measures. Anexinet offers a full range of services to help lock down your environment. If you need a security evaluation or mitigation, please contact us!
SQL Server Architect