Bind Variables: performance booster

One fine morning, I got a request from my manager to drop some staging tables. I Started working on it and ended up quickly writing a dynamic query to drop the db Objects which he wanted, but unfortunately one my peers, who then would provide his code review approval interjected suggesting me to pass hard coded value, As it was a Dynamic code. I quickly followed his concerns maybe he wanted to safeguard from SQL Injection.

Whenever we encounter a normal business scenario that would require the use of dynamic query, we do not emphasize on ways to avoid SQL Injection . I encounter many scenarios in day to day coding where we avoid any such practice.

In this post i would first show how an Bind variables in Dynamic sql enhances the performance followed by a code example prone to sql injection and different ways to avoid it.

TABLE CREATION SCRIPT

create table ALL_CUSTOMERS
(
 id NUMBER not null,
 first_name VARCHAR2(100) not null,
 last_name VARCHAR2(100) not null,
 description VARCHAR2(100)
)
 
create index X_CUSTOMER_ID_NAME on ALL_CUSTOMERS (ID, FIRST_NAME);
----------------------------------------------------------------------
 
BEGIN
FOR i in 1..30000 LOOP
INSERT/*+APPEND*/ INTO ALL_CUSTOMERS 
 (ID, 
  FIRST_NAME, 
 LAST_NAME,
 DESCRIPTION)
 VALUES(customersequence.nextval,
 dbms_random.string('A',10),
 dbms_random.string('A',15),
   dbms_random.string('A',20));
END LOOP;
END;

NO BIND Variable

DECLARE
L_STR NUMBER;
 ALL_CUSTOMERS_REC ALL_CUSTOMERS%rowtype;
 BEGIN
 L_STR := dbms_utility.get_time;
 FOR i in 1 .. 30000 loop
 BEGIN
execute immediate
 'select * from ALL_CUSTOMERS where id = ' || i
 into ALL_CUSTOMERS_REC;
 exception
 when no_data_found then null;
 end;
 end loop;
 DBMS_OUTPUT.PUT_LINE(dbms_utility.get_time-L_STR);
 end;
 --------------------------------------------------------------------
 Time in h sec: 4646

Bind Variable
DECLARE
L_STR NUMBER;
 ALL_CUSTOMERS_REC ALL_CUSTOMERS%rowtype;
 BEGIN
 L_STR := dbms_utility.get_time;
 FOR i in 1 .. 30000 loop
 BEGIN
 
 EXECUTE IMMEDIATE
 'select * from ALL_CUSTOMERS where id = :i'
 into ALL_CUSTOMERS_REC
 using i;
 exception
 when no_data_found then null;
 end;
 
 end loop;
 DBMS_OUTPUT.PUT_LINE(dbms_utility.get_time-L_STR);
 end;
------------------------------------------------------------ 
Time in h second: 141

I need not say how drastically the performance is enhanced. Please mind bind variables are good against sql injection will talk about bind variables with sql injection in some other post.

Lets now come back to our original problem Passing Table_name in dynamic query, Let me jot down the code quickly for you.

CREATE OR REPLACE  FUNCTION Test_data ( P_TABLE_NAME in varchar2 ) return  SYS_REFCURSOR IS
P_out SYS_REFCURSOR;
V_SQL varchar2(1000);
 BEGIN
   V_SQL :=  'SELECT * FROM '|| P_TABLE_NAME;
   DBMS_OUTPUT.PUT_LINE(v_sql);
OPEN P_out for V_SQL;
return P_out;
END;

Lets try and Inject this SQL

INJECT SQL

SELECT * FROM dual where 1=2 union select name || ';' ||

spare4 from user$ where user# = 0;

This could be a potential risk as the invader will get a leverage to get the password and attack the system.

Please follow this link to find how password are stored in oracle: http://www.dba-oracle.com/t_password_storage.htm.

Is it not vulnerable. Lets see a injection of a function

CREATE OR REPLACE  FUNCTION attack
RETURN VARCHAR2
AUTHID CURRENT_USER
IS
 PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
 EXECUTE IMMEDIATE 'GRANT DBA TO HR';
 RETURN '1';
END;

SELECT * FROM dual where 'x' = hr.attack() - If we pass this bold text with the test_data input parameter it would grant a DBA role to a normal user to do any changes in our database opening new avenues of injection.

We certainly have several ways to safeguard our code. some of which are

1. Use static SQL – where possible

2. Use invoker rights

3. Use bind variables, if possible

4. Check that the schema exists • select 1 from all_users where username = :1 • dbms_assert.schema_name

5 Check that the object exists • select 1 from all_objects where owner = :1 and object_name = :2

dbms_assert.sql_object_name

For more on sql inject please follow -

https://www.ashishworld.com/post/sql_injection-bind-variables