Pivot , Unpivot Queries

In the FOR IN clause of PIVOT keyword, we cannot use column names , can only use values 

ORA-56901 : non -constant expression is not allowed in pivot|unpivot values 

 In PIVOT keyword, it is required to provide an aggregate function like COUNT, SUM, AVG,MAX,MIN etc 

ORA-56902 : expect aggregate functions inside pivot operation  

In PIVOT keyword, instead of aggregate function window keyword cannot be used 

ORA-30483 : window functions are not allowed here

Query to Pivot Rows to columns even if aggregate function is not be used (use Max/min with row_number)

SELECT * FROM (
     SELECT column_name, row_number() over(partition by table_name order by table_name) rr FROM user_tab_columns WHERE table_name = 'EMPLOYEES' ) pivot (MAX(column_name) FOR  rr IN (1 C_FIRST, 2 AS C_SECOND, 3 C_THIRD, 4 C_FOURTH, 5 C_FIVE, 6 C_SIX, 7 C_SEV, 8 C_EIGHT, 9 C_NINE, 100 C_HUND));


In HR Schema 

SELECT * FROM ( SELECT column_name,TABLE_NAME,column_id FROM user_tab_columns WHERE table_name ='EMPLOYEES' ) unpivot (column_value FOR column_name IN (table_name,column_name,column_id));

 ORA-01790 expression must have same datatype as corresponding expression 

In unpivot function FOR IN clause data-type with same columns are only allowed . In the above query table_name,column_name are varchar2 but column_id is number .So this results in the above error


SELECT * FROM ( SELECT column_name,TABLE_NAME,column_id FROM user_tab_columns WHERE table_name = 'EMPLOYEES' ) unpivot (column_value FOR column_name IN (table_name,column_name));

SELECT * FROM ( SELECT column_name,table_name,column_id FROM user_tab_columns WHERE table_name = 'EMPLOYEES' ) unpivot (column_value FOR column_name IN (column_name));


SELECT * FROM ( SELECT column_name,TABLE_NAME FROM user_tab_columns WHERE table_name = 'EMPLOYEES' ) unpivot (column_value FOR column_name IN (table_name,column_name));

Try this query for getting more detail about pivot function

SELECT * FROM ( SELECT column_name, row_number() over(partition by table_name order by table_name) rr FROM user_tab_columns WHERE table_name = 'EMPLOYEES' ) pivot (MAX(rr) FOR  rr IN (1 C_FIRST, 2 AS C_SECOND, 3 C_THIRD, 4 C_FOURTH, 5 C_FIVE, 6 C_SIX, 7 C_SEV, 8 C_EIGHT, 9 C_NINE, 100 C_HUND));
    
 SELECT * FROM ( SELECT column_name, row_number() over(partition by table_name order by table_name) rr FROM user_tab_columns WHERE table_name = 'EMPLOYEES' ) pivot (MAX(column_name) FOR  column_name IN (1 C_FIRST, 2 AS C_SECOND, 3 C_THIRD, 4 C_FOURTH, 5 C_FIVE, 6 C_SIX, 7 C_SEV, 8 C_EIGHT, 9 C_NINE, 100 C_HUND));

If there are duplicate records in a query then usage of pivot will removes the duplicate records .For example refer below query


SELECT * FROM (     SELECT column_name, row_number() over(partition by table_name order by table_name) rr FROM user_tab_columns WHERE table_name = 'EMPLOYEES'
     union all
      SELECT column_name, row_number() over(partition by table_name order by table_name) rr FROM user_tab_columns WHERE table_name = 'EMPLOYEES'   ) pivot (MAX(column_name) FOR  rr IN (1 C_FIRST, 2 AS C_SECOND, 3 C_THIRD, 4 C_FOURTH, 5 C_FIVE, 6 C_SIX, 7 C_SEV, 8 C_EIGHT, 9 C_NINE, 100 C_HUND));



 For more examples
 

SQL Injection Part 2

Part 1

