文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >动态Oracle的构建

问动态Oracle的构建
EN

Database Administration用户
提问于 2011-01-17 21:17:18
回答 4查看 27.1K关注 0票数 7

在与DBA讨论安全性之后，我正在开发应用程序，该应用程序使用动态查询根据用户输入执行select语句，DBA希望我将动态select语句转换为存储过程。

我已经使用MSSQL构建了动态SQL，但我不知道如何将它转换为Oracle sql。

代码语言：javascript
复制
CREATE PROCEDURE GetCustomer
@FirstN nvarchar(20) = NULL,
@LastN nvarchar(20) = NULL,
@CUserName nvarchar(10) = NULL, 
@CID nvarchar(15) = NULL as
DECLARE @sql nvarchar(4000),
SELECT @sql = 'C_FirstName, C_LastName, C_UserName, C_UserID ' + 
'FROM CUSTOMER ' +
'WHERE 1=1 ' +

IF @FirstN  IS NOT NULL
SELECT @sql = @sql + ' AND C_FirstName like @FirstN '
IF @LastN  IS NOT NULL 
SELECT @sql = @sql + ' AND C_LastName like @LastN '
IF @CUserName IS NOT NULL
SELECT @sql = @sql + ' AND C_UserName like @CUserName '
IF @CID IS NOT NULL 
SELECT @sql = @sql + ' AND C_UserID like @CID '
EXEC sp_executesql @sql, N'@C_FirstName nvarchar(20), @C_LastName nvarchar(20), @CUserName nvarchar(10), @CID nvarchar(15)',
                   @FirstN, @LastN, @CUserName, @CID

*请注意，我希望防止SQL注入，我不想仅仅将字符串添加到一起

**我为在.net中的应用程序创建这个动态查询构建了一个单独的类--我有将近1000行代码来处理所有内容并防止sql注入，但是DBA告诉我，他们需要存储过程，以便能够控制输入和输出。

oracle
where
EN

回答 4

Database Administration用户

回答已采纳

发布于 2011-01-17 21:46:59

这可能会给你一个想法：

代码语言：javascript
复制
create table Customer (
  c_firstname varchar2(50),
  c_lastname  varchar2(50),
  c_userid    varchar2(50)
);

insert into Customer values ('Micky' , 'Mouse', 'mm');
insert into Customer values ('Donald', 'Duck' , 'dd');
insert into Customer values ('Peter' , 'Pan'  , 'pp');

create or replace function GetCustomer(
  FirstN    varchar2 := null,
  LastN     varchar2 := null,
  CID       varchar2 := null
) return sys_refcursor
as
  stmt varchar2(4000);
  ret sys_refcursor;
begin
  stmt := 'select * from Customer where 1=1';

  if  FirstN is not null then
      stmt := stmt || ' and c_firstname like ''%' || FirstN || '%''';
  end if;

  if  LastN is not null then
      stmt := stmt || ' and c_lastname like ''%' || LastN  || '%''';
  end if;

  if  CID is not null then
      stmt := stmt || ' and c_userid like ''%' || CID || '%''';
  end if;

  dbms_output.put_line(stmt);

  open ret for stmt;
  return ret;
end;
/

稍后，在SQL*Plus中：

代码语言：javascript
复制
set serveroutput on size 100000 format wrapped

declare
  c sys_refcursor;
  fn Customer.c_firstname%type;
  ln Customer.c_lastname %type;
  id Customer.c_userid   %type;
begin
  c := GetCustomer(LastN => 'u');

  fetch c into fn, ln, id;
  while  c%found loop
      dbms_output.put_line('First Name: ' || fn);
      dbms_output.put_line('Last Name:  ' || ln);
      dbms_output.put_line('user id:    ' || id);

      fetch c into fn, ln, id;
  end loop;

  close c;
end;
/

编辑:注释是正确的，该过程受SQL注入的限制。因此，为了防止出现这种情况，可以使用绑定变量，例如在这个修改的过程中：

代码语言：javascript
复制
create or replace function GetCustomer(
  FirstN    varchar2 := null,
  LastN     varchar2 := null,
  CID       varchar2 := null
) return sys_refcursor
as
  stmt varchar2(4000);
  ret  sys_refcursor;

  type parameter_t is table of varchar2(50);
  parameters parameter_t := parameter_t();
