Oracle - 为什么在存储过程中允许 EXECUTE IMMEDIATE? [英] Oracle - Why is EXECUTE IMMEDIATE allowed in stored procedures?

问题描述

如果存储过程旨在缓解 SQL 注入攻击，为什么在存储过程中允许 EXECUTE IMMEDIATE?以下问题的公认答案将它们称为抵御此类攻击的步骤:

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:

什么是存储过程? https://stackoverflow.com/a/459531/3163495

"存储过程还具有安全性优势，您可以授予执行存储过程的权限，但用户不需要对基础表具有读/写权限.这是一个很好的对抗 SQL 注入的第一步."

"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."

...除非存储过程使用 EXECUTE IMMEDIATE.

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

此 PL/SQL 代码返回产品的描述(第二个参数).

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;

恶意用户输入.

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

生成的查询.

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

执行查询时，攻击者会获得管理员密码.

如您所见，虽然我们使用了存储过程，但攻击者仍然可以轻松利用漏洞，就像我们是业余开发人员在 PHP 中连接一些 SELECT 语句而不清理输入一样容易.在我看来，对开发人员说存储过程将有助于确保您的数据库安全似乎是一种误导.

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.

推荐答案

Execute Immediate 仍然可以安全地使用.这一切都归结为存储过程的逻辑.concat 使代码不安全，而不是立即执行.

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 || '''';

应该使用绑定变量或 dbms_assert 调用.

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 ;

或

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

在下面使用这两种方法的完整示例中.第一个有绑定，第二个用 DBMS_ASSERT 包装.

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> 

这篇关于Oracle - 为什么在存储过程中允许 EXECUTE IMMEDIATE?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！