---------------------------------------------------------------------------------------------------  
Inband: Retrieve the results of the SQL Injection in the same input (e.g. in the browser). Data can be display in the normal output or in an error message.
Most common techniques for Inband  are
* UNION based attacks
* Error Based

Now we must find out how many columns are used in the first SELECT statement. The most common techniques are the usage of  ORDER BY  or adding NULL values to the second query.

 SELECT * FROM table 
UNION 
SELECT null,null FROM table  

SELECT * FROM table ORDER BY 8 

Out-of-Band: A different channel (e.g. HTTP, DNS) is used to transfer the data from the SQL query. If this is working it is the easiest way to retrieve a large amount of data from the database

Blind: Different timings / results are used to retrieve data from the database.
Oracle offers 2 possibilities to run blind injection. • DECODE • CASE

--------

To get how may records are inserted after import

select table_name, num_rows, blocks, avg_row_len from dba_tables where table_name='EMP';

select table_name, monitoring from dba_tables where owner='SCOTT'  ORDER  BY  2;

Insert into scott.EMP   (EMPNO, ENAME, JOB, MGR, HIREDATE,SAL, COMM, DEPTNO) Values
   (7999, 'SMITH', 'CLERK', 7902, TO_DATE('12/17/1980 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),  800, NULL, 20);
   
COMMIT;

exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();


SELECT * FROM SYS.DBA_TAB_MODIFICATIONS where table_owner='SCOTT' ;

Interanl execution during Disable versioning a table

Step 1:
SELECT 1  FROM DUAL WHERE EXISTS (SELECT 1 FROM  scott.experiment_lt   WHERE VERSION IN (SELECT VERSION FROM wmsys.wm$version_hierarchy_table 
WHERE workspace != 'LIVE'))
                       
Step 2:       
DELETE FROM experiment_lt  WHERE nextver = ',0,' OR nextver IN ( SELECT next_vers FROM wmsys.wm$nextver_table
WHERE VERSION IN (SELECT VERSION FROM wmsys.wm$version_hierarchy_table  WHERE workspace = 'LIVE')) OR delstatus < 0
           
Step 3:
ALTER TABLE  scott.experiment_lt   ADD CONSTRAINT sys_c0036728 PRIMARY KEY (experiment_id) USING INDEX scott.sys_c0036728

Step 4:
SELECT dtc.column_name FROM dba_constraints dc,dba_cons_columns dcc, dba_tab_columns dtc WHERE dc.owner ='SCOTT' AND dc.table_name ='ADDRESS' || '_LT'
AND dc.constraint_type = 'P' AND dcc.owner = 'SCOTT'  AND dcc.table_name = 'EXPERIMENT'|| '_LT' AND dc.constraint_name = dcc.constraint_name AND
dcc.column_name NOT IN ('VERSION','LTLOCK','DELSTATUS','NEXTVER') AND dcc.column_name NOT LIKE 'WM$%' AND dcc.column_name NOT LIKE 'WM@_%' ESCAPE '@'
AND dtc.owner = 'SCOTT' AND dtc.table_name = 'EXPERIMENT'||'_LT' AND dcc.column_name = dtc.column_name  ORDER BY dtc.column_id

Step 5:
INSERT INTO wmsys.wm$vt_errors_table
     VALUES (:b4, :b3, :b2, :b1, 'DV STEP BEING EXECUTED', NULL)

Step 6:
ALTER TABLE EXPERIMENT_lt DROP COLUMN ltlock;
ALTER TABLE EXPERIMENT_lt DROP COLUMN delstatus;
ALTER TABLE EXPERIMENT_lt DROP COLUMN VERSION;
ALTER TABLE EXPERIMENT_lt DROP COLUMN NEXTVER;

Step 7:

DELETE FROM TABLE (SELECT undo_code FROM wmsys.wm$versioned_tables WHERE owner ='SCOTT' AND table_name ='EXPERIMENT')
WHERE index_type =:b4 AND index_field =:b3

Oracle Analytic functions

Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic_clause. Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause Analytic functions take 0 to 3 arguments Use OVER analytic_clause to indicate that the function operates on a query result set.