begin
  stmt := 'select * from Customer where 1=1';

  if  FirstN is not null then
      parameters.extend;
      parameters(parameters.count) := '%' || FirstN || '%';
      stmt := stmt || ' and c_firstname like :' || parameters.count;
  end if;

  if  LastN is not null then
      parameters.extend;
      parameters(parameters.count) := '%' || LastN || '%';
      stmt := stmt || ' and c_lastname like :' || parameters.count;
  end if;

  if  CID is not null then
      parameters.extend;
      parameters(parameters.count) := '%' || CID || '%';
      stmt := stmt || ' and c_userid like :' || parameters.count;
  end if;


  if    parameters.count = 0 then
        open ret for stmt;
  elsif parameters.count = 1 then
        open ret for stmt using parameters(1);
  elsif parameters.count = 2 then
        open ret for stmt using parameters(1), parameters(2);
  elsif parameters.count = 3 then
        open ret for stmt using parameters(1), parameters(2), parameters(3);
  else  raise_application_error(-20800, 'Too many parameters');
  end   if;

  return ret;
end;
/

注意，现在，不管输入是什么，select语句变成了类似于select ... from ... where 1=1 and col1 like :1 and col2 :2 ...的东西，这显然要安全得多。

票数 6
EN

Database Administration用户

发布于 2011-01-17 21:47:35

您不一定需要动态SQL，仅仅因为某些条件在不存在时不适用。

代码语言：javascript
复制
SELECT 
    C_FirstName, C_LastName, C_UserName, C_UserID 
FROM 
    CUSTOMER
WHERE 
    (FirstN IS NULL OR C_FirstName LIKE FirstN)
    AND (LastN IS NULL OR C_LastName LIKE LastN)
    AND (CUserName IS NULL OR C_UserName LIKE CUserName)
    AND (CID IS NULL OR C_UserID LIKE CID)

将此代码放在包内的存储过程中是一个很好的主意。

Oracle提供了一些优秀的文档，可以帮助您了解存储过程和包。您可能希望从概念指南开始，了解甲骨文的工作原理，然后转到SQL语言引用和PL/SQL语言引用，获取与当前任务相关的信息。

票数 6
EN

Database Administration用户

发布于 2011-01-22 12:52:06

这不是一个独立的答案，而是对RenéNyffenegger使用绑定变量的代码的额外解释。

SaUce询问了为什么这段代码不受sql注入的影响。

在这里，我将勒内的S代码更改为不执行动态语句，而是显示它：

代码语言：javascript
复制
create or replace function GetCustomer(
  FirstN    varchar2 := null,
  LastN     varchar2 := null,
  CID       varchar2 := null
) return sys_refcursor
as
  stmt varchar2(4000);
  ret  sys_refcursor;

  type parameter_t is table of varchar2(50);
  parameters parameter_t := parameter_t();
begin
  stmt := 'select * from Customer where 1=1';

  if  FirstN is not null then
      parameters.extend;
      parameters(parameters.count) := '%' || FirstN || '%';
      stmt := stmt || ' and c_firstname like :' || parameters.count;
  end if;

  if  LastN is not null then
      parameters.extend;
      parameters(parameters.count) := '%' || LastN || '%';
      stmt := stmt || ' and c_lastname like :' || parameters.count;
  end if;

  if  CID is not null then
      parameters.extend;
      parameters(parameters.count) := '%' || CID || '%';
      stmt := stmt || ' and c_userid like :' || parameters.count;
  end if;


   OPEN ret for SELECT stmt FROM DUAL;


  return ret;
end;
/

现在我可以试着打电话了

代码语言：javascript
复制
Var r refcursor
exec  GetCustomer(:r, 'Micky', '')
print r

结果是：

从1=1和FirstN喜欢的客户位置选择* :1

在勒内的S代码中，这将被执行为：

代码语言：javascript
复制
select * from Customer where 1=1  and FirstN like :1 using 'Micky'

您知道，为FirstN提供哪个值并不重要。它从不改变查询的含义。

使用变量绑定还有更多的原因，对于具有Server背景的开发人员来说，这一点很难掌握。它们取决于Oracle如何在共享池中存储预编译的执行计划。不使用绑定变量会产生不同的语句和不同的执行计划，而使用绑定变量则使用单个执行计划。

票数 1
EN
页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://dba.stackexchange.com/questions/667

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档