gpt4 book ai didi

sql - Oracle - 为什么在存储过程中允许 EXECUTE IMMEDIATE?

转载 作者:行者123 更新时间:2023-12-05 08:16:19 29 4
gpt4 key购买 nike

如果存储过程旨在减轻 SQL 注入(inject)攻击,为什么在存储过程中允许 EXECUTE IMMEDIATE?以下问题的公认答案将它们称为针对此类攻击的步骤:

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

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

...除非存储过程正在使用 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 语句而不对输入进行清理一样容易。对我来说,对开发人员说存储过程将有助于保护数据库安全似乎是一种误导。

最佳答案

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

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

应该使用绑定(bind)变量或 dbms_assert 调用。

  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 ;

在下面使用这两种方法的完整示例中。第一个有绑定(bind),第二个用 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>

关于sql - Oracle - 为什么在存储过程中允许 EXECUTE IMMEDIATE?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/50009100/

29 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com