title: Oracle - Why is EXECUTE IMMEDIATE allowed in stored procedures?
body: <p>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:</p>
  <blockquote>
  <p><em>What is a stored procedure?</em> <a href="https://stackoverflow.com/a/459531/3163495">https://stackoverflow.com/a/459531/3163495</a></p>
  <p>&quot;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.&quot;</p>
  </blockquote>
  <p>...unless the stored procedure is using EXECUTE IMMEDIATE.</p>
  <p><strong>This PL/SQL code returns a product's description (second parameter).</strong></p>
  <pre><code>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;
  </code></pre>
  <p><strong>Malicious user input.</strong></p>
  <pre><code>A' AND 1=2 UNION SELECT password FROM members WHERE username='admin
  </code></pre>
  <p><strong>Generated Query.</strong></p>
  <pre><code>SELECT description FROM products WHERE name='A' OR 1=2 UNION SELECT password FROM members WHERE username='admin'
  </code></pre>
  <p><strong>When the query is executed, the attacker gets the administrator’s password.</strong></p>
  <p>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.</p>