Oracle - Why is EXECUTE IMMEDIATE allowed in stored procedures?

Question

Why is EXECUTE IMMEDIATE allowed in stored procedures, if stored procedures are meant to mitigate SQL injection attacks? The accepted answer to the following question refers to them as a step against such attacks:

What is a stored procedure? https://stackoverflow.com/a/459531/3163495

"Stored procedures also have a security benefit in that you can grant execute rights to a stored procedure but the user will not need to have read/write permissions on the underlying tables. This is a good first step against SQL injection."

...unless the stored procedure is using EXECUTE IMMEDIATE.

This PL/SQL code returns a product's description (second parameter).

CREATE OR REPLACE PROCEDURE prodDescr(vname IN VARCHAR2, vresult OUT VARCHAR2) AS
vsql VARCHAR2(4000);
BEGIN
vsql := 'SELECT description FROM products WHERE name=''' || vname || '''';
EXECUTE IMMEDIATE vsql INTO vresult;
END;

Malicious user input.

A' AND 1=2 UNION SELECT password FROM members WHERE username='admin

Generated Query.

SELECT description FROM products WHERE name='A' OR 1=2 UNION SELECT password FROM members WHERE username='admin'

When the query is executed, the attacker gets the administrator’s password.

As you can see, although we used a stored procedure, an attacker can still exploit a vulnerability just as easily as if we were an amateur developer concatenating some SELECT statement in PHP without sanitizing input. To me, it seems it can be very misleading to say to developers that stored procedures will help keep your database safe.

Answer 1

Execute Immediate can still be used in a safe way. It all comes down to the logic of the stored proc. The concat is making the code unsafe not the execute immediate.

vsql := 'SELECT description FROM products WHERE name=''' || vname || '''';

Should be using bind variables or a dbms_assert call.

  vsql := 'select count(1) from all_objects where owner = :1'
  EXECUTE IMMEDIATE vsql into vresult using vname ;

OR

 vsql := 'select count(1) from all_objects where owner ='||DBMS_ASSERT.ENQUOTE_LITERAL(vname);
  EXECUTE IMMEDIATE vsql into vresult  ;

In a full example below using both methods. The first has bind(s) and the second is wrappered with DBMS_ASSERT.

SQL>declare
      v_in varchar2(2000);
      ret  varchar2(2000);
    begin
      v_in := 'KLRICE';
     EXECUTE IMMEDIATE 'select count(1) from all_objects where owner = :1' into ret using v_in ;
     dbms_output.put_line('First Object Count  : ' || ret);

     EXECUTE IMMEDIATE 'select count(1) from all_objects where owner ='||DBMS_ASSERT.ENQUOTE_LITERAL(v_in)  into ret ;

    dbms_output.put_line('Second Object Count  : ' || ret);
  end
SQL> /
First Object Count  : 74
Second Object Count  : 74


PL/SQL procedure successfully completed.

SQL>