Detecting SQL Injection in Oracle-part one

Trace files clearly have promise for implementing a SQL injection detection system but they also have some serious problems:

• Trace would need to be turned on globally all of the time.
• Trace generation would consume system resources; how much depends on the type of database and application.
• A huge amount of trace files would quickly consume disk space. A denial of service attack would be easy to achieve.
• As with other methods, a parser or filter program would be needed to extract the SQL, user and timing information and then to decide if the SQL was a SQL injection attempt.
• Because trace files are again generated based on OS PID, managing them would be tricky in real time to ensure that resources are not overused. Any long running sessions could easily fill a disk.

The information in trace files is good and usable but the problems with managing the trace files and the performance issues with generating trace constantly would suggest this method might not be usable. Again, as with SQL*net these files can be used sparingly.

Reading SQL from the System Global Area (SGA)

This should be the most promising method to extract SQL and analyse if a SQL injection attempt has been made. The reasons is purely because this is the heart of the Oracle RDBMS, and all SQL and PL/SQL executed spends some time in the SQL area in the SGA. There are a couple of issues to be aware of with this, however. The first is that querying the SQL area can be resource intensive and could affect performance on a critical production system and secondly it could be possible to miss SQL that has been executed. If a database runs thousands of pieces of SQL and all are different and the memory allocated for the SQL area is not large, then little used SQL (once or twice) could be aged out of the area very quickly.

Querying the SQL regularly is the key to monitoring for abuse. Too often this could affect performance and not often enough you could miss something. If an organisation were to use this method to check for abuse, start with checks maybe two or three times a day, monitor it and adjust as more is learnt.

Once again, as with the other sources of information a filter or parser is really needed to analyse the SQL extracted to give some indication as to whether it is legal or not. Start with a basic script like the one below that just checks for the existence of a union in the SQL, filter out some users perhaps, save the results to a summary table using a create table as select statement and further filter for specific tables involved. A good first approximation would be to highlight any SQL issued by a non SYS user with a union that also accesses a table or view owned by sys. Our example queries the view all_users owned by sys.

Here is a simple query that extracts the SQL from the SGA where there is a union included. This is to give the reader an idea of what data can be read. Further filtering can be done as described above.

select  a.address address,
        s.hash_value hash_value,
        s.piece piece,
        s.sql_text sql_text,
        u.username parsing_user_id,
        c.username parsing_schema_id
from    v$sqlarea a,
        v$sqltext_with_newlines s,
        dba_users u,
        dba_users c
where a.address=s.address
and a.hash_value=s.hash_value
and a.parsing_user_id=u.user_id
and a.parsing_schema_id=c.user_id
and exists (select ''''x''''
        from v$sqltext_with_newlines x
        where x.address=a.address
        and x.hash_value=a.hash_value
        and upper(x.sql_text) like ''''%UNION%'''')
order by 1,2,3
/

Running this gives:

<output snipped>
QL_TEXT                            PARSING_USER_ID PARSING_SCHEMA_
----------------------------------- --------------- ---------------
and a.parsing_schema_id=c.user_id
and upper(s
.sql_text) like ''''%UNION%''''           SYSTEM          SYSTEM
order by 1,2,3

BEGIN dbsnmp.get_cust(''''x'''''''' union    SYS             SYS
select username from all_users
where ''''''''x''''''''=''''''''x''''); END;             SYS             SYS

select customer_phone from          SYS             SYS
customers where
customer_surname=''''x''''
union select username from          SYS             SYS
all_users where ''''x''''=''''x''''

BEGIN get_cust(''''x'''''''' union select    DBSNMP          DBSNMP
username from all_users where ''''
''''x''''''''=''''''''x''''); END;                    DBSNMP          DBSNMP

select                              SYS             SYS
tc.type#,tc.intcol#,tc.position#,c.
type#, c.length,c.scal
<output snipped>

The example has been run as both the SYS user and DBSNMP user whilst this database has been up so it appears a number of times in the output. This method so far seems to be the simplest to use and offers probably the easiest way to implement a complete tool/script to analyse for SQL injection. It also has the least downside effects!

Using Oracle audit

The features available with standard Oracle audit are rich and varied but there are a number of problems with using it to detect SQL injection.

• Audit doesn''''t work at Row level only at session or access level.
• The SQL that was used at run time cannot be captured.
• There is no easy way to detect the use of a union (our example) or any of the other cases defined above that are only recognisable by parsing the SQL text.

Irrespective of any of the other methods, audit should be used. It is a robust and useful system and can reap benefits when used to detect abuse.

It is not easily possible to detect SQL injection with audit but rather to see the smoking gun. For instance if audit were enabled for select access on the tables dbsnmp.customers and sys.all_users then the audit trail should show entries when the SQL injection attempt as described in our example is used. We cannot definitely say a union was employed but access by DBSNMP to ALL_USERS should be visible.

When a hacker is attempting to guess what SQL he can add to an existing string, how to add a union or sub-select, or adding comments to truncate, SQL errors can be generated when he gets it wrong. Again it is a smoking gun and detailed analysis of errors in the audit trail and other actions by the same user in the same session can indicate what was happening.

Here is an example of using audit whilst the SQL injection example is run. First turn on audit on the ALL_USERS view and CUSTOMERS table.

SQL> audit select on sys.all_users by access;

Audit succeeded.

SQL> audit select on dbsnmp.customers by access;

Audit succeeded.

Check the audit trail with the following SQL:

col username for a8
col timestamp for a20
col action_name for a15
col obj_name for a10
col owner for a8
col sessionid for 999
col returncode for 999
select  username,
        to_char(timestamp,''''DD-MON-YYYY HH24:MI:SS'''') timestamp,
        action_name,
        obj_name,
        owner,
        sessionid,
        returncode
from    dba_audit_object
order by timestamp;

The results are:

USERNAME TIMESTAMP            ACTION_NAME     OBJ_NAME   OWNER    SESSIONID RETURNCODE
-------- -------------------- --------------- ---------- -------- --------- ----------
DBSNMP   16-JUN-2003 20:21:08 SELECT          ALL_USERS  SYS            227          0

DBSNMP   16-JUN-2003 20:21:08 SELECT          CUSTOMERS  DBSNMP         227          0

DBSNMP   16-JUN-2003 20:21:08 SELECT          CUSTOMERS  DBSNMP         227          0

This shows access to the ALL_USERS view and CUSTOMERS table by the user DBSNMP in the same session. It is simple also to write a

上一页  [1] [2] [3] [4] [5] [6]  下一页

Copyright ＠ 2007-2012 敏韬网(敏而好学，文韬武略--MinTao.Net)（学习笔记） Inc All Rights Reserved.
闵涛 E_mail:admin@mintao.net(欢迎提供学习资源)

鄂公网安备 42011102001154号

站长：MinTao ICP备案号：鄂ICP备11006601号-18