Detecting SQL Injection in Oracle-part one

An immediate success with this method is apparent, because the SQL is captured with the SQL injection attempt in it. As with other methods in this article, to take this further a few things would need to be done:

• A filter program would need to be implemented that could parse out the SQL statements and determine if any potential SQL injection attempt has taken place.
• To be of any real use the filter program would also need to extract the timestamp from the packet header as well as the source IP address.
• Extracting the database user would be extremely difficult as previous packets would need to be inspected to extract login information. This would also suggest the need to store previous packets or information about them and their sequence to do this.

As a simple solution, packet sniffing would appear to be an option provided a reasonably simple filter/parser program can be written in Perl or C. The goal would be to output the possible wrongdoing by extracting the SQL, timestamp and src and dest IP from the packet stream.

Sniffing packets within oracle (sqlnet trace)

Extracting network information closer to Oracle is possible by using the trace facility of SQL*Net, Net*8 or Oracle networking (whichever name is relevant to your database release).

Trace facilities are available for most of the Oracle networking tools such as the listener, Oracle names, connection manager, names control utility and of course the Oracle networking client and server. In this example we can concentrate on the server trace. It is possible to trace from the client end but it would be necessary to do so for all clients and thus harder to manage.

There are some disadvantages to using Oracle networking trace files as a tool to look for SQL injection:

• The trace files can grow very quickly and use an enormous amount of disk space. If not managed correctly there is a danger of filling a disk and a possible denial of service taking place.
• There is an overhead involved in writing the trace files.
• Even though it is possible to define a unique trace file name and location, Oracle appends a process ID (PID) to the trace file name. This is the operating system PID of the shadow process. The pid can be seen with the following SQL:

  SQL> select p.spid,s.username
    2  from v$session s,v$process p
    3  where s.paddr=p.addr;
  
  SPID      USERNAME
  --------- ------------------------------
  <records snipped>
  616       DBSNMP
  556       SYSTEM
  
  9 rows selected.
  
  SQL>
  

To enable trace simply add the following lines to the $ORACLE_HOME/network/admin/sqlnet.ora file:

TRACE_FILE_SERVER=pf_trace.trc
TRACE_DIRECTORY_SERVER=/tmp
TRACE_LEVEL_SERVER=SUPPORT

The parameters define where the trace is to be written, what it is called and also the level. There are four levels that can be used: OFF, USER, ADMIN and SUPPORT. They rise in detail from OFF to SUPPORT; the SUPPORT level includes the contents of the network packets.

Let''''s run our example again from SQL*Plus on a Windows client and see what is generated in the trace file. The trace file is as expected, pf_trace_616.trc. Wow, this file is 4005 lines in length just from connecting and executing the following:

SQL> exec get_cust(''''x'''''''' union select username from all_users where ''''''''x''''''''=''''''''x'''');

PL/SQL procedure successfully completed.

Searching in the trace for the packet dump we can find the SQL injection attempt sent to the database:

nsprecv: 165 bytes from transport
nsprecv: tlen=165, plen=165, type=6
nsprecv: packet dump
nsprecv: 00 A5 00 00 06 00 00 00  |........|
nsprecv: 00 00 03 5E 35 03 04 00  |...^5...|
nsprecv: 21 00 78 71 DE 00 01 52  |!.xq...R|
nsprecv: BC 39 DE 00 01 0A 00 00  |.9......|
nsprecv: 00 00 E0 39 DE 00 00 01  |...9....|
nsprecv: 01 00 00 00 00 00 00 00  |........|
nsprecv: 00 00 00 00 00 00 00 00  |........|
nsprecv: 00 00 00 00 00 00 00 E2  |........|
nsprecv: 39 DE 00 42 45 47 49 4E  |9..BEGIN|
nsprecv: 20 67 65 74 5F 63 75 73  | get_cus|
nsprecv: 74 28 27 78 27 27 20 75  |t(''''x'''''''' u|
nsprecv: 6E 69 6F 6E 20 73 65 6C  |nion sel|
nsprecv: 65 63 74 20 75 73 65 72  |ect user|

