Detecting SQL Injection in Oracle
by Pete Finnigan
last updated July 22, 2003

--------------------------------------------------------------------------------

Introduction
Last year I wrote a two-part paper about SQL Injection and Oracle. That paper explored which SQL injection techniques are possible with Oracle, gave some simple examples on how SQL injection works and some suggestions on how to prevent attackers and malicious employees using these methods. Those SQL Injection papers can be found here:

"SQL injection and Oracle - part one"
"SQL injection and Oracle - part two"
This paper takes the subject further and investigates the possibilities for the Oracle Database Administrator (DBA) to detect SQL injection in the wild against her Oracle database. Is it possible to detect SQL injection happening? If so what tools and techniques can be employed to achieve this?

The main focus of this paper is to explore some simple techniques in extracting logging and trace data that could be employed for monitoring. The aim is to show the reader what data is readily available so they can make their own mind up about what can be useful. The paper will not cover commercial solutions. Because a true SQL injection tool would involve writing a parser or filter to analyse the SQL statements a fully featured tool is unfortunately beyond the scope of a short paper - I leave the implementation of such a tool to interested readers.

Example code given in this paper can be obtained from http://www.petefinnigan.com/sql.htm.

Can SQL Injection be detected?
The short answer is definitely yes... err... well err... probably... that is, yes it is possible to detect SQL injection but probably not all of the time for all cases and not always in real time. The reasons for this are many and complicated:

There are many different forms of SQL injection attacks that can take place - these are limited only by the hacker's imagination and the DBA's foresight (or lack thereof) to protect the database and provide the least privileges necessary.
Identifying SQL that shouldn't be there is not simple. The reason SQL injection is possible is because of the use of dynamic SQL in applications. This intended dynamic SQL means that the set of all legal SQL statements is harder if impossible to define. If the legal statements are impossible to define then so are the illegal ones.
Distinguishing normal administration from an attacker is not always easy as an attacker can steal an administrator's account.
Detecting SQL injection inevitably involves parsing the SQL statement for possible additions or truncations to it. Table names and view names need to be extracted and checked to see if they should be altered.
For a technique to be useful it should not affect the performance of the database too much.
Corroborating data such as usernames and timestamps are also need to be extracted at the same time.
Many more...
It is possible to detect SQL injection attempts in general and specifically against Oracle. How can we do this, and what data is available? This paper attempts to explore these questions.

The first step is to define the boundary conditions, or what actions are to be detected and then to look at the possible free solutions within Oracle and how these can be used to good effect.

Some possible commercial solutions
There are no real commercial solutions that specifically detect SQL injection attempts against an Oracle database. There are a reasonable number of firewall products that incorporate an Oracle proxy and a few IDS tools that claim to support Oracle. A number of companies are at present seriously looking into the design and development of a true application IDS for Oracle, and perhaps these tools will detect SQL injection. At present most of the commercial tools to be used properly would need rules and signatures to be defined for the specific Oracle cases.

Some free solutions
The ideal list of all possible SQL injection types or signatures is impossible to define in totality but a good starting point would cover the following possibilities:

SQL where the addition of a union has enabled the reading of a second table or view
SQL where an unintentional sub-select has been added.
SQL where the where clause has been short-circuited by the addition of a line such as 'a'='a' or 1=1
SQL where built-in or bespoke package procedures are called where they should not be.
SQL where access is made to system tables and/or application user and authentication tables.
SQL where the where clause has been truncated with a comment i.e --
Analysis of certain classes of errors - such as multiple errors indicating that select classes have the wrong number of items or wrong data types. This would indicate someone trying to create an extra select using a union.
The key is to keep it simple at first; trying to do something too complicated with ad-hoc and built in tools will never work efficiently and effectively. It is important to not get too clever with SQL and the assumptions about what is legal SQL and what is hacker-created SQL. Beware of the false positives. Keep it simple and be proactive - use more than one method if possible and extend and learn.

Any tool or system employed to detect SQL injection could identify most of the above list of possibilities. In trying to identify where the data can come from to analyse SQL, the following steps should be possible for one or more of the techniques:

Grab the SQL as it is sent to the database or as soon after as possible
Analyse the SQL to check for some or all of the above cases that indicate SQL injection
Obtain user and timestamp data
Concentrating on grabbing the SQL and whether it is possible to get timestamp and user info as well as possible further analysis leads to the following list of possibilities:

Pre-existing packet sniffers / IDS tools such as snort (not included in the experiments below)
A free packet sniffer such as snoop
Oracle networking trace files
Oracle server trace files
Extracting the SQL from the Oracle server memory (SGA)
Use of a tool such as Oracle Log Miner and possibly the raw analysis of redo logs
Oracle Audit
Database Triggers
Fine grained audit (FGA)
There are some issues to be aware of. The audit facilities can rarely be used for more than a smoking gun. If Oracle advanced options are used to encrypt network traffic then extracting the SQL from the network will be difficult. If trace facilities are used they tend to generate huge amounts of data and consume system resources. Any method that does not allow the detection of select statements whilst trapping others is really not useful.

If it is not possible to detect SQL injection taking place in real time, it is better to know after the fact than to not know it is happening at all.

Worked examples
Next we can work through some simple examples of a SQL injection attempt using one of the examples from my previous papers. The first step is to create the sample customer table, add data, and also create the demonstration procedure get_cust.

An example SQL injection attempt that will be used below to see if it is detected is:

SQL> exec get_cust('x'' union select username from all_users where ''x''=''x');
debug:select customer_phone from customers where customer_surname='x' union
select username from all_users where 'x'='x'
::AURORA$JIS$UTILITY$
::AURORA$ORB$UNAUTHENTICATED
::CTXSYS
::DBSNMP
::EMIL
::FRED

Let us now explore what trace, packet, audit and internal information is available that records any evidence of running this query.

Log Miner

Oracle provides two database package procedures DBMS_LOGMNR and DBMS_LOGMNR_D that allow archive logs and on-line redo logs to be analysed. The redo logs contain all the information to replay every action in the database. These are used for point in time recovery and for transaction and data consistency. However there are some serious problems with the Log Miner functionality. These can be listed as follows:

If an MTS database is used, Log Miner cannot be used due to the internal memory allocation of this tool. Log Miner uses PGA memory which would not be visible to each thread used in Multi Threaded Server (MTS).
The tool doesn't properly support chained and migrated rows and also objects are not fully supported. Analysis of index-only tables and clusters are also not supported. The tool can still be used even though the output has gaps in it.
The SQL generated by Log Miner is not the same SQL executed by the user. This is because the redo logs store enough data to change the data at row and column level and so the original compound statements cannot be reproduced.
Some advantages of Log Miner are:

Analysis doesn't have to be done in the source database so archive logs could be moved to a dedicated database for the whole organisation and analysed offline.
There is a GUI tool available via the Oracle Enterprise Manager (OEM)
Also, to make the use of this tool realistic the database has to be in ARCHIVELOGMODE and transaction_auditing needs to be true in the initialisation file for user information to be included.

This is a very effective tool for after the fact analysis and forensics to find out exactly when some event occurred within the database and who did it. It can be used successfully to help recover, for instance, a table deleted by accident.

Redo logs can also be analysed by hand the hard way. A good paper demonstrating this can be found here (PDF).

Now we can run through the example and explore the contents of the archive logs. First check if the database is in ARCHIVELOGMODE, determine where the archive logs are written to, and finally that username auditing is on.

SQL> select log_mode from v$database;