Executing code dynamically is security sensitive. It has led in the past to the following vulnerabilities:
Any code which is dynamically evaluated in your process will have the same permissions as the rest of your code. Thus it is very dangerous to do so
with code coming from an untrusted source. Injected Code can either run on the
server or in the client (exemple: XSS attack).
EXECUTE IMMEDIATE executes as a dynamic SQL statement or anonymous PL/SQL block the string passed as an argument. It’s safe only if
the argument is composed of constant character string expressions. But if the command string is dynamically built using external parameters, then it
is considered very dangerous because executing a random string allows the execution of arbitrary code.
This rule marks for review each occurence of dynamic code execution.
Ask Yourself Whether
- the executed code may come from an untrusted source and hasn’t been sanitized.
- you really need to run code dynamically.
There is a risk if you answered yes to any of those questions.
Recommended Secure Coding Practices
The best solution is to not run code provided by an untrusted source. If you really need to build a command string using external parameters, you
should use EXECUTE IMMEDIATE with bind variables instead.
Do not try to create a blacklist of dangerous code. It is impossible to cover all attacks that way.
Sensitive Code Example
CREATE OR REPLACE PROCEDURE ckpwd (p_user IN VARCHAR2, p_pass IN VARCHAR2)
IS
v_query VARCHAR2(100);
v_output NUMBER;
BEGIN
v_query := q'{SELECT COUNT(*) FROM user_pwd }'
|| q'{WHERE username = '}'
|| p_user
|| q'{' AND password = '}'
|| p_pass
|| q'{'}';
EXECUTE IMMEDIATE v_query
INTO v_output;
END;
Compliant Solution
CREATE OR REPLACE PROCEDURE ckpwd_bind (p_user IN VARCHAR2, p_pass IN VARCHAR2)
IS
v_query VARCHAR2(100);
v_output NUMBER;
BEGIN
v_query :=
q'{SELECT COUNT(*) FROM user_pwd WHERE username = :1 AND password = :2}';
EXECUTE IMMEDIATE v_query
INTO v_output
USING p_user, p_pass;
END;
See
