Sunday 25 November 2012

Immuning code from SQL INJECTION Attacks (Part 1)

There are various methods of how the plsql coders write their code to avoid sql injection attacks, and here we will see some methods and tips of how we can really protect the code..

Native dynamic SQL code what i feel is actually little easy to read and write than the code that uses the DBMS_SQL package, and runs much faster specially when it can be optimized by the compiler. But thats not the point of our discussion, the point is that SQL injection is a pretty famous attack and we can avoid this with strict adherence to some basic coding practices....

Assuming that you know the concepts of dynamic sql and then bind arguments, and what oracle says you must use dynamic SQL and not only this using bind arguments protects us against SQL injection attacks. Using bind arguments also enables cursor sharing, and thus improves application performance.

If an interface is not available to an attacker, it is clearly not available to be abused. Thus the first, and arguably most important, line of defense is to reduce the exposed interfaces to only those absolutely required.

Be alert of the following rights :=

Use Definer's Right: When you want to provide users unrestricted access to a table or tables via a subprogram, create the subprogram with definer's right.

Use Invoker's Right: When the purpose of the subprogram is to perform a parameterized, but powerful, operation by using the privileges of the user that invokes it, create the subprogram with invoker's right. Using invoker's rights helps to limit the privileges, and thereby, minimize the security exposure. However, it is not sufficient as the sole measure for eliminating SQL injection vulnerabilities.

Invoker's rights helps to minimize the security exposure

For the following procedure if you give execute privilege to HR user lets say, HR user would only be able to change the password of any user including the user SYS only if he has the ALTER USER system privilege.

create or replace procedure change_password (p_username varchar2 default null,p_new_password varchar2 default null)
is
v_sql_stmt varchar2(500);
begin
v_sql_stmt := 'ALTER USER '||p_username||' IDENTIFIED BY '||p_new_password;
execute immediate v_sql_stmt;
end change_password;
/

How bind arguments should be used ? Basically complete protected code from sql injection attack can be achieved only through elimination of input string concatenation in dynamic SQL, so 1 should avoid input string concatenation and use bind arguments be it automatically through static SQL or explicitly through dynamic SQL statements. A basic code of how bind arguments are used is below :=

create or replace procedure sum_it_up
    ( a number, b number, c number)
    is
    begin
    dbms_output.put_line(a+b+c);
    end;
   /


  DECLARE
   x NUMBER := 2;
   y NUMBER := 5;
   plsql_block VARCHAR2(100);
  BEGIN
   plsql_block := 'BEGIN sum_it_up(:g, :g, :h); END;';
   EXECUTE IMMEDIATE plsql_block USING x, y;
 END;
 /

How to reduce the actual attack, below we will see how the code is not immune in the first go and then how the code is immune in the following 2 cases ( PROCEDURE GET_CODE), below is the table of citizens where humas have their social security number which is the most private number and shall be hidden or protected :=

SQL> create table citizens (fname varchar2(20),lname varchar2(20), s_security_num number);

Table created.

SQL> insert into citizens values ('karan','dodwal',10034007);

1 row created.

SQL> insert into citizens values ('ram','singh',10059991);

1 row created.

SQL> commit;

Commit complete.

--FIRST ORDER ATTACK

Now look how it is vulnuerable in the following code

