Let us say we have a requirement to sort the result set based on the given input parameter . In most of the system , the developers concacatenate the SELECT statement with ORDER BY clause .
In our example ( scott's emp table) , this will be something like
....
lssql = "SELECT EMPNO , ENAME ,JOB ,HIREDATE , SAL ,DEPTNO FROM EMP ORDER BY " + p_input
....
...
The above sql is potential candiate for SQL injection . The above SQL can be rewritten as below to efficetly use BIND varaibles.
We can also employ dynamic SQL to implement the variants of dynamic SORT.
SQL> show user
USER is "ZAHIR"
SQL> create table emp as select * from scott.emp;
Table created.
SQL> desc emp
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> set pagesize 90
SQL> SELECT EMPNO ,
2 ENAME ,
3 JOB ,
4 HIREDATE ,
5 SAL ,
6 DEPTNO
7 FROM emp ;
EMPNO ENAME JOB HIREDATE SAL DEPTNO
---------- ---------- --------- --------- ---------- ----------
7369 SMITH CLERK 17-DEC-80 800 20
7499 ALLEN SALESMAN 20-FEB-81 1600 30
7521 WARD SALESMAN 22-FEB-81 1250 30
7566 JONES MANAGER 02-APR-81 2975 20
7654 MARTIN SALESMAN 28-SEP-81 1250 30
7698 BLAKE MANAGER 01-MAY-81 2850 30
7782 CLARK MANAGER 09-JUN-81 2450 10
7788 SCOTT ANALYST 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 08-SEP-81 1500 30
7876 ADAMS CLERK 23-MAY-87 1100 20
7900 JAMES CLERK 03-DEC-81 950 30
7902 FORD ANALYST 03-DEC-81 3000 20
7934 MILLER CLERK 23-JAN-82 1300 10
14 rows selected.
SQL> variable p_input varchar2(20);
SQL> exec :p_input := 'EMPNO';
PL/SQL procedure successfully completed.
SQL> SELECT EMPNO ,
2 ENAME ,
3 JOB ,
4 HIREDATE ,
5 SAL ,
6 DEPTNO
7 FROM emp
8 ORDER BY
9 CASE
10 WHEN :P_INPUT = 'EMPNO'
11 THEN to_char(EMPNO)
12 WHEN :p_input = 'ENAME'
13 THEN ENAME
14 WHEN :p_input = 'JOB'
15 THEN JOB
16 WHEN :P_INPUT = 'HIREDATE'
17 THEN TO_CHAR(HIREDATE, 'YYYY-MM-DD')
18 ELSE to_char(DEPTNO)
19 END
20 ;
EMPNO ENAME JOB HIREDATE SAL DEPTNO
---------- ---------- --------- --------- ---------- ----------
7369 SMITH CLERK 17-DEC-80 800 20
7499 ALLEN SALESMAN 20-FEB-81 1600 30
7521 WARD SALESMAN 22-FEB-81 1250 30
7566 JONES MANAGER 02-APR-81 2975 20
7654 MARTIN SALESMAN 28-SEP-81 1250 30
7698 BLAKE MANAGER 01-MAY-81 2850 30
7782 CLARK MANAGER 09-JUN-81 2450 10
7788 SCOTT ANALYST 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 08-SEP-81 1500 30
7876 ADAMS CLERK 23-MAY-87 1100 20
7900 JAMES CLERK 03-DEC-81 950 30
7902 FORD ANALYST 03-DEC-81 3000 20
7934 MILLER CLERK 23-JAN-82 1300 10
14 rows selected.
SQL> exec :p_input := 'JOB';
PL/SQL procedure successfully completed.
SQL> SELECT EMPNO ,
2 ENAME ,
3 JOB ,
4 HIREDATE ,
5 SAL ,
6 DEPTNO
7 FROM emp
8 ORDER BY
9 CASE
10 WHEN :P_INPUT = 'EMPNO'
11 THEN to_char(EMPNO)
12 WHEN :p_input = 'ENAME'
13 THEN ENAME
14 WHEN :p_input = 'JOB'
15 THEN JOB
16 WHEN :P_INPUT = 'HIREDATE'
17 THEN TO_CHAR(HIREDATE, 'YYYY-MM-DD')
18 ELSE to_char(DEPTNO)
19 END
20 ;
EMPNO ENAME JOB HIREDATE SAL DEPTNO
---------- ---------- --------- --------- ---------- ----------
7788 SCOTT ANALYST 19-APR-87 3000 20
7902 FORD ANALYST 03-DEC-81 3000 20
7934 MILLER CLERK 23-JAN-82 1300 10
7900 JAMES CLERK 03-DEC-81 950 30
7369 SMITH CLERK 17-DEC-80 800 20
7876 ADAMS CLERK 23-MAY-87 1100 20
7698 BLAKE MANAGER 01-MAY-81 2850 30
7566 JONES MANAGER 02-APR-81 2975 20
7782 CLARK MANAGER 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 08-SEP-81 1500 30
7654 MARTIN SALESMAN 28-SEP-81 1250 30
7521 WARD SALESMAN 22-FEB-81 1250 30
7499 ALLEN SALESMAN 20-FEB-81 1600 30
14 rows selected.
Comments welcome.
In our example ( scott's emp table) , this will be something like
....
lssql = "SELECT EMPNO , ENAME ,JOB ,HIREDATE , SAL ,DEPTNO FROM EMP ORDER BY " + p_input
....
...
The above sql is potential candiate for SQL injection . The above SQL can be rewritten as below to efficetly use BIND varaibles.
We can also employ dynamic SQL to implement the variants of dynamic SORT.
SQL> show user
USER is "ZAHIR"
SQL> create table emp as select * from scott.emp;
Table created.
SQL> desc emp
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> set pagesize 90
SQL> SELECT EMPNO ,
2 ENAME ,
3 JOB ,
4 HIREDATE ,
5 SAL ,
6 DEPTNO
7 FROM emp ;
EMPNO ENAME JOB HIREDATE SAL DEPTNO
---------- ---------- --------- --------- ---------- ----------
7369 SMITH CLERK 17-DEC-80 800 20
7499 ALLEN SALESMAN 20-FEB-81 1600 30
7521 WARD SALESMAN 22-FEB-81 1250 30
7566 JONES MANAGER 02-APR-81 2975 20
7654 MARTIN SALESMAN 28-SEP-81 1250 30
7698 BLAKE MANAGER 01-MAY-81 2850 30
7782 CLARK MANAGER 09-JUN-81 2450 10
7788 SCOTT ANALYST 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 08-SEP-81 1500 30
7876 ADAMS CLERK 23-MAY-87 1100 20
7900 JAMES CLERK 03-DEC-81 950 30
7902 FORD ANALYST 03-DEC-81 3000 20
7934 MILLER CLERK 23-JAN-82 1300 10
14 rows selected.
SQL> variable p_input varchar2(20);
SQL> exec :p_input := 'EMPNO';
PL/SQL procedure successfully completed.
SQL> SELECT EMPNO ,
2 ENAME ,
3 JOB ,
4 HIREDATE ,
5 SAL ,
6 DEPTNO
7 FROM emp
8 ORDER BY
9 CASE
10 WHEN :P_INPUT = 'EMPNO'
11 THEN to_char(EMPNO)
12 WHEN :p_input = 'ENAME'
13 THEN ENAME
14 WHEN :p_input = 'JOB'
15 THEN JOB
16 WHEN :P_INPUT = 'HIREDATE'
17 THEN TO_CHAR(HIREDATE, 'YYYY-MM-DD')
18 ELSE to_char(DEPTNO)
19 END
20 ;
EMPNO ENAME JOB HIREDATE SAL DEPTNO
---------- ---------- --------- --------- ---------- ----------
7369 SMITH CLERK 17-DEC-80 800 20
7499 ALLEN SALESMAN 20-FEB-81 1600 30
7521 WARD SALESMAN 22-FEB-81 1250 30
7566 JONES MANAGER 02-APR-81 2975 20
7654 MARTIN SALESMAN 28-SEP-81 1250 30
7698 BLAKE MANAGER 01-MAY-81 2850 30
7782 CLARK MANAGER 09-JUN-81 2450 10
7788 SCOTT ANALYST 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 08-SEP-81 1500 30
7876 ADAMS CLERK 23-MAY-87 1100 20
7900 JAMES CLERK 03-DEC-81 950 30
7902 FORD ANALYST 03-DEC-81 3000 20
7934 MILLER CLERK 23-JAN-82 1300 10
14 rows selected.
SQL> exec :p_input := 'JOB';
PL/SQL procedure successfully completed.
SQL> SELECT EMPNO ,
2 ENAME ,
3 JOB ,
4 HIREDATE ,
5 SAL ,
6 DEPTNO
7 FROM emp
8 ORDER BY
9 CASE
10 WHEN :P_INPUT = 'EMPNO'
11 THEN to_char(EMPNO)
12 WHEN :p_input = 'ENAME'
13 THEN ENAME
14 WHEN :p_input = 'JOB'
15 THEN JOB
16 WHEN :P_INPUT = 'HIREDATE'
17 THEN TO_CHAR(HIREDATE, 'YYYY-MM-DD')
18 ELSE to_char(DEPTNO)
19 END
20 ;
EMPNO ENAME JOB HIREDATE SAL DEPTNO
---------- ---------- --------- --------- ---------- ----------
7788 SCOTT ANALYST 19-APR-87 3000 20
7902 FORD ANALYST 03-DEC-81 3000 20
7934 MILLER CLERK 23-JAN-82 1300 10
7900 JAMES CLERK 03-DEC-81 950 30
7369 SMITH CLERK 17-DEC-80 800 20
7876 ADAMS CLERK 23-MAY-87 1100 20
7698 BLAKE MANAGER 01-MAY-81 2850 30
7566 JONES MANAGER 02-APR-81 2975 20
7782 CLARK MANAGER 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 08-SEP-81 1500 30
7654 MARTIN SALESMAN 28-SEP-81 1250 30
7521 WARD SALESMAN 22-FEB-81 1250 30
7499 ALLEN SALESMAN 20-FEB-81 1600 30
14 rows selected.
Comments welcome.