Oracle injection

xiaoxiao2021-03-06 67

Detecting SQL Injection in Oracle By Pete Finnigan Last Updated July 22, 2003

-------------------------------------------------- ----------------------------- IntroductionLast 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 Detely Yes ... Err ... Well Err ... probably ... this is, yes it is possible to detect sql incjection 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 should not 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 inevi tably 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 expedition.

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 solutionsThere 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 INJECCITION. AT Present Most of The Commercial Tools To Be.com.

Some Free SolutionSthe Ideal List of All Possible SQL Injection Types or Signatures Is Impossible To Define In Totality Butt Woulding 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 ie - 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 analyte sql, the following steps surplend be posible for one or more of the technique:

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.

IT IS Not Possible To Deal Time, IT IT THAN TO KNOW AFTER The Fact Than To NOT KNOW IT IS HAPPENING AT ALL.

Worked examplesNext 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' unionselect username from all_users where 'x' = 'x ': Aurora $ JIS $ UTILITY $: :: CTXsys :: dbsnmp :: emil :: fred

Let Us Now Explore What Trace, Packet, Audit and International 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 can not 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 does not 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 can not be reproduced Some advantages of Log Miner are..:

Analysis does not 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 via a GUI tool available 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 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 Analyzed 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;

LOG_MODE ------------ ArchiveLog

SQL> SELECT NAME, VALUE from V $ Parameter2 Where name in ('log_archive_start', 'log_archive_dest');

Name ------------------------------------- --------------- Value -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------- LOG_ARCHIVE_STARTTRUE

Log_archive_dest / export / home / u01 / app / oracle / admin / emil / archive

To Detect Which User Executed A Command:

SQL> SELECT NAME, VALUE from V $ Parameter2 Where name = 'transaction_auditing'

Now execute the SQL injection attempt and then use Log Miner to see what is recorded To make the analysis easier for this example, the archive log is saved before and after to ensure only this command is in the log.:

SQL> Connect Sys as Sysdbaenter Password: Connected.SQL> ALTER System Archive Log Current;

SYSTEM altered.

SQL> SQL> connect dbsnmp/dbsnmp@emilConnected.SQL> set serveroutput on size 100000SQL> exec get_cust ( 'x' 'union select username from all_users where' 'x' '=' 'x'); debug: select customer_phone from customers where customer_surname = 'x' unionselect username from all_users where 'x' = 'x' :: AURORA $ JIS $ UTILITY $ :: AURORA $ ORB $ UNAUTHENTICATED :: CTXSYS :: DBSNMP :: EMIL :: SYS: : System :: wksys :: zuliapl / sql procedure successfully completed.

SQL> Connect Sys as Sysdbaenter Password: Connected.SQL> ALTER System Archive Log Current;

SYSTEM altered.

SQL>

First Create The Log Miner Dictionary:

SQL> set serveroutput on size 1000000SQL> exec dbms_logmnr_d.build ( 'logmnr.dat', '/ tmp'); LogMnr Dictionary Procedure startedLogMnr Dictionary File OpenedTABLE: OBJ $ recorded in LogMnr Dictionary FileTABLE: TAB $ recorded in LogMnr Dictionary FileTABLE: COL $ recorded in logmnr Dictionary FileTable: TS $ Recorded in logmnr Dictionary file Procedure Executed successfully - logmnr Dictionary created

PL / SQL Procedure SuccessFully Completed.

SQL>

Find The Correct Archive Log File:

SQL> SELECT NAME2 from V $ archived_log3 where completion_time = (SELECT MAX (COMPLETITON_TIME) from V $ archived_log);

Name ------------------------------------- ------------------------------ / Export / Home / U01 / App / Oracle / Admin / EMIL / Archive / 1_7. DBF

SQL>

Now loading the archive log file into log miner:

SQL> EXEC DBMS_LOGMNR.ADD_Logfile ('/ export / home / u01 / app / oracle / admin / emil / archive / 1_7.dbf', sys.dbms_logmnr.new);

