Detecting SQL Injection in Oracle-part one

Audit can clearly be used to assist in detecting SQL injection but would require considerable effort 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.

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 cannot 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. By 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 number of issues with FGA that cause the trigger to not fire correctly, for instance if a RULE hint is used in the SQL.

First check the optimiser and analyse the sample table:

SQL> analyze table dbsnmp.customers compute statistics;

Table analyzed.

SQL> select name,value from v$parameter
  2  where name=''''optimizer_mode'''';

NAME
----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
optimizer_mode
CHOOSE

Next, create a policy that will execute every time a statement is run against the table (in this case DBSNMP.CUSTOMERS).

  1  begin
  2  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 then check the audit trail for any entries:

SQL> set serveroutput on size 100000
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
<records snipped>
::SYS
::SYSTEM
::WKSYS
::ZULIA

PL/SQL procedure successfully completed.

SQL> col db_user for a15
SQL> col object_name for a20
SQL> col sql_text for a30 word_wrapped
SQL> select db_user,timestamp,object_name,sql_text
  2  from dba_fga_audit_trail
  3  order by timestamp;

DB_USER         TIMESTAMP OBJECT_NAME          SQL_TEXT
--------------- --------- -------------------- ------------------------------
DBSNMP          16-JUN-03 CUSTOMERS            select customer_phone from
                                               customers where
                                               customer_surname=''''x'''' union
                                               select username from all_users
                                               where ''''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 union in it or 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=>''''''''''''||owner||'''''''''''',object_name=>''''''''''''
||table_name||'''''''''''',policy_name=>''''''''''''||table_name||'''''''''''',audit_condition=>''''''''1=1'''''''',aud
it_column=>null,handler_schema=>null,handler_module=>null,enable=>true);''''
from dba_tables
where owner not in (''''SYS'''',''''SYSTEM'''')
/

Of course the policies would probably need 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 detection

Some solutions for protecting against SQL injection were given in the previous papers but for completeness a few of the main ideas 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 privilege principle and allow only the privileges necessary

Conclusions

SQL 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 inject an Oracle database than other products. However, that doesn''''t mean there is not a problem with 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 thought as possible to the OS and network security.

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 methods such as trace files are clearly resource hogs. The fine grained audit and reading SQL from the SGA look like good candidates, as does the consistent use of audit.

Of course, the best form of defence is to audit your database security and avoid dynamic SQL!!

• Oracle security step-by-step "A survival guide for Oracle security" - Pete Finnigan 2003, published by SANS Institute
• Oracle security handbook - Aaron Newman and Marlene Theriault - published by Oracle Press.
• Expert One-on-one - Tom Kyte - wrox press
• http://www.orafaq.com/papers/redolog.pdf
• www.petefinnigan.com/orasec.htm
• Oracle Net8 configuration and troubleshooting - Toledo and Gennick , O''''Reilly
• Oracle in a nutshell - Greenwald and Kreines - O''''Reilly
• Introduction to simple Oracle auditing"
• "SQL injection and Oracle - part one"
• "SQL injection and Oracle - part two"
• http://www.interealm.com/technotes/roby/fga.htm

上一页  [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