主页 / dba / 问题 / 667
Accepted
Vladimir Oselsky
Asked: 2011-01-18 13:17:18 +0800 CST

构建动态 Oracle Where 子句

  • 772

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

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

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

4 个回答

  1. Best Answer

    这可能会给你一个想法：

    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 中：

    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注入。因此，为了防止这种情况，您可以使用绑定变量，例如在这个修改过的过程中：

    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 ...，显然更安全。

    • 7

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

    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 提供了一些优秀的文档，可以帮助您快速了解存储过程和包。您可能希望从概念指南开始了解 Oracle 的工作原理，然后转到SQL 语言参考和PL/SQL 语言参考以获取与当前任务相关的信息。

    • 6

  3. 这不是独立的答案，而是对 René Nyffenegger 使用绑定变量的代码的附加解释。

    SaUce 询问为什么这段代码对 sql 注入免疫。

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

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

    现在我可以尝试像这样的电话

    Var r refcursor
exec  GetCustomer(:r, 'Micky', '')
print r

    结果是：

    select * from Customer where 1=1 and FirstN like :1

    在 René 的代码中，这将被执行为：

    select * from Customer where 1=1  and FirstN like :1 using 'Micky'

    您会看到，为 FirstN 提供哪个值并不重要。它永远不会改变查询的含义。

    使用变量绑定还有其他原因，这对于具有 SQL-Server 背景的开发人员来说很难掌握。它们取决于 Oracle 在共享池中存储预编译执行计划的方式。不使用绑定变量会给出不同的语句和不同的执行计划，而使用绑定变量会使用单个执行计划。

    • 1

  4. 对于您的存储过程，最好的迁移到 oracle 就像

    CREATE or replace PROCEDURE GetCustomer 
    p_FirstN nvarchar2 := NULL, 
    p_LastN nvarchar2 := NULL, 
    p_CUserName nvarchar2 := NULL, 
    p_CID nvarchar2 := NULL, 
    MyRefCursor IN OUT typRefCursor
as 
begin   

    IF p_FirstN IS NULL then
        if p_p_LastN is null then
            if p_CUserName is null then
                if  p_CID is null then
                    Open MyRefCursor for Select C_FirstName, C_LastName, C_UserName, C_UserID FROM CUSTOMER; 
                else
                    Open MyRefCursor for Select C_FirstName, C_LastName, C_UserName, C_UserID FROM CUSTOMER WHERE upper(C_UserID) like upper(p_CID) ; 
                end;        
            else
                if  p_CID is null then
                    Open MyRefCursor for Select C_FirstName, C_LastName, C_UserName, C_UserID FROM CUSTOMER WHERE UPPER(C_UserName) like UPPER(p_CUserName); 
                else
                    Open MyRefCursor for Select C_FirstName, C_LastName, C_UserName, C_UserID FROM CUSTOMER WHERE upper(C_UserID) like upper(p_CID) and UPPER(C_UserName) like UPPER(p_CUserName); 
                end;        
            end if;
        else
            if p_CUserName is null then
                if  p_CID is null then
                    ...
                else
                    ...
                end;        
            else
                if  p_CID is null then
                    ...
                else
                    ...
                end;        
            end if;
        end if;
    else
        if p_p_LastN is null then
            if p_CUserName is null then
                if  p_CID is null then
                    ...
                else
                    ...
                end;        
            else
                if  p_CID is null then
                    ...
                else
                    ...
                end;        
            end if;
        else
            if p_CUserName is null then
                if  p_CID is null then
                    ...
                else
                    ...
                end;        
            else
                if  p_CID is null then
                    ...
                else
                    ...
                end;        
            end if;
        end if;
    end if; 

end;
/

    好吧，晚了，我有点懒，但填写剩下的 12 个案例是直截了当的。

    不使用动态 SQL 有一些优点：

    1. 您可以在编译时验证您的语法
    2. 你不必摆弄上下文

    不要因为它对人类来说看起来很无聊，就认为它对计算机不利（尤其是在运行 Oracle 时）。

    但是不要为了强迫我展示使用动态 sql 的解决方案而添加更多参数，而是要避免需要此类解决方案的疯狂设计。

    • 0

相关问题