Backups de banco de dados no Oracle - Exportar o banco de dados ou usar outras ferramentas?

Question

Vladimir Oselsky

Asked: 2011-01-18 13:17:18 +0800 CST2011-01-18 13:17:18 +0800 CST 2011-01-18 13:17:18 +0800 CST

Construindo Cláusula Where do Oracle Dinâmico

772

Estou trabalhando em um aplicativo que usa consulta dinâmica para fazer uma instrução select com base na entrada do usuário, depois de discutir segurança com DBAs eles querem que eu converta minha instrução de seleção dinâmica em procedimento armazenado.

Eu construí sql dinâmico usando MSSQL, mas não consigo descobrir como convertê-lo para 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

*por favor, note que eu quero evitar a injeção de SQL, não quero apenas adicionar strings

**criei uma classe separada para criar essa consulta dinâmica para meu aplicativo em .net, tenho quase 1000 linhas de código para lidar com tudo e evitar injeção de sql, mas os DBAs me disseram que querem procedimentos armazenados para que possam controlar entrada e resultado.

4 respostas

Voted

René Nyffenegger · Answer 1 · 2011-01-18T13:46:59+08:00

Isso pode te dar uma ideia:

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

Mais tarde, no 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;
/

Edit : O comentário está correto e o procedimento está sujeito a injeção de SQL . Portanto, para evitar isso, você pode usar variáveis de ligação, como neste procedimento modificado:

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

Observe que agora, qualquer que seja a entrada, a instrução select se torna algo select ... from ... where 1=1 and col1 like :1 and col2 :2 ...que obviamente é muito mais seguro.

Leigh Riffel · Answer 2 · 2011-01-18T13:47:35+08:00

Leigh Riffel

2011-01-18T13:47:35+08:002011-01-18T13:47:35+08:00

Você não precisa necessariamente de SQL dinâmico apenas porque certas condições não se aplicam quando não estão presentes.

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)

Colocar esse código em um procedimento armazenado dentro de um pacote é uma excelente ideia.

A Oracle fornece uma excelente documentação que pode agilizar os procedimentos e pacotes armazenados. Você pode querer começar com o Guia de Conceitos para entender como o Oracle funciona e, em seguida, passar para a Referência da Linguagem SQL e a Referência da Linguagem PL/SQL para obter informações pertinentes à sua tarefa atual.

6

bernd_k · Answer 3 · 2011-01-23T04:52:06+08:00

Esta não é uma resposta independente, mas uma explicação adicional ao código de René Nyffenegger usando variáveis de ligação.

SaUce perguntou por que esse código é imune à injeção de SQL.

Aqui eu mudo o código do René para não executar a instrução dinâmica, mas para exibi-la:

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

Agora posso tentar chamadas como

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

O resultado é:

selecione * do cliente onde 1 = 1 e FirstN como: 1

No código de René isso será executado como:

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

Veja bem, não importa qual valor é fornecido para FirstN. Ele nunca muda o significado da consulta.

Existem outras razões para usar a vinculação de variáveis, que são difíceis de entender para desenvolvedores com experiência em SQL-Server. Eles dependem da maneira como o Oracle armazena planos de execução pré-compilados no pool compartilhado. Não usar variáveis de ligação fornece instruções diferentes e planos de execução diferentes, enquanto usar variáveis de ligação usa um único plano de execução.

bernd_k · Answer 4 · 2011-01-18T15:01:15+08:00

Para seu procedimento armazenado, a melhor migração para o oracle seria

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

OK, é tarde e estou um pouco preguiçoso, mas preencher os 12 casos restantes é simples.

Não usar SQL dinâmico tem algumas vantagens:

Você pode verificar sua sintaxe em tempo de compilação
Você não precisa mexer nos contextos

Não pense porque parece chato para um humano, que é ruim para um computador (especialmente ao executar o Oracle).

Mas não adicione mais parâmetros apenas para me forçar a mostrar uma solução usando sql dinâmico, em vez disso, evite projetos insanos que exijam tais soluções.

Construindo Cláusula Where do Oracle Dinâmico

Como você mysqldump tabela (s) específica (s)?

Como você mostra o SQL em execução em um banco de dados Oracle?

Como selecionar a primeira linha de cada grupo?

Listar os privilégios do banco de dados usando o psql

Posso ver Consultas Históricas executadas em um banco de dados SQL Server?

Como uso currval() no PostgreSQL para obter o último id inserido?

Como executar o psql no Mac OS X?

Como inserir valores em uma tabela de uma consulta de seleção no PostgreSQL?

Como faço para listar todos os bancos de dados e tabelas usando o psql?

Passando parâmetros de array para um procedimento armazenado

Construindo Cláusula Where do Oracle Dinâmico

4 respostas

relate perguntas