View the Exhibit.
How do you reduce the chances of SQL injection for the procedure?
A.
Execute the SQL statement in V_STMT as dynamic SQL.
B.
Remove the default value for the arguments in the procedure.
C.
Convert the condition in the WHERE clause to be accepted from the user and concatenated.
D.
Convert the SELECT statement to static SQL, placing the value of P_EMAIL into a local
variable.
A is invalid, because there is no need to use dynamic sql. We have only value of column to substitute.
D – correct
D
why D) is correct, I input p_email := ‘xxx OR 1=1’, then I can get the unauthorized data
D.
3.2 Use Static SQL
If you do not need dynamic SQL, use static SQL
Definition of Static SQL Syntax Template
Stmt constant varchar2(32767) := ‘SELECT Email FROM EMPLOYEES WHERE v_EMPID = :B;
It is clear that the SQL statement will extract Email of the employee whose email has been specified by the bind variable B
Definition of Dynamic SQL Syntax Template
Stmt constant varchar2(32767) := ‘SELECT ‘ || Col_List() || ‘ Report from “My Table” where v_EMPID = :B;
Col_List()is invoked at run time, until then it is not clear how many columns the SQL statement will extract.
http://download.oracle.com/oll/tutorials/SQLInjection/html/lesson3/les03_tm_static.htm