create or replace procedure get_code (p_fname varchar2 default null)
is
type c is ref cursor;
cv c;
vcode citizens.s_security_num%type;
v_stmt varchar2(300);
begin
v_stmt := 'select s_security_num from citizens where fname='''||p_fname||'''';
dbms_output.put_line('sql query : '||v_stmt);
open cv for v_stmt;
loop
fetch cv into vcode;
exit when cv%notfound;
dbms_output.put_line('code is '||vcode);
end loop;
close cv;
exception when others then
dbms_output.put_line(sqlerrm);
dbms_output.put_line('sql query '||v_stmt);
end;
/

Social security number of only ram is printed in the following output

SQL> exec get_code('ram');
sql query : select s_security_num from citizens where fname='ram'
code is 10059991

PL/SQL procedure successfully completed.

But look below how the Social security number of KARAN is also printed without even knowing RAM or KARAN, just by dummy ''X''

SQL>  exec get_code('x'' union select s_security_num from citizens where ''x''=''x');
sql query : select s_security_num from t1 where fname='x' union select
s_security_num from t1 where 'x'='x'
code is 10034007
code is 10059991

ISNT IT REALLY BAD...

--PROTECTION

--Now see how it isnt vulnuerable in the following code because here we are avoiding the use of dynamic SQL with concatenated input values

create or replace procedure get_code (p_fname varchar2)
is
begin
for i in (select s_security_num from citizens where fname=p_fname)
 loop
dbms_output.put_line(i.s_security_num);
end loop;
end;
/

Social security number of only ram is printed in the following output

SQL> exec get_code('ram');
10059991

PL/SQL procedure successfully completed.

But look below how neither of Social security numbers are printed and attacker is defeated clearly

SQL>  exec get_code('x'' union select s_security_num from citizens where ''x''=''x');

PL/SQL procedure successfully completed.

In my subsequent posts i will share with you other protection mechanisms, but for the time being i wish you all happy thanks giving and also do note the following practices to observe when you secure the Oracle database:

1) Encrypt sensitive data so that it cannot be viewed.
 
2) Evaluate all PUBLIC privileges and revoke them where possible.

3) Do not widely grant EXECUTE ANY PROCEDURE.

4) Avoid granting privileges WITH ADMIN option.

5) Ensure that application users are granted minimum privileges by default. Make privileges configurable if necessary.

6) Do not allow wide access to any standard Oracle packages that can operate on the operating system. These packages include:
        UTL_HTTP, UTL_SMTP, UTL_TCP, DBMS_PIPE, UTL_MAIL, and UTL_FTP

7) Certain Oracle packages such as UTL_FILE and DBMS_LOB are governed by the privilege model of the Oracle DIRECTORY object. Protect Oracle DIRECTORY objects.

8) Lock the database default accounts and expire the default passwords.

9) Remove example scripts and programs from the Oracle directory.

10) Run the database listener as a nonprivileged user.

11) Ensure that password management is active.

12) Enforce password management. Apply basic password management rules, such as password length, history, and complexity, to all user passwords. Mandate that all the users change their passwords regularly.

13) Lock and expire the default user accounts and change the default user password.

Wednesday 27 June 2012

Solution for connection time taking long in oracle

I was at the client side back in november 2011 and repeatingly the same month i got few queries, client was complaining that some times the connection to database take time and give time out error , how i can check this problem in database . so to begin with i looked at the contents of SQLNET.INBOUND_CONNECT_TIMEOUT parameter in sqlnet.ora file, in addition to that this is what was there in sqlnet.ora - NAMES.DIRECTORY_PATH= (TNSNAMES).

Getting more details i saw some parameter values and and process parameter is 1500 fore each node :=

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size           integer     0
java_soft_sessionspace_limit         integer     0
license_max_sessions                 integer     0
license_sessions_warning             integer     0
logmnr_max_persistent_sessions       integer     1
session_cached_cursors               integer     20
session_max_open_files               integer     10
sessions                             integer     49
shared_server_sessions               integer

Sometimes what happens is that if you have a shared connection, and the instance has a dispatcher parameter set higher than needed, connection time is affected inversely(sometimes on the order of minutes). I faced such a problem in a big database and it was actually solved by reducing the max_dispatchers parameter. Moreover if you see above in the client's system the value for sessions parameter seems to be very low. Normally its calculated as Sessions = (1.1 * PROCESSES) + 5

Just to explain the above SQLNET.INBOUND_CONNECT_TIMEOUT parameter in details, we use it to specify the time, in seconds, for a client to connect with the database server and provide the necessary authentication information.

If the client fails to establish a connection and complete authentication in the time specified, then the database server terminates the connection. In addition, the database server logs the IP address of the client and an ORA-12170: TNS:Connect timeout occurred error message to the sqlnet.log file. The client receives either an ORA-12547: TNS:lost contact or an ORA-12637: Packet receive failed error message.

The default value of this parameter is appropriate for typical usage scenarios. However, if you need to explicitly set a different value, Oracle recommends setting this parameter in combination with the INBOUND_CONNECT_TIMEOUT_listener_name parameter in the listener.ora file. When specifying the values for these parameters, note the following 2 recommendations:

1) Set both parameters to an initial low value.

2) Set the value of the INBOUND_CONNECT_TIMEOUT_listener_name parameter to a lower value than the SQLNET.INBOUND_CONNECT_TIMEOUT parameter.

For example, you can set INBOUND_CONNECT_TIMEOUT_listener_name to 2 seconds and SQLNET.INBOUND_CONNECT_TIMEOUT parameter to 3 seconds. If clients are unable to complete connections within the specified time due to system or network delays that are normal for the particular environment, then increment the time as needed... Have a nice day




Saturday 25 February 2012

Difference between a child cursor and a parent cursor?



For basic information every sql statement in library cache has a parent cursor , it has a handle which is used to search hash value in library cache hash chains for the cursor handle. Mostly it points to its child cursors which are created due to optimizer mismatch and offcourse if semantics are different but the syntax is same. Check them in v$SQL_SHARED_CURSOR why childrens are not being shared.

Every cursor (aka sql statements)  gets two cursors in the library cache. One parent and one child cursor. This is the implementation for cursor multi versioning.

Each child cursor is also comprised of a handle and an object. The child object is comprised of two heaps numbered 0 and 6. Heap 0 contains all the identifying information for a particular version of the SQL statement and heap 6 contains the execution plan. This distinction between parent and child cursors is maintained even when there is only one version of each SQL statement.

The parent cursor contains the SQL statement text only, but no execution plan. Execution plans are found in child cursors. Child cursors are also called versions. 

Let us consider a case: A SQL statement was executed first time, then it would get a parent and child. If the same sql statement was executed again (with no change to the body of SQL keeping the same hash_value ), with a different non-shareable attributes, then a new child cursor will be added to this parent cursor. As you can see, you can have many child cursors associated with a parent cursor. Non-shareable attributed includes : such as different optimizer_mode, different session parameters etc.

In the absence of adaptive cursor sharing you would see lot of child cursors getting created if cursor_sharing was set to similar, for each unique value in the presence of histogram because of data skew after bind variable value peeking. Even if the plan is same you would see a unique child cursor for a distinct value. Even the length of the bind can cause a new child cursor to be created.

I did a following test on my laptop running 11G R2 on Rhel 5 on VM

SQL>create table karan (id number)

Table created.

SQL> select * from karan;

no rows selected

SQL> select sql_id, sql_text, child_number,optimizer_mode,
plan_hash_value from v$sql where sql_text like 'select * from kar%';

SQL_ID
-------------
SQL_TEXT
----------------------------------------------------------------------------------------------------
CHILD_NUMBER OPTIMIZER_ PLAN_HASH_VALUE
------------ ---------- ---------------
c99yw1xkb4f1u
select * from karan
           0 ALL_ROWS        1357081020


SQL> col SQL_TEXT format a40
SQL> /

SQL_ID        SQL_TEXT                                 CHILD_NUMBER OPTIMIZER_ PLAN_HASH_VALUE
------------- ---------------------------------------- ------------ ---------- ---------------
c99yw1xkb4f1u select * from karan                                  0 ALL_ROWS        1357081020


SQL> select sql_id, sql_text, child_number,optimizer_mode,plan_hash_value from v$sql where sql_text like 'select * from kar%'

SQL_ID        SQL_TEXT                                 CHILD_NUMBER OPTIMIZER_ PLAN_HASH_VALUE
------------- ---------------------------------------- ------------ ---------- ---------------
c99yw1xkb4f1u select * from kar                                  0 ALL_ROWS        1357081020

SQL> show  parameter optimizer_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      ALL_ROWS

SQL> alter session set optimizer_mode=first_rows;

Session altered.

SQL> select * from karan;

no rows selected

SQL> select sql_id, sql_text, child_number,optimizer_mode,plan_hash_value from v$sql where sql_text like 'select * from kar%'
  2  ;

SQL_ID        SQL_TEXT                                 CHILD_NUMBER OPTIMIZER_ PLAN_HASH_VALUE
------------- ---------------------------------------- ------------ ---------- ---------------
c99yw1xkb4f1u select * from karan                                  0 ALL_ROWS        1357081020
c99yw1xkb4f1u select * from karan                                  1 FIRST_ROWS      1357081020


SQL> select id+1 from karan;

no rows selected

SQL> select sql_id, sql_text, child_number,optimizer_mode,plan_hash_value from v$sql where sql_text like 'select id+1 from kar%';

SQL_ID        SQL_TEXT                                 CHILD_NUMBER OPTIMIZER_ PLAN_HASH_VALUE
------------- ---------------------------------------- ------------ ---------- ---------------
0nxtnbrbf48rc select id+1 from karan                               0 FIRST_ROWS      1357081020

SQL> select id+1 from karan;

no rows selected

SQL> select sql_id, sql_text, child_number,optimizer_mode,plan_hash_value from v$sql where sql_text like 'select id+1 from kar%';

SQL_ID        SQL_TEXT                                 CHILD_NUMBER OPTIMIZER_ PLAN_HASH_VALUE
------------- ---------------------------------------- ------------ ---------- ---------------
0nxtnbrbf48rc select id+1 from karan                               0 FIRST_ROWS      1357081020