Showing posts with label open. Show all posts
Showing posts with label open. Show all posts

May 29, 2013

Cursors

         Whenever a SQL statement is executed, Oracle automatically allocates a memory area (known as context area) in Oracle database PGA i.e. Process Global Area. This allocated memory space is the query work area which holds the query related information.This is know as cursors

OPEN stage

•PGA memory allocation for cursor processing (OPEN Cursor)
•Parsing of SELECT statement (Parse SQL)
•Variable binding (Bind SQL)
•SELECT Query execution (Execute Query)
•Move the record pointer to the first record

FETCH stage

The record, to which the record pointer points, is pulled from the result set. The record pointer moves only in the forward direction. The FETCH phase lives until the last record is reached.

CLOSE stage

After the last record of the result set is reached, cursor is closed, and allocated memory is flushed off and released back to SGA. Even if an open cursor is not closed, oracle automatically closes it after the execution of its parent block


Cursor FOR loops

Cursor FOR loops improvise upon the performance and code interactivity by their implicit actions

Parameterized Cursors

Parameterized cursors enables programmer to pass parameter to the cursors

Dynamic Cursor FOR Loops

FOR I IN (SELECT EMPLOYEE_NAME, JOB_ID,SALARY FROM EMPLOYEES WHERE DEPARTMENT_ID=10)

FOR UPDATE OF clause is used to lock a set of rows in a session. This concept can be used in explicit cursors to impose exclusive row level lock on all the rows contained by the cursor query result set. These rows will remain locked until the session issues ROLLBACK or COMMIT.

Oracle provides WHERE CURRENT OF clause to update or delete the rows which are locked by the FOR UPDATE OF cursor in the session

Mar 15, 2011

Impact of SQL Injection

   Retrieval of Crucial Data
   Data Manupulation
   Alter database State
   Revoke Database Service


Developers are ignorant that their above query can be misused to an extent that it can list login names and passwords, which is relevant and crucial information for an organization. An invader can give input as
Code :

p'or upper(s_empname) like'%
p'or'p'='p


Code sql:

SELECT USERNAME, PASSWORD FROM USERS
WHERE USERNAME = ''
OR 1=1

SQL Injection: Example

A procedure P_GET_SAL was created to get the salary of input Employee Id.

Code sql:

CREATE OR REPLACE PROCEDURE P_GET_SAL  (P_ENAME VARCHAR2 DEFAULT NULL)
AS
CUR SYS_REFCURSOR;
V_ENAME VARCHAR2(100);
V_SAL NUMBER;
BEGIN
  V_STMT := 'SELECT ENAME, SALARY FROM EMPLOYEE  WHERE ENAME = '''|| P_ENAME || '''';
  DBMS_OUTPUT.PUT_LINE(V_STMT); 
  OPEN CUR FOR V_STMT;
  LOOP
    FETCH CUR INTO V_ENAME, V_SAL;
    EXIT WHEN CUR%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Employee : '||V_ENAME||' draws '||TO_CHAR(V_SAL));
  END LOOP;
  CLOSE CUR;
END;



Code sql:

SQL> EXEC P_GET_SAL(‘KING’);
Employee KING draws 4500

PL/SQL PROCEDURE successfully completed.


Code sql:

SQL> EXEC P_GET_SAL('KING'' UNION SELECT ENAME, SALARY FROM EMPLOYEE WHERE 1=1');
Employee KING draws 4500
Employee ALLEN draws 1200
Employee MIKE draws 3400
Employee KATE draws 2300
Employee PAUL draws 6400
Employee TOMY draws 2700
Employee JENNY draws 6200
Employee JAKES draws 4600

PL/SQL PROCEDURE successfully completed.



Several strategies can be adopted to safeguard the SQL code and eradicate the impacts of SQL injection in applications. Some of them are listed below.

1. Use of Static SQL
2. Using Invoker’s rights
3. Use of Dynamic SQL with bind arguments
4. Validate and sanitize input using DBMS_ASSERT


-------