PL / SQL Procedure SuccessFully Completed.SQL> EXEC DBMS_LOGMNR.Start_logmnr (DictFileName => '/tmp/logmnr.dat' /tmp/sql procedure successfully completed.

SQL>

Finally, Search The Results:

SQL> SELECT SCN, Username, TimeStamp, SQL_REDO2 from V $ logmnr_contentssql> SCN UserName TimeStamp SQL_REDO ------------------------- ------ ------------------------------ 253533 dbsnmp 16-jun-03 set transaction read write; 253533 dbsnmp 16-jun-03 Update "Sys". "AUD $" set "Action #" = '101', "ReturnCode" = '0', "logoff $ limited" = '228', "logoff $ pread" = '0 ', "Logoff $ LWRITE" =' 10 ', "Logoff $ dead" =' 0 ', "logoff $ time" = to_date ('16 -jun-200312: 16: 12', 'DD-MON-YYYY "

SCN Username TimeStamp SQL_REDO ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------HH24: MI: SS '), "sessioncpu" =' 5 'where "action #" =' 100'and "Returncode" = '0' AND "LOGOFF $ LOGOFF $ PREAD" IS NULL AND "LOGOFF $ LWRITE" IS NULL AND "LOGOFF $ DEAD" is Null and "logoff $ time" is null and "sessioncpu" is null and rowid = ' Aaaabiaabaaaaaewaax ';

The first thing that can be noticed is that Log Miner does not process select statements and display the output in 9i. The Log Miner package does not support selects as they are not stored in the redo logs. It is possible to use Log Miner to read on-line redo logs but I will leave that to the reader to experiment with. Even though SQL injection can be detected in insert, delete and update statements, Log Miner is not suitable for detecting SQL injection. This is due to its lack of Being aable to detect select statements as well as something above.com

The main issue with packet sniffing for connections to the Oracle database is that the Oracle network protocol is proprietary and not published. Does that matter for trying to ascertain if SQL injection attempts have taken place? Probably yes, as access to the protocol would allow a . better design and efficient tool for this task Without access to the protocol and no wish to reverse engineer it, the task is limited to grabbing ASCII text strings from the wire There are both advantages and disadvantages with this method.:

Advantages:

A System Could Be Implement ON A Separate Server Allowing Real Time Analysis without Impacting The Source Database.disadVantages:

This method can be resource intensive. The packets need to be sniffed close to the source and on the same subnet to ensure all packets pass in front of the sniffer. If the Oracle advanced security options for encrypting of network packets or any other third party solution is used to encrypt, sniffing packets will not work. If, as in our example, the SQL injection attempt is passed as a call to a package procedure then the true internal dynamic SQL will not be visible. Instead you will simply see the typed in command. Packet sniffing every packet will generate a huge amount of data. Piping the packets through a filter program is a solution to mitigate this issue. to demonstrate, we will use snoop on Solaris to see what is visible within network packets. Start up snoop And Fire the SQL from A SQL * Plus Session:

ROOT: JUPITER> Snoop -ta-x 0 Jupiter and Port 1521 | StringSUSING Device / dev / hme (Promiscuous Mode) 15: 06: 34.31348 172.16.240.3 -> jupiter TCP D = 1521 s = 1404 ACK = 299902194 SEQ = 26460609 LEN = 174 WIN = 84130: 0800 2092 9D88 00A0 CCD3 A550 0800 4500 .. ........ p je.16: 00d6 6884 4000 8006 596D AC10 F003 AC10 ..h. @ ... ym .. .... 32: F00B 057C 05F1 0193 C1C1 11E0 24F2 5018 ... | ........ p.48: 20DD 0F36 0000 00AE 0000 0600 0000 0000 ..6 ....... ..... 64: 1169 36A4 61DE 0001 0101 0303 5E37 0304.i6.a ....... ^ 7..80: 0021 0078 71DE 0001 52BC 39DE 0001 0A00.! Xq ... r.9 ..... 96: 0000 0000 0000 0000 0000 .... 9 ......................................................................... ....... 128: E239 DE00 4245 4749 4E20 6765 745F 6375 .9.... u.... bGIN GET_CU144: 7374 2827 7827 2720 756E 696F 6E20 7365 ST ('x' Union SE160: 6C65 6374 2075 7365 726E 616D 6520 6672 Lect Username Fr176: 6F6D 2061 6C6C 5F75 7365 7273 2077 6865 Om all_users Whe192: 7265 2027 2778 2727 3D27 2778 2729 3B20 re '' x '=' 'x'); 208: 454E 443B 0 A00 0101 0101 0000 00 00 0001 End; ............ 224: 0800 0105 .... 15: 06: 34.33281 jupiter -> 172.16.240.3 TCP D = 1404 s = 1521 ACK = 26460783 SEQ = 299902194 LEN = 54 WIN = 248200: 00A0 CCD3 A550 0800 2092 9D88 0800 4500 ..... p .. ..... E.16: 005E 094A 4000 4006 F91F AC10 F00B AC10. ^. J @. @. ........ 32: F003 05F1 057C 11E0 24F2 0193 C26F 5018 ..... |. $ ... OP.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 strings 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 with Oracle (SQLNet Trace)

Extracing Network Information Closer to Oracle Is Possible By Using The Trace Facility of SQL * NET, NET * 8 OR ORACLE NETWORKING (Whichever Name is Release 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.Username2 from V $ Session S, V $ Process P3 where s.paddr = p.addr;

Spid username -------------------------------------- 616 dbsnmp556 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.TRCTRACE_DIRECTORY_SERVER = / TMPTRACE_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' '=' '' '');

PL / SQL Procedure SuccessFully Completed.

Searching in the trace for the packet dump we can find the SQL Injection Attempt Sent To The Database:

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 resour ce 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.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 a Number of Problems with use. It to detect sql.

Audit does not work at Row level only at session or access level. The SQL that was used at run time can not 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 shouth 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 Happensening.

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 a8col timestamp for a20col action_name for a15col obj_name for a10col owner for a8col sessionid for 999col returncode for 999select username, to_char (timestamp, 'DD-MON-YYYY HH24: MI: SS') timestamp, action_name, obj_name, owner, sessionid ReturnCodefrom dba_audit_ObjectOrder 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

This Shows Access to the all_users view and customs table by the user dbsnmp in The Same Session. It is simple also to write. It is simple.................

Audit CAN CLEARLY BE. Assist in Detecting SQL Injection To Define "Good" SQL queries and audit settings to reliably "guess" At SQL Injection Attempts.

For more information see the previous article by this Author About Oracle Audit for a brief Introduction To ITS USE.

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' '=' '' '');

PL / SQL Procedure SuccessFully Completed.

SQL>

A TRACE 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.408Appname 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 = 373290820 ad = ' 846c85c0'alter session set sql_trace = truend of stmtexec # 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 = 3204430447 ad = '8482555c'BEGIN get_cust (' x '' union select username from all_users where '' x '' = '' x '); END; END OF STMTPARSE # 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

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: begin get_cust ('x' union select username from all_users where '' '' '=' '' X ');

Call Count CPU Elapsed Disk Query Current Rows -------------------------------------------------------------------------------------------------------------- ------------------ ---------- Parse 1 0.00 0.00 0 0 0 0 0 0 1FETCH 0 0.00 0.00 0 0 0 0 0 ---------------------------------- - - ---------- ---------- Total 2 0.00 0.01 0 0 0 1

Misses in library cache during parse: 1Optimizer goal: CHOOSEParsing user id: 17 select customer_phonefromcustomers where customer_surname = 'x' union select username from all_userswhere 'x' = 'x'

Call Count CPU Elapsed Disk Query Current Rows -------------------------------------------------------------------------------------------------------------- ---------------------------- Parse 1 0.01 0.02 0 0 0 0 0 1 01 01 01 0.00 0 0.00 0 184 5 36 ---------------------------------- - ---------- ---------- Total 39 0.03 0.03 0 184 6 36

Misses in Library Cache During Parse: 1Optimizer Goal: Chooseparsing User ID: 17 (Recursive Depth: 1)

Rows Row Source Operation --------------------------------------------- ------------ 36 Sort Unique36 Union-All

The User ID Can Be Read from The Database As:

SQL> SELECT UserName, user_id2 from dba_users3 where user_id = 17;

Username User_id -------------------------------------- dbsnmp 17

SQL>

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 USABLES AND THE PERFORMANCE ISSUES WIENERATING THITHOD Might Not Be usable. Again, AS with SQL * NET .............

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 A Djust 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 Table or View OWNED by sys. Our example.

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_idfrom v $ sqlarea a, v $ sqltext_with_newlines s, dba_users u, dba_users cwhere a.address = s.addressand a.hash_value = s .hash_valueand a.parsing_user_id = u.user_idand a.parsing_schema_id = c.user_idand exists (select 'x'from v $ sqltext_with_newlines xwhere x.address = a.addressand x.hash_value = a.hash_valueand upper (x.sql_text) like'% Union% ') Order By 1, 2, 3 /

Running this gives:

QL_Text Parsing_user_id Parsing_Schema_-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------- and a.parsing_schema_id = C.user_idand Upper (s.sql_text) Like '% Union%' System SystemOrder by 1, 2, 3

Begin dbsnmp.get_cust ('x' union sysselect usrname from all_userswhere '' 'x' '=' '' x '); END; SYS SYS

Select customer_phone from syscustomers wherecustomer_surname = 'x'Union select username from sysall_users where' x '=' x '

Begin get_cust ('x' union select dbsnmp dbsnmpusername from all_users where '' '=' ''); end; dbsnmp dbsnmp

Select sys systemc.type #, tc.intcol #, tc.position #, c.TYPE #, C.LENGTH, C.SCAL

Oracle injection (six) end

Database Triggers

Database triggers are usually the next line of defence when Oracle's internal audit is used. The normal audit facilities operate at object level or privilege level and are not useful for determining what happened at the row level. Triggers can be used for this. To use them involves programming a trigger for each table and for each action such as insert, update or delete. The main failing with triggers is that they can not be written to fire when a select takes place against a table or view.Extracting the actual SQL used to do the update, delete or insert is not possible. It is possible to create SQL that is used to alter the table the trigger fires on by reading before and after values for each row and creating the trigger to fire for each row.

In View of these Restrictions, Database Triggers Are Not Really Useful in The Quest to Find SQL INJECTION Attempts.

Fine grained Auditing

With version 9i, Oracle introduced fine grained auditing (FGA). This functionality is based on internal triggers that fire every time an SQL statement is parsed. As with fine grained access control it is based on defining predicates to limit the SQL that will be audited Using Audit Policies, Auditing Can Be Focused On A Small Subset of Activities Performed Against A Defined Set of Data.

The one advantage that this functionality brings is the ability to finally monitor select statements at the row level. The standard handler function also captures SQL, so this looks like it could be a good tool to check for SQL injection.

Indeed Oracle states in their on-line documentation for this package that it is an ideal tool to implement an IDS system with. However it remains to be seen as to whether anyone has successfully used this package and policies as a base for an IDS tool. Let's set up a simple policy and see if our example injection can be caught. The first requirement is that the tables with FGA set up against them need to have statistics generated by analysing. Also the cost-based optimiser must be used. There are a .

First Check The Optimiser and Analyze The Sample Table:

SQL> Analyze Table dbsnmp.customers compute statistics;

Table analyzed.

SQL> SELECT NAME, VALUE from V $ Parameter2 Where name = 'Optimizer_Mode';

NEXT, CREATE A Policy That Will Execute Every Time A Statement Is Run Against The Table (In this case dbsnmp.customers).

1 begin2 dbms_fga.add_policy (object_schema => 'DBSNMP', 3 object_name => 'CUSTOMERS', 4 policy_name => 'SQL_INJECT', 5 audit_condition => '1 = 1', 6 audit_column => null, 7 handler_schema => null , 8 handler_module => null, 9 enable => true); 10 * End; SQL> /

PL / SQL Procedure SuccessFully Completed.

SQL>

Next, Execute the SQL INJECTION EXAMPLE AND THEEN CHECK The Audit Trail for Any Entries:

SQL> set serveroutput on size 100000SQL> exec get_cust ( 'x' 'union select username from all_users where' 'x' '=' 'x'); debug: select customer_phone from customers where customer_surname = 'x' unionselect username from all_users where = = $ = u: =>>>>> p> p p p p p p p p p p p p p p p p p p> p p p p p SuccessFully Completed.

SQL> col db_user for a15SQL> col object_name for a20SQL> col sql_text for a30 word_wrappedSQL> select db_user, timestamp, object_name, sql_text2 from dba_fga_audit_trail3 order by timestamp;

DB_USER TIMESTAMP Object_Name SQL_Text ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------- DBSNMP 16-JUN-03 Customers Select Customer_Phone fromCustomers WhereCustomer_Surname = 'X' UnionSelect User from All_Userswhere 'X' = 'x'

SQL>

Okay, success. The dynamic SQL is shown including the union join to the ALL_USERS system table. The use of FGA would need to be expanded to every table in the application schema. Then reports would need to be written that returned only entries that violate some Of the Basic Rules We defined At the beginning, Such as SQL with a line such as 'x' = 'x'.

All of the policies could Be Easily Generated Using SQL SUCH AS:

SELECT 'EXEC DBMS_FGA.ADD_POLICY (Object_schema =>' '|| oer ||' ', Object_name =>' '|| Table_name ||' ', Policy_Name =>' '|| Table_name ||' ', audit_condition =>' '1 = 1', audit_column => null, handler_schema => null, handler_module => null, enable => true); 'from dba_tableswhere Owner Not in (' sys', 'system') / Of Course The Policies Would PROBABLY NAED BETTER NAMES TO AVOID NAME CLASHES AND IT WOULD BE Prudent To include Some logic in a handler function to analyse the sql for abuses.

Protection Is Better Than DetectionSome Solutions For Protecting Against SQL Injection Were Given in The Previous PaperS But for Completeness A Few of The Main Ideas Areas Are INCLUDED Here Again:

Do not use dynamic SQL that uses concatenation. If it is absolutely necessary then filter the input carefully. If possible do not use dynamic PL / SQL anywhere in an application. Find another solution. If dynamic PL / SQL is necessary then use bind variables. Use Authid Current_User in Pl / Sql So That It Runs As The Current User and NOT The Owner. Use Least Privile Principle And Allow Only the Privileges Necessary

ConclusionsSQL injection is a relatively new phenomenon and is being embraced by attackers with gusto. No figures are yet available to quantify how big the problem is for Oracle-based systems. At present more exposure in the press, technical sites and publications is given to other Database Products. I believe this is because it is slightly harder to sql incject An Oracle Database Than Other Products. However, That Doesn't mean there is not a problem. Oracle DataBases.

The other reason there are no accurate figures is that most companies probably would not even know anyone is using SQL injection against their Oracle database. I hope that this paper has given some insight to the issue and some simple ideas for DBAs and security managers to monitor for this problem.As I have stated before, one simple solution is to not connect your Oracle database to the Internet (or intranet) if it is not necessary. Secondly, do not use dynamic SQL or PL / SQL and if you do, use Bind Variables. Also Audit and Secure The Data With as much thing..............

The simple test cases above have shown that there are indeed a number of trails left in the database or network trace files when SQL injection is attempted. Therefore it should be possible for the DBA to use some of the above sources as a basis for a detection Policy. Some of the method.......................

Of Course, The Best Form of Defence is to audit your database security and avoid dynamic sql !!

转载请注明原文地址:https://www.9cbs.com/read-85976.html

9cbs

New Post(0)