Explaining SQL injection using PowerShell and SQL Server

20

In this post I demo and explain a few variations of basic SQL injection using PowerShell and SQL Server.

What is SQL injection?

SQL injection, also refereed to as SQLi, is a security vulnerability that allows attackers to modify the queries that an application makes to its underlying database.
This type of vulnerability can allow attackers to interact with data that they are not normally able to access, including data belonging to other application users.

Depending on the application login’s permissions on the instance or a combination of db_owner and trusted database, the attacker may even end up accessing other databases on the SQL Server instance or even interact with the underlying OS via xp_cmdshell and an improperly secured service account.

Is it still a thing?

It sure is! The latest and most relevant example being “Bypassing airport security via SQL injection“.

Not all applications/websites are relying on coding best practices that prevent SQL injections such as parametrized queries or stored procedures, not all of them are behind Web Application Firewalls, and WAFs aren’t always 100% impossible to bypass.

How does SQL injection work?

Let’s say the application database has table with the following structure:

The IsSecret column dictates which products should be visible to the general public, records with IsSecret = 1 are not visible to normal users of the application.

For normal users, the application gets a string provided by the user (in this case it’s just the letter “a”), adds it to the WHERE clause as a filter for the product name, and sends the following query to search for desired products:

But, since the query uses a literal value for filtering on the Name column, if the user inputs a search string such as the following:

The query would end up looking like this:

This retrieves all products regardless if they’re secret or not, because the second filtering condition is now commented out.

What the above string does

Search string element	Effect
‘	Sets the ending of the string used for product name filtering right after the first %
;	Terminates the statement. This isn’t mandatory for SQL Server (yet), but some RDBMS will yell at you if the statement terminator is missing.
—	Comments out any subsequent filtering T-SQL. In this case making the database engine ignore the AND [IsSecret] = 0 condition.

The practical SQL injection example using PowerShell

Environment set-up

You just need a SQL Server instance or container (in Podman or the new sqlcmd), where you can run the following T-SQL:

To emulate the application behavior, I’ve added a PowerShell script named SQLi_with_PS.ps1 in my blog’s GitHub repo.

You’ll need to edit the script’s connection details (the first 4 lines excluding the comment) to match your environment:

Running the script

Open PowerShell, navigate to where the script is located and run it via:

You’ll be prompted to input the search string. Here are a few examples searching for “chair” and then for any product containing the letter “a” in its name:

Note that the script only exists if you answer N when prompted if you want to search again.

The SQL Injection part

Ok, now to actually try some SQL injection payloads for the search string.

To understand what’s happening, the query is also displayed before executing it.

Get products normal users shouldn’t see:

Notice the additional products.
Yes, those are all The Expanse references.

But wait, there’s more

Ok, I was initially planning on this being a fairly short post, but there’s a lot more stuff I can cover with this script and it would be a shame not to.

UNION SQL injection attack

Since we’re successful returning data with our fairly basic SQLi payload, we can also leverage the vulnerability into a UNION SQL injection attack.

Basically this allows an attacker using a specially crafted SQL injection payload to append the results of another query to the the existing one.
You can read more on UNION attacks on PortSwigger’s site.

We already know that the query returns 2 columns, so we don’t need to waste any time figuring that out through trial and error.

Getting the instance and current login

Using the following payload we can get the name of the SQL Server instance and the login name that the application uses to connect to it:

The instance name and login name are appended to the result.

Get the names of the other databases on the instance

With the following payload, the application will return all the user database names:

I’ve added NULL as the second column’s value because it matches any data type and it also makes things easier for sorting. Otherwise the database names would have been scattered between product names.

Checking if the current login is a sysadmin

You can get pretty creative here as long as you limit your output to two columns and appended result set’s data types match the ones of the initial result set.

This is a bit more intricate since we need to ensure that the second column is VARCHAR, but it gets the job done:

Last UNION SQL injection example – getting a list of SQL logins and their hashes

The following payload appends a list of SQL logins found on the instance plus their password hashes:

An attacker can then crack these offline to identify the clear text passwords.

But wait, there’s more – part 2

In this case, the SQL injection vulnerability is not limited to just retrieving data.
It can be leveraged to also execute other commands on the underlying SQL Server instance.

The commands that can be executed are only limited by the permissions of the application login.

For example, the following payload would create a new database on the instance:

Keep in mind that you’ll need to check using a UNION payload if the desired effected of the executed command was achieved.
This is because the output from the command won’t be shown by the application.

Fixing the SQL injection vulnerability in this example

To fix the vulnerability in the query demoed here, the query needs to be parametrized.

Going from this:

To this:

In this case, any string provided by the user will be contained within the @SearchString parameter.
This means that it can consist of anything without allowing the contents to interfere with the query.

I’ve made a non-vulnerable version of the demo script, named SQLi_with_PS_Fixed.ps1.

Here are two examples of SQL injection attempts no longer working with this script:

Notice the lack of results for the two SQL injection payloads.

What was the fix?

In this specific case, the relevant portion of the PowerShell code was changed from this:

To this:

Basically, switching from a query that uses a literal string passed through the $SearchString PowerShell variable, to a parametrized query that uses the @SearchString SQL parameter to handle said string.

Since the parameter ensures that the string it contains is treated as such and not as valid T-SQL, any attempt of SQL injection will fail.

Conclusion

I hope this demo of SQLi using PowerShell and SQL Server helped you get a basic understanding of how SQL injection works.

This type of vulnerability is still present in the wild and is responsible for multiple high profile breaches.