Without Bind Variables, Your Code is Less Secure

Not something entirely learned new today - but rather a revisited "something I learned".

When you don't use bind variables revolves around security, specifically the risk of SQL injection.
SQL injection occurs when an application program accepts arbitrary SQL from an untrusted source (think "end user"), blindly adds it to the application's SQL, and executes it. It would be similar to the operating system's taking a couple of lines of C code from a user, compiling it on the fly, and just executing it in the operating system kernel. It would stand a good chance of crashing something.

To understand how it works, suppose that we have an application that asks a user for a username and password. We execute this query using with and without Bind variables.

scott@10GR2> create table t(userid varchar2(10),passwd varchar2(100));

Table created.

scott@10GR2> insert into t values ('tom','top_secret_password');

1 row created.

scott@10GR2> commit;

Commit complete.

scott@10GR2> Create or replace procedure without_bind (p_username in varchar2,p_passwd in varchar2)
  2  as
  3     l_count number := 0;
  4     v_sql varchar2(500);
  5  begin
  6     v_sql := 'select count(*)
  7     from t
  8     where userid = '''||p_username ||''' AND  passwd = '|| p_passwd ;
  9
 10     execute immediate v_sql into l_count;
 11
 12     if l_count > 0 then
 13             dbms_output.put_line('Thanks for Correct user name and password');
 14     else
 15             dbms_output.put_line('Incorrect Credentials');
 16     end if;
 17
 18  end without_bind;
 19  /

Procedure created.

scott@10GR2> Create or replace procedure using_bind (p_username in varchar2,p_passwd in varchar2)
  2  as
  3     l_count number := 0;
  4  begin
  5
  6     execute immediate ' select count(*) from t where userid = :x and passwd = :y ' into l_count using p_username, p_passwd ;
  7
  8     if l_count > 0 then
  9             dbms_output.put_line('Thanks for Correct user name and password');
 10     else
 11             dbms_output.put_line('Incorrect Credentials');
 12     end if;
 13
 14  end using_bind;
 15  /

Procedure created.

