Home

Description

The rule "Dynamically executing code is security-sensitive" in PL/SQL code means that code which is executed at runtime, rather than being compiled and stored in the database, can pose a security risk. This is because the code is not checked for errors or malicious code before it is executed, and so can potentially be used to gain access to sensitive data or perform malicious actions. As such, it is important to ensure that any code which is executed dynamically is properly checked and secured before it is executed.

Key Benefits

• Increased security - It helps to ensure that the code being executed is secure and not vulnerable to exploitation.
• Reduced risk - By preventing the execution of potentially malicious code, this rule helps to reduce the risk of a security breach or other malicious activity.
• Improved compliance - Adhering to this rule helps to ensure that your organization meets the security requirements of any applicable regulatory or industry standards.

Non-compliant Code Example

CREATE OR REPLACE PROCEDURE GetCustomerPhoneNumber (customerId IN INTEGER)
IS
 oracleQuery  VARCHAR2(100);
 customerPhoneNumber NUMBER; 
 
BEGIN
 oracleQuery :=    q'{SELECT PhoneNumber FROM Customers }'
         ||    q'{WHERE id = '}'
         ||    customerId
         ||    q'{'}';
    EXECUTE IMMEDIATE oracleQuery INTO customerPhoneNumber;     --Non Compliant code
END;

Compliant Code Example

CREATE OR REPLACE PROCEDURE GetCustomerPhoneNumber (customerId IN INTEGER)
IS
	oracleQuery  VARCHAR2(100);
	customerPhoneNumber NUMBER; 
	
BEGIN
 oracleQuery := q'{BEGIN SELECT PhoneNumber FROM Customers WHERE id = :customerId  END; }';
        
    EXECUTE IMMEDIATE oracleQuery INTO customerPhoneNumber;        --Compliant code
END;