Examine the following line of code that is part of a PL/SQL application:
stmt:=’SELECT session_id FROM sessions WHERE ‘ || p_where_stmt;
Identify a solution for preventing SQL injection in the above code.
A.
Replace P_WHERE_STMT with a bind variable.
B.
Do not use APIs that allow arbitrary query parameters to be exposed.
C.
Use the RESTRICT_REFERENCES clause in the PL/SQL subprogram that contains the code.
D.
Use DBMS_SQL to detect that the expression provided for P_WHERE_STMT is free from SQL injection.
B
answer a) b) c): a) — obviously the statement would be executed VIA dynamic sql, so a bind variable can avould malicious string concatenation input like ‘XXXX UNION select employee_name, employee_salary FROM employees”
b) prevent the query parameter is exposed, in that case, the user might maliciously input string concatenation
c) no read/write to database state(table data), package state(package variable), thus no leakage of confidential data.
A seems not correct, as the dynamic sql doesn’t concat parameter value, it concat an condition; If you don’t know which column you want to restrict, how to use bind parameter?
B seems correct, don’t allow this will solve this.
C seems not correct, even you have restriction, but this sql is used to read data from sessions, so others still could inject the sql to read more data than you expect.
D. has no idea.