scott@10GR2> exec without_bind('tom','''i_dont_know'' or 1 = 1');
Thanks for Correct user name and password
 
Note the password we just used. It incorporates a little SQL, doesn't it? Since we are just gluing strings together, not binding, the end user can actually type in arbitrary SQL and have it executed! Our application takes this string now and continues on.

Look at that. Apparently, the password 'i_dont_know' or 1 = 1  is our password. But if we use bind variables instead and accept the exact input from the end user, we see this:

scott@10GR2> exec using_bind('tom','''i_dont_know'' or 1 = 1');
Incorrect Credentials 

Bind variables add security to your applications.

Export and Import (DataPump) Over the Network - Schema Refresh.

Here's how to set up an Oracle Data Pump Import, using a network-based approach 

• Set up a remote link from a satellite database (database 2) to the master database (database 1).

• Create the import as a SQL script to run weekly from the satellite database to import selected tables from the source system, filtering and transforming as needed   

 Creating user name TEST_DPUMP to populate data from Master Database ( longdesc ).
 
system@ORCL> create user test_dpump identified by test_dpump;

User created.

system@ORCL> grant connect ,resource,dba to test_dpump;

Grant succeeded.

system@ORCL>

test_dpump@ORCL> create database link test_dpump_to_longdes connect to LONGDESC identified by password using 'orcl';

Database link created.

test_dpump@ORCL> select * from dual@test_dpump_to_longdes;

D
-
X

test_dpump@ORCL>



longdesc@ORCL> select count(*) from user_tables;

  COUNT(*)
----------
       136


  
Script for Schema Refresh from Master Database.

DECLARE
    ind NUMBER;                 -- Loop index number
    job_handle number;            -- Data Pump job handle
    percent_done number := 0;    -- Percentage of job complete
    job_state  varchar2(100) := 'UNDEFINED';    --    Keeps track of job state
    le ku$_LogEntry;            -- work-in-progress and error messages
    js ku$_JobStatus;           -- Job status from get_status
    jd ku$_JobDesc;             -- Job description from get_status
    sts ku$_Status;             -- Status object returned by get_status
BEGIN
   
    -- Using database link establish connection to pull data from source to destination database.  
    job_handle := dbms_datapump.open (    operation=>'IMPORT',
                                        job_mode=>'TABLE',
                                        remote_link=>'TEST_DPUMP_TO_LONGDES',
                                        job_name=> 'TEST_DPUMP_REFRESH');
   
    -- Setting the degree of parallelization to 1 for this Refresh operation.  
    dbms_datapump.set_parallel ( handle =>job_handle,
                                 degree => 1 );
   
    --- Remap Schema objects from source schema Named LONGDESC to destination schema TEST_DPUMP.
    dbms_datapump.metadata_remap ( handle => job_handle,name => 'REMAP_SCHEMA',old_value => 'LONGDESC',value => 'TEST_DPUMP' );
   
    -- If a table already exists in the destination schema, don't override (this is default behavior.)
    dbms_datapump.set_parameter( handle => job_handle,name => 'TABLE_EXISTS_ACTION',value => 'SKIP');
   
    -- Use statistics (rather than blocks) to estimate time.
    dbms_datapump.set_parameter( handle => job_handle, name => 'ESTIMATE', value => 'STATISTICS' );
   
    -- Start the job. An exception is returned if something is not set up properly.
    dbms_datapump.start_job (handle => job_handle);
   
    -- The import job should now be running. This loop monitors the job until it completes, displaying progress information if there are problems.

        while ( job_state <> 'COMPLETED' and job_state <> 'STOPPED')
        loop
            dbms_datapump.get_status(  job_handle,
                                       DBMS_DATAPUMP.ku$_status_job_error +
                                       DBMS_DATAPUMP.ku$_status_job_status +
                                       DBMS_DATAPUMP.ku$_status_wip ,
                                       -1,
                                       job_state ,
                                       sts );
          
            js := sts.job_status;
          
    -- As the percentage-complete changes in this loop, the new value displays.      
            if js.percent_done != percent_done then
                DBMS_OUTPUT.PUT_LINE('*** Job percent done = ' || to_char(js.percent_done));
                percent_done := js.percent_done;
            end if;  
          
            -- Displays any work-in-progress or error messages received for the job.
            if (bitand(sts.mask,dbms_datapump.ku$_status_wip) != 0) then
                le := sts.wip;
            else
                if (bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0) then
                le := sts.error;
                else
                    le := null;
                end if;
            end if;
          
            if le is not null then
                ind := le.FIRST;
                while ind is not null
                loop
                    DBMS_OUTPUT.PUT_LINE(le(ind).LogText);
                    ind := le.NEXT(ind);
                end loop;
            end if;  
          
        end loop; --- End of While loop.
      
        -- When the job finishes, display status before detaching from job.
        DBMS_OUTPUT.PUT_LINE('Job has completed');
        DBMS_OUTPUT.PUT_LINE('Final job state = ' || job_state);
        DBMS_DATAPUMP.DETACH(job_handle);      
END;
/





test_dpump@ORCL> select count(*) from user_tables;

  COUNT(*)
----------
       136




Oracle Data Pump imports are anywhere from 15 to 40 times as fast as with traditional import, even with parallelism set to 1, in part because of Oracle Data Pump's use of direct path (when possible).
 

dbms_utility.FORMAT_ERROR_BACKTRACE - 10g New Features

When an exception is raised, one of the most important piece of information that a developer would like to know is the line of code that raised that exception, prior to Oracle 10g one way to obtain this information by allowing the exception to go unhandled.

test@9iR2> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for 32-bit Windows: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

test@9iR2> create or replace procedure p1 as
  2  begin
  3     dbms_output.put_line ('raising error at p1');
  4     raise no_data_found;
  5  end;
  6  /

Procedure created.

test@9iR2> create or replace procedure p2 as
  2  begin
  3     p1;
  4  end;
  5  /

Procedure created.

test@9iR2> create or replace procedure p3 as
  2  begin
  3     p2;
  4   end;
  5  /

Procedure created.

test@9iR2> exec p3;
raising error at p1
BEGIN p3; END;

*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "TEST.P1", line 4
ORA-06512: at "TEST.P2", line 3
ORA-06512: at "TEST.P3", line 3
ORA-06512: at line 1

Now we have the line number, we can zoom right into the code and fix it, on the other hand we got this information by letting the Exception going unhandled. How ever in many application we work to avoid unhandled exception.

Let's see what happen when exception handled in procedure P3.

test@9iR2> create or replace procedure p3 as
  2  begin
  3     p2;
  4  exception
  5             when others then
  6             dbms_output.put_line (' calling format error stack from P3 ');
  7             dbms_output.put_line (dbms_utility.format_error_stack);
  8   end;
  9  /

Procedure created.

Notice that I call dbms_utility.format_error_stack, it returns the full error message.
test@PWTK52> exec p3;
raising error at p1
 calling format error stack from P3
ORA-01403: no data found

PL/SQL procedure successfully completed.

dbms_utility.format_error_stack - does not show the full error stack with line numbers.



In Oracle database 10g, Oracle added format_error_backtrace which can and should be called from exception handler. It displays the call stack at the point where exception was raised.
 Let's see what happen when exception handled using dbms_utility.format_error_stack procedure P3.

scott@10gR2> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

scott@10gR2> create or replace procedure p3 as
  2  begin
  3     p2;
  4  exception
  5             when others then
  6             dbms_output.put_line (' calling format error stack from P3 ');
  7             dbms_output.put_line (dbms_utility.FORMAT_ERROR_BACKTRACE);
  8  end;
  9  /

Procedure created.





And now when I run the procedure P3, I will see the following output.


scott@10gR2> exec p3;
raising error at p1
 calling format error stack from P3
ORA-06512: at "scott.P1", line 4
ORA-06512: at "scott.P2", line 3
ORA-06512: at "scott.P3", line 3

PL/SQL procedure successfully completed.


The information that had previously been available only through an unhandled exception is now retrievable from within the PL/SQL code.

How PL/SQL Optimizes Your Programs

Today, I learned a new 10g Release 2 and above 'trick' that I wasn't aware of. This is pretty cool

You can choose the level of optimization through the  plsql_optimization_level setting:

2  Most aggressive, maximum possible code transformations, biggest impact on compile time. [default]
1  Smaller scale change, less impact on compile times
0  Pre-10g compilation without optimization

scott@10GR2> show parameter plsql_optim;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
plsql_optimize_level                 integer     2

scott@10GR2> alter session set plsql_optimize_level=0;

Session altered.

scott@10GR2> create or replace procedure p
  2  as
  3     l_start_time number ;
  4     a positiven := 1;
  5     b positiven := 1;
  6     c positiven := 1;
  7     d positiven := 1;
  8  begin
  9     l_start_time := dbms_utility.get_cpu_time;
 10     for i in 1..1000000
 11     loop
 12             a := 42;
 13             b := a*2;
 14             c := a * 5;
 15             d := a+b+c;
 16     end loop;
 17     dbms_output.put_line ( dbms_utility.get_cpu_time - l_start_time);
 18  end p;
 19  /

Procedure created.

scott@10GR2> begin
  2     for i in 1..5
  3     loop
  4             p;
  5     end loop;
  6  end;
  7  /
39
41
39
39
41

PL/SQL procedure successfully completed.

scott@10GR2> alter session set plsql_optimize_level=1;

Session altered.

scott@10GR2> alter procedure p compile;

Procedure altered.

scott@10GR2> begin
  2     for i in 1..5
  3     loop
  4             p;
  5     end loop;
  6  end;
  7  /
9
8
9
8
8

PL/SQL procedure successfully completed.

scott@10GR2> alter session set plsql_optimize_level=2;

Session altered.

scott@10GR2> alter procedure p compile;

Procedure altered.

scott@10GR2> begin
  2     for i in 1..5
  3     loop
  4             p;
  5     end loop;
  6  end;
  7  /
10
7
10
9
8

PL/SQL procedure successfully completed.

scott@10GR2> SELECT name, type, plsql_optimize_level
  2  FROM user_plsql_object_settings
  3  WHERE name = 'P'
  4  /

NAME                           TYPE         PLSQL_OPTIMIZE_LEVEL
------------------------------ ------------ --------------------
P                              PROCEDURE                       2

Dependency Chain (PKG vs Proc)

Not something entirely learned new today - but rather a revisited "something I learned".

Nice quote from Oracle product documentation about dependency chain
If the body of the referenced package is replaced, then the dependent procedure is not affected. However, if the specification of the referenced package is replaced, then the dependent procedure is invalidated. This is a mechanism for minimizing dependencies among procedures and referenced objects by using packages.


If procedure A calls procedure B and B is "changed", then A is directly affected and must be recompiled.  There is no "auto recompile A when B changes" option -- although A will recompile itself automatically the next time it is run.
    On the other hand, If I create a package PKG_A with a procedure A and a package PKG_B with a procedure B then PKG_A will be dependent on PKG_B's specification.  I can compile and recompile PKG_B's Body as often as I like without affecting PKG_A's state.  As long as PKG_B's specification or interface does not change -- dependent objects are not affected by the recompilation of the body.

test@10gR2> create or replace procedure B
  2  as
  3  begin
  4     null;
  5  end;
  6  /

Procedure created.

test@10gR2> create or replace procedure A
  2  as
  3  begin
  4     b;
  5  end;
  6  /

Procedure created.

So procedure A calls B.  One of the downsides to procedures, in addition to the direct dependency, is that you have to create them in the "right" order if you want everything valid in the database after an install.  I had to create B before A.  Packages don't have this nuance either -- as shown below

test@10gR2> create or replace package pkg_a as
  2     procedure a;
  3  end pkg_a;
  4  /

Package created.

test@10gR2> create or replace package pkg_b as
  2     procedure b;
  3  end pkg_b;
  4  /

Package created.

Here i have created the package specification, they are not dependent on other and can be created in any order.

test@10gR2> create or replace package body pkg_a as
  2     procedure a
  3     is
  4     begin
  5             pkg_b.b;
  6     end a;
  7  end pkg_a;
  8  /

Package body created.

test@10gR2> create or replace package body pkg_b as
  2     procedure b
  3     is
  4     begin
  5             null;
  6     end b;
  7  end pkg_b;
  8  /

Package body created.

Then i created the bodied - I can create bodies in any order after the specification (In general) -  PKG_A is dependent on Pkg_B specification - not is body. so we don't have the ordering problem.

test@10gR2> select object_type,object_name,status
  2  from user_objects
  3  where status ='INVALID';

no rows selected

nothing is Invalid.

test@10gR2> alter procedure b compile;

Procedure altered.

test@10gR2> select object_type,object_name,status
  2  from user_objects
  3  where status ='INVALID';

OBJECT_TYPE         OBJECT_NAME                    STATUS
------------------- ------------------------------ -------
PROCEDURE           A                              INVALID

Standalone procedure A goes invalid, B was changed and A needs to be recompiled.

test@10gR2> alter package pkg_b compile body;

Package body altered.

test@10gR2> select object_type,object_name,status
  2  from user_objects
  3  where status ='INVALID';

OBJECT_TYPE         OBJECT_NAME                    STATUS
------------------- ------------------------------ -------
PROCEDURE           A                              INVALID

Pkg_A never goes invalid - As long as spec does not change, Pkg_A remains valid.

Don't use standalone procedures!  Use packages.  Packages break the dependency chain.

Script to compile Invalid objects - Tracking dependencies

begin
  FOR r IN (SELECT 'ALTER ' || decode(a.object_type,   'PACKAGE BODY',   'PACKAGE',   'TYPE BODY',   'TYPE', a.object_type) || ' ' || a.object_name || decode(a.object_type,   'JAVA CLASS',   ' RESOLVE',   ' COMPILE') || decode(a.object_type,   'PACKAGE BODY',   ' BODY',   'TYPE BODY',   'BODY') text
          FROM user_objects a,
              (SELECT MAX(LEVEL) dlevel,
               object_id
             FROM public_dependency START WITH object_id IN
              (SELECT object_id
               FROM user_objects
               WHERE status = 'INVALID'
                  and    object_type <> 'SYNONYM' )
            CONNECT BY object_id = PRIOR referenced_object_id
             GROUP BY object_id)b
          WHERE a.object_id = b.object_id(+)
           AND a.status = 'INVALID'
           and    object_type <> 'SYNONYM'
           ORDER BY b.dlevel DESC,a.object_name ASC )
  loop
  begin
    execute immediate r.text;
    exception
      when others then
        dbms_output.put_line (' Exception '||sqlcode||' - '||sqlerrm ||' - ' ||r.text);
  end;   
  end loop;
end;


rajesh@10GR2> begin
  2    FOR r IN (SELECT 'ALTER ' || decode(a.object_type,   'PACKAGE BODY',   'PACKAGE',   'TYPE BODY',   'TYPE', a.object_type) || ' ' || a.object_name || decode(a.object_type,   'JAVA CLASS',   ' RESOLVE',   ' COMPILE') || decode(a.object_type,   'PACKAGE BODY',   ' BODY',   'TYPE BODY',   'BODY') text
  3            FROM user_objects a,
  4                (SELECT MAX(LEVEL) dlevel,
  5                 object_id
  6               FROM public_dependency START WITH object_id IN
  7                (SELECT object_id
  8                 FROM user_objects
  9                 WHERE status = 'INVALID'
 10                    and    object_type <> 'SYNONYM' )
 11              CONNECT BY object_id = PRIOR referenced_object_id
 12               GROUP BY object_id)b
 13            WHERE a.object_id = b.object_id(+)
 14             AND a.status = 'INVALID'
 15             and    object_type <> 'SYNONYM'
 16             ORDER BY b.dlevel DESC,a.object_name ASC )
 17    loop
 18    begin
 19      execute immediate r.text;
 20      exception
 21        when others then
 22          dbms_output.put_line (' Exception '||sqlcode||' - '||sqlerrm ||' - ' ||r.text);
 23    end;
 24    end loop;
 25  end;
 26  /
 Exception -24344 - ORA-24344: success with compilation error - ALTER FUNCTION EMP_FUNC COMPILE
 Exception -24344 - ORA-24344: success with compilation error - ALTER FUNCTION SHOW_DATA COMPILE

PL/SQL procedure successfully completed.

Session - Open Cursors

So, something else I learned recently...its about Open cursors in Session.

V$open_cursor – Represents a set of cached cursors that server has.  Here is a quick test to demonstrate that.

scott@10GR2> SELECT *
  2  FROM v$open_cursor
  3  WHERE sql_text LIKE '%CURSOR_TEST%';

no rows selected

scott@10GR2> declare
  2  cursor c1 is select * from dual cursor_test;
  3  l_rec c1%rowtype;
  4  begin
  5     open c1;
  6     fetch c1 into l_rec;
  7     close c1;
  8  end;
  9  /

PL/SQL procedure successfully completed.

scott@10GR2> SELECT *
  2  FROM v$open_cursor
  3  WHERE sql_text LIKE '%CURSOR_TEST%'
  4  /

no rows selected

scott@10GR2> create or replace procedure test_proc as
  2  cursor c1 is select * from dual cursor_test;
  3  l_rec c1%rowtype;
  4  begin
  5     open c1;
  6     fetch c1 into l_rec;
  7     close c1;
  8  end test_proc;
  9  /

Procedure created.

scott@10GR2> exec test_proc;

PL/SQL procedure successfully completed.

scott@10GR2> SELECT sid,sql_id,sql_text
  2  FROM v$open_cursor
  3  WHERE sql_text LIKE '%CURSOR_TEST%';

 SID         SQL_ID          SQL_TEXT
 --------   ----------         -----------------------------
 159         9y8yp8fh22fxz   SELECT * FROM DUAL CURSOR_TEST

Even I closed the cursor Explicitly it’s still shown up in V$open_Cursor (due to set of cached cursors in server).

To get the current open cursors ( Cursor leaking ) across session

scott@10GR2> select sum(a.value), b.name,a.sid
  2  from v$sesstat a, v$statname b
  3  where a.statistic# = b.statistic#
  4  and b.name = 'opened cursors current'
  5  group by b.name,a.sid
  6  having  sum(a.value) > 0
  7  /

SUM(A.VALUE) NAME                                  SID
------------ ------------------------------ ----------
          32 opened cursors current                140
           5 opened cursors current                138
           5 opened cursors current                132
           4 opened cursors current                162
          81 opened cursors current                154
          29 opened cursors current                161
          11 opened cursors current                134
          23 opened cursors current                136
          55 opened cursors current                144
          25 opened cursors current                149

10 rows selected.

open_cursors - its purely related to Session, say if you set Open_cursor 1000, so there could be 10,000 of them if you have 10 sessions.