Rule description

• Dynamically executing code is security-sensitive

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;