Início / dba / Perguntas / 115073
Accepted
J Hays
Asked: 2015-09-16 05:06:45 +0800 CST

Prática recomendada para passar e trabalhar com parâmetros de segurança/desempenho

  • 772

Nosso programador sênior me pediu para escrever procedimentos armazenados no seguinte formato para proteção contra ataques de injeção. Ele diz que a melhor prática é pegar o parâmetro, então declarar uma nova variável no corpo e atribuir essa variável ao parâmetro, que esta etapa é uma proteção extra contra ataques de injeção, pois força qualquer tentativa de injeção a ser considerada como dados e não tomada literalmente. Isso é verdade? Eu pensaria que isso retardaria a consulta e usaria memória extra e não adicionaria nenhuma proteção extra, mas posso estar errado. Exemplo abaixo. 

    CREATE PROCEDURE [dbo].[sp_foo_GET] 
        @parAcct            NVARCHAR(20)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE 
        @varAcct            NVARCHAR(20) = @parAcct;

    SELECT [userid] 
    FROM [tblAccounts] 
    WHERE [accountID] = @varAcct;   
END

EDIT Depois de conversar com o programador, ele parece pensar que passar parâmetros para uma variável e atribuí-los a uma variável declarada ajuda na segurança E fará com que as consultas sejam executadas mais rapidamente.

best-practices parameter

1 respostas

  1. Best Answer

    Ele parece confuso. Usar o mesmo valor de uma variável local não deve ser diferente de usá-lo de um parâmetro de entrada. Seu colega está entendendo errado no que diz respeito ao uso de consultas parametrizadas (às vezes chamadas de instruções preparadas) em vez de SQL ad-hoc completo?

    Por exemplo:

    resultSet = dbconnector.getRS('EXEC sp_foo_GET \''+inputVariable+'\');

    pode ser uma rota de injeção e mover o valor entre as variáveis ​​dentro do procedimento não o afetará em nada, pois o código injetado não será visto pelo procedimento. Se inputVariablefosse para ter o valor xxx'; WAITFOR DELAY '00:10:00'--o SQL enviado para o servidor seria

    EXEC sp_foo_GET 'xxx'; WAITFOR DELAY '00:10:00'--'

    Seu procedimento seria executado com o parâmetro de entrada definido como xxxe não conheceria o código extra, ele será executado após a conclusão da chamada do procedimento.

    Com uma instrução preparada, supondo que sua biblioteca de acesso ao banco de dados os suporte adequadamente e não esteja fazendo bobagens ad hoc sob o capô, isso não aconteceria:

    resultSet = dbconnector.getRSWithParams('EXEC sp_foo_GET ?', [inputVariable]);

    Seu procedimento seria chamado com o valor, xxx'; WAITFOR DELAY '00:10:00'--em vez de esse código extra ser visto como algo a ser executado.

    É claro que a técnica pode ser parte de um padrão maior que ele deseja que você siga, cujo objetivo ficará claro à medida que você fizer coisas mais avançadas, mas que na verdade não faz nada de útil (além de criar o hábito de seguir o padrão) neste momento estágio, mas suspeito que ele está simplesmente entendendo mal e, portanto, adicionando trabalho desnecessário. Você poderia pedir a ele para explicar com mais detalhes? Talvez ele possa fornecer a você uma chamada de exemplo contra a qual ele acha que o padrão protegerá?

    Depois do comentário Ref. atuação

    De acordo com seu comentário, embora eu duvide que haja algum motivo de segurança para esse padrão, a nova explicação de que isso pode fazer uma diferença de desempenho é mais válida, embora você possa achar que na maioria dos casos não faz diferença e, em alguns, reduz em vez de aumentar o desempenho. benchmarking em vez de usar cegamente o padrão é recomendado.

    O problema vem do cache do plano de consulta e de como os parâmetros de entrada e os literais são tratados de maneira diferente das variáveis ​​locais. Não entrarei em muitos detalhes aqui, pois estamos nos afastando da pergunta original (pesquise na documentação do MS e na Web geralmente em torno das frases "sniffing de parâmetro", "cache do plano de consulta" e " OPTIMIZE FOR, em seguida, pergunte a um nova pergunta se você precisar de mais clareza depois de ler algumas coisas), mas como um iniciante para dez: no exemplo que você deu acima, o que você está fazendo com a variável local é efetivamente o mesmo que declarar o procedimento com OPTION (OPTIMIZE FOR (@parAcct UNKNOWN)). , como acontece com qualquer dica de otimização, pode variar muito dependendo das consultas executadas no procedimento e do equilíbrio dos dados tocados por elas.

    A diferença entre o uso de parâmetros de entrada, literais e variáveis ​​locais em relação aos planos em cache pode ser significativa ao depurar problemas de desempenho (pode significar que o perfil de desempenho que você vê ao depurar/testar é bem diferente daquele visto na produção, se você não estiver ciente disso o problema). http://www.brentozar.com/archive/2014/06/tuning-stored-procedures-local-variables-problems/ cobre a questão deste contexto particular.

    • 4

relate perguntas