SQL Injection:- (safe guarding PL/SQL code from SQL injection)
·
Reducing the surface attack is a preferable step to safe guard
the PL/SQL code from SQL injection.
·
We will reduce the attacking surface in two ways
1. Revoking
unnecessary, unintended and excess privileges from user.
2. Defining
a PL/SQL program with invoker write (instead of definer write).
·
Grant select on stab to u2;
·
Select * from u1.stab;
·
Insert into u1.stab values()
//invalid
Defining a Program with Definer Write:-
Create or replace procedure p is v number(10):=10;
Begin
Insert into u1.stab values(10);
Select sno into v from u1.stab where sno=v;
End p;
·
Grant execute on p to u2;
·
Execute u1.p
·
Select * from u1.stab;
Defining a Program With Invoker Write:-
Create or replace
procedure p authid current user is v number(10):=10;
Begin
Insert into u1.stab
values(v);
Select sno into v from
u1.stab where sno=v;
End p;
·
Grant execute on p to u2;
·
Exec u1.p
//insufficient privilege
·
To deduce the SQL injection (or) to immunize the PL/SQL code
from SQL injection better avoid dynamic SQL statements.
·
Following is an eg of which specifies need to avoid dynamic SQL
statements.
Create or
replace procedure p(X-name varchar2) is
Vrec
sys_refcursor;
V_name
varchar2(10);
V_deptno
number(5);
Begin
Open vrec
for ‘select ename,deptno from emp where enmae=’||x_name;
// here ‘vrec’ as a variable
Fetch vrec
into v_name, v_deptno;
DOPL(v_name||’
‘||v_deptno);
Exit when
vrec%not found;
End loop;
Close vrec;
End;
Calling Environment:-
SQL> exec p(‘ ‘ ‘king’ ‘ ‘);
King 10
Exec p(‘null union
select ename, sal from emp’);
·
Here we have a chance for attacking malicious data so, to
restrict the SQL injection convert the dynamic SQL into static as shown in
following eg.
Create or replace
procedure p(X-name varchar2) is
Vrec sys_refcursor;
V_name varchar2(10);
V_deptno number(5);
Begin
Open vrec for select ename, deptno from
emp where ename=x_name;
Fetch vrec into v_name, v_deptno;
DOPL(v_name||’ ‘||v_deptno);
Exit when vrec%not found;
End loop;
Close vrec;
End;
·
Exec p(‘king’);
King 10
·
Exec p(‘null union select ename,sal from emp); (here getting
unintended data is not possible).
Note:-
·
When we know all the SQl identifiers (column, table names)
before of fraiming a statement, in that case only it is possible to convert the
dynamic SQL statements into static SQl which means it is not possible to
convert all the dynamic SQL statements static if this is the case then
mien/mise the attacking of SQL injection by using “bind argument” as shown in
the following snippet (or) eg.
·
In addition with protectiong the PL/SQL code from SQL injection
it also possible to enhance the performance immeyely(more effectively).
·
Using bind arguments in dynamic SQL will avoid hard
parsing(buffer myss) and promote soft parsing(buffer hit).
·
For the above eg just change
Begin
Open vrec for ‘select enaame, deptno from emp where ename=:’
using x_name;
// remaining structure is same.
·
Exec p(‘king’);
·
Exec p(‘null union select ename,sal from emp’);
·
Finally we will reduce the SQL injection by sanitizing the user
inputs with the help of an APL i.e DBMS_assert(package).
·
While tunning the PL/SQL code avoid implicit conversion which ia
a burden to the ename as server as such in the following.
Eg:-
Declare
Stime number:=0;
Etime number:=0;
V varchar2(10);
Begin
Stime:=dbms_utility.get_time;
For I in 1..1000 loop
V:=I;
End loop;
Etime:=dbms_utility.get_time;
DOPL(edate_sdate);
End;
Declare
Stime number:=0;
Etime number:=0;
V number(5);
Begin
Stime:=dbms_utility.get_time;
For I in 1..1000 loop
V:=i;
End loop;
Ctime:=dbms_utility.get_time;
DOPL(edate-sdate);
end;
ursor eg:-
declare
cursor c is select *
from emp;
vrow c%rowtype;
begin
open c;
Fetch c into
vrow;
DOPL(vrow.ename);
If c%notfound
then //(1)
Exit;
End if;
End loop;
Close c;
End;
In case of (1) we can
write
If
c%rowcount=3 then
Exit;
End if;
·
If you write like this we can stop the program after 3rd
record. In this case for loop is not prefereable, simple loop is prefereable.
Paramatarised Cursor Eg:-
Declare
Cursor c(x number) is
select * from emp where deptno:=x;
Vrow emp%rowtype;
Vno number(5):=&n;
Vnol
number(5):=&m;
Begin
Open c(vno);
Fetch c into vrow;
DOPL(vrow.ename)
Exit when c%not found;
End loop;
Close c;
Open c(vnol);
Fetch c into vrow;
DOPL(vrow.ename);
Exit when c%not found;
End loop;
Close c;
·
Before ‘open c’:
% is open
-F
% found
–E
% notfound
-E
%
rowcount -E
·
After ‘open c’:
% is open
-T
%
found -T
% notfound
-F
% rowcount
-0
·
After fetch: (1st fetch)
% is open
-T
% found
-1
% notfound
-F
% rowcount
-1
·
After close c:
% is open
-F
% found -E
% notfound
-E
% rowcount
-E
We can use cursor
variable as a parameter in stored procedures
·
Create or replace function f return sys_refcursor is vrec
sys_refcursor;
Begin
Open vrec
for select * from emp;
Return
vrec; //return statement
End f;
·
We can also use sys_refcursor datatype for function return value
(or) using cursor variable as a return value in function.
·
Calling:-
Var refvar
refcursor;
Exec
:refvar:=f;
Print
refvar;
·
Create or replace function f(vrec out sys_refcursor) return
Boolean
Begin
Open vrec for select * from emp;
Close vrec;
Return true;
End;
Declare
Vrec1 sys_refcursor;
V Boolean;
Vrow emp%rowtype;
Begin
V:=f(vrec1);
Fetch vrec1 into vrow;
DOPL(vrow.ename);
Exit when vrec1%not found;
End loop;
End;
·
Create or replace function f(vrec out sys_refcursor) return
Boolean is
Vrow emp%rowtype;
Begin
Open vrec for select * from emp;
Fetch vrec into vrow;
Return true;
End f;
Declare
Vrec1 sys_refcursor;
V Boolean;
Vrow emp%rowtype;
Begin
V:=f(vrec1);
Fetch vrec1 into vrow;
DOPL(vrow.ename);
Exit when vrec1%not found;
End loop;
End;
Output:- It will be 13 records:
Black
Clak
·
Here points the second record in emp.
·
Create or replace function f return sys_refcursor is
Vrec sys_refcursor;
Vrec1 sys_refcursor;
Begin
Open vrec for select * from emp;
Vrec1:=vrec;
Close vrec; //here ‘close vrec’ closes both the
cursors.
Return vrec1;
End f;
Declare
Vrec2 sys_refcursor;
Vrow emp%rowtype;
Begin
Vrec2:=f;
Fetch vrec2 into vrow;
DOPL(vrow.ename);
Exit when vrec2%notfound;
End loop;
End;
Analyzing PL/SQL Code:-
·
By using ‘format-call-stack” procedure we will sequentially
trace out the program calls so , to analyse the coading as shown in the
following eg. (or) snippet.
·
Create or replace procedure p is
Begin
DOPL(‘dbms_utility.format_call_stack’);
End;
Create or replace
procedure p1 is
Begin
P;
End p1;
Create or replace
procedure p2 is
Begin
P1;
End p2;
Procedure created.
Begin
P2;
End;
PL/SQL call stack
Lienumber
|
Object
|
3
|
Procedure APPs-p
|
3
|
Procedure
|
3
|
Procedure apps.p2
|
2
|
Anonymous block
|
Atomics of Program (or) PL/SQL Program:-
Identifiers->literals->delimiters->comments
Identifiers are also derived as:
Constants, variables, packages, cursors, exceptions.
·
If program is length at that time we have to know where calling
happened to know the line numbers for easy identification we use above package.
/*
…….
………
*/
Note:- If you want to execute
to multiple lines we give like this.
/*
……
……..
/*
…..
……
*/
…….
….....
*/
Note:- We can’t nest comment in comment.
Instead of Trigger:-
·
This trigger is for views, to perform DML operations on complex
views will not be allowed directly, but by using instead of trigger it is
passible.
·
This trigger is always a rowlevel trigger even if you won’t
mention the “for each row” clause.
·
Defining instead of trigger on tables ends with an error.
·
Create table vtabl(sno number(5));
·
Create table vtab2(loc varchar2(10);
·
Create view comview as select * from vtab1, vtab2;
·
Insert into comview values(10,’hyd’);
Error:
Can not modify a
column which maps to a non key preserved data.
Create or replace
triggr comtrig
Instead of insert on
comview;
Begin
Insert into vtab1
values(:new.sno);
Insert into vtab2
values(:new.loc);
End;
Insert into comview
values(10,’hyd’);//here insertion is successfully happening on view.
Note:- DML operation can’t perform on complex views. But as in the
above case trigger is inserting the values before insertion.
Mutating Error:-
·
We get this error when a row level trigger attempts to read or
write the table from which it was raised.
·
You won’t get this error in statement level.
·
Create or replace trigger muttrigBefore update on vtab1
For each row
Declare
Vno number95);
Begin
Select sno into vno from vtab1 where
sno=30;
End;
- · Update vtab1 set sno=50;
Error: table apps.vtab1 is mutating,
trigger/function may not see it.
To Avoid
Mutating Errors We Use “Pragma Atonomous Transaction” (or) we can also use
“statement level trigger”.
No comments:
Post a Comment