nsprecv: 6E 61 6D 65 20 66 72 6F  |name fro|
nsprecv: 6D 20 61 6C 6C 5F 75 73  |m all_us|
nsprecv: 65 72 73 20 77 68 65 72  |ers wher|
nsprecv: 65 20 27 27 78 27 27 3D  |e ''''''''x''''''''=|
nsprecv: 27 27 78 27 29 3B 20 45  |''''''''x''''); E|
nsprecv: 4E 44 3B 0A 00 01 01 01  |ND;.....|

Whilst using SQL*Net trace files are a possibility, there are a number of issues besides the items listed above:

• Once again a parser would be needed to extract the SQL text from the packet dumps and to then identify from a set of rules whether the SQL might be a SQL injection attempt.
• The PID used as part of the trace file name is not known until the user connects and the shadow process it started. If the filenames were not unique then like snoop the trace could be piped through a filter and the disk usage issue would go away.
• The trace files could be monitored regularly with a simple shell script that checks for trace files where there is no longer a shadow process running. These can then be processed and removed. Long running sessions would generate huge trace files and not be ideally managed in this way.
• It is easier to determine the OS user and database user involved with SQL*Net traces as this information can be found from the v$process and v$session views with knowledge of the OS PID.

This method is not practical for implementing a simple SQL injection utility in an organisation. The resource issues alone would make it difficult to manage and work with. It could be used sparingly when a suspected incident has occurred and where trace usage could be controlled to a narrow time band or number of clients.

Internal Oracle trace files

Oracle can also generate trace files from the Oracle kernel when SQL is executed. This can be enabled at the RDBMS level by setting the initialisation parameter SQL_TRACE=true in the init.ora file. It can also be turned on just for the instance by doing:

ALTER SYSTEM SET SQL_TRACE=TRUE;

Trace can also be turned on for the current session with:

ALTER SESSION SET SQL_TRACE=TRUE;

Before trace is turned on the parameter timed_statistics should be set to TRUE in the initialisation file. Trace files will be written to the file pointed at by the user_dump_dest parameter in the initialisation file.

Let''''s create a trace file and analyse it for our simple SQL injection example as follows:

SQL> alter session set sql_trace=true;

Session altered.

SQL> exec get_cust(''''x'''''''' union select username from all_users where ''''''''x''''''''=''''''''x'''');

PL/SQL procedure successfully completed.

SQL>

A trace file has been created with a name format of {SID}_ora_{PID).trc. The format is platform specific. The PID is the OS PID as for the SQL*Net trace file and again it can be determined from querying the views v$session and v$process. The raw trace file can be read and the SQL in question can be seen as follows:

*** 2003-06-16 16:54:01.429
*** SESSION ID:(8.29) 2003-06-16 16:54:01.408
APPNAME mod=''''SQL*Plus'''' mh=3669949024 act='''''''' ah=4029777240
=====================
PARSING IN CURSOR #3 len=33 dep=0 uid=17 oct=42 lid=17 tim=1055782441429190 hv=3
732290820 ad=''''846c85c0''''
alter session set sql_trace=true
END OF STMT
EXEC #3:c=0,e=1,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1055782441407935
*** 2003-06-16 16:54:34.876
=====================
PARSING IN CURSOR #3 len=82 dep=0 uid=17 oct=47 lid=17 tim=1055782474876639 hv=3
204430447 ad=''''8482555c''''
BEGIN get_cust(''''x'''''''' union select username from all_users where ''''''''x''''''''=''''''''x''''); END;
END OF STMT
PARSE #3:c=0,e=6430,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1055782474876611
=====================

A better way is to post process the trace file with a utility called tkprof as follows:

oracle:jupiter> tkprof emil_ora_616.trc output.trc sys=yes

TKPROF: Release 9.0.1.0.0 - Production on Mon Jun 16 16:59:50 2003

(c) Copyright 2001 Oracle Corporation.  All rights reserved.

The generated file contains quite a lot of information, including timing and user ID. The timing would need to be read from the raw trace file but the user ID can be found from the tkprof output. The processed output shows the PL/SQL package being called and also the dynamic SQL string:

<output snipped>
BEGIN get_cust(''''x'''''''' union select username from all_users where ''''''''x''''''''=''''''''x'''');
  END;


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse  
上一页  [1] [2] [3] [4] [5] [6]  下一页

[Access]sql随机抽取记录  [Access]ASP&SQL让select查询结果随机排序的实现方法
[办公软件]PowerPoint模板使用经验之谈  [办公软件]教你在Powerpoint中设置页眉页脚
[办公软件]在Powerpoint中如何插入Flash动画  [办公软件]如何在Powerpoint 中(实现)输入上标、下标
[办公软件]如何在PowerPoint同一张幻灯片中显示大量文字  [办公软件]这样来修改PowerPoint超级链接的文字颜色
[办公软件]PowerPoint小小操作技巧，让您工作更轻松  [办公软件]如何在office(PowerPoint,Word,Excel)中制作带圈的…



 

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

鄂公网安备 42011102001154号

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