Divida por zero na ORDEM POR CLÁUSULA
Acabei de obter um resultado do PEN-Test de um aplicativo da web. O testador afirmou que era capaz de usar a cláusula ORDER BY de uma instrução SQL dinâmica para recuperar informações. O nome da coluna que deve ser classificada é passado para a consulta por um aplicativo. O testador modificou o valor para que contenha uma instrução complexa semelhante a CASE WHEN ... THEN 1 ELSE 1/0 END.
No entanto, construí um caso de teste muito simples. Tenho um banco de dados Oracle e um SQLServer. Ambos contêm as mesmas tabelas. Eu questiono
SELECT * FROM users ORDER BY 1/0
Quando eu executo isso no Oracle, a consulta funciona bem. No SQLServer recebo um erro 8134 Divide by zero error encountered..
Como o PEN-Tester estava usando o aplicativo em um servidor Oracle diferente do que estou usando agora e eles relataram que estavam abusando do fato de que o Oracle eventualmente gerou erros, eu me pergunto se há uma configuração do Oracle que impede a execução de cláusulas ORDER BY que avaliar para uma divisão por zero. Existe tal configuração? É uma questão de versão do Oracle em uso? Eu estou 12.1.0.2.0onde nenhuma exceção é lançada. Essa configuração também existe no SQLServer?
É claro que modificarei a geração da instrução, portanto, ela verifica se os valores passados são aliases de coluna disponíveis para classificação.
Respostas
Oráculo:
order by 1/0 for bem-sucedido, porque essa cláusula sozinha não faz sentido, o otimizador a elimina automaticamente da consulta no momento da análise e nunca é executada.
SQL> select username from t1 where username like 'SYS%' order by 1/0;
USERNAME
--------------------------------------------------------------
SYS
SYSTEM
SYS$UMF
SYSBACKUP
SYSRAC
SYSKM
SYSDG
7 rows selected.
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
SQL_ID cnnmg28k0vspg, child number 0
-------------------------------------
select username from t1 where username like 'SYS%' order by 1/0
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 9 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("USERNAME" LIKE 'SYS%')
Nenhuma espécie foi realizada.
Adicione outra coisa e falhará:
SQL> select username from t1 where username like 'SYS%' order by 1/0, 1;
select username from t1 where username like 'SYS%' order by 1/0, 1
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
SQL>
Ou:
SQL> select username from t1 where username like 'SYS%' order by 1/0;
USERNAME
--------------------------------------------------------------------------------
SYS
SYSTEM
SYS$UMF
SYSBACKUP
SYSRAC
SYSKM
SYSDG
7 rows selected.
SQL> select /*+ opt_param('_optimizer_order_by_elimination_enabled', 'false') */ username from t1 where username like 'SYS%' order by 1/0;
select /*+ opt_param('_optimizer_order_by_elimination_enabled', 'false') */ username from t1 where username like 'SYS%' order by 1/0
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
SQL>
Além disso, se o valor não for fixo no momento da análise (por exemplo, é uma variável):
SQL> variable B1 number
SQL> exec :B1 := 0;
PL/SQL procedure successfully completed.
SQL> select username from t1 where username like 'SYS%' order by 1/:B1;
select username from t1 where username like 'SYS%' order by 1/:B1
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
SQL>
Para 1/0especificamente, você pode evitar o erro, executando as seguintes SETdeclarações (nota que eu não recomendo este como uma "solução" para o problema, como transformar essas configurações off pode causar muita confusão e ocultar erros importantes):
SET ANSI_WARNINGS, ARITHABORT OFF;
A ORDER BYcláusula suporta a especificação de uma coluna na lista de seleção por sua posição ordinal para classificação. Em outras palavras, " ORDER BY 1" significa ordenar pelo primeiro item na lista de seleção.
Este exemplo usa o banco de dados de amostra "AdventureWorks" da Microsoft:
SELECT p.BusinessEntityID, p.FirstName
FROM Person.Person p
ORDER BY 2;
O SQL Server não oferece suporte a expressões constantes :
SELECT p.BusinessEntityID, p.FirstName
FROM Person.Person p
ORDER BY 2-1;
Msg 408, Nível 16, Estado 1, Linha 18
Uma expressão constante foi encontrada na lista ORDER BY, posição 1.
No seu caso, 1/0é uma expressão constante. No entanto, como calcular isso resultaria em erro, o SQL Server o trata de maneira diferente. Você pode ler sobre isso no artigo de Itzik Ben Gan Números de linha com ordem não determinística :
O que acontece é que, por um lado, o SQL Server não consegue aplicar dobradura constante e, portanto, a ordem é baseada em uma expressão que não é uma única constante. Por outro lado, o otimizador calcula que o valor de ordenação é o mesmo para todas as linhas, portanto, ignora a expressão de ordenação por completo.
Você pode ver isso no plano de execução se executarmos a 1/0versão da consulta com essas duas configurações desativadas :
SET ANSI_WARNINGS, ARITHABORT OFF;
GO
SET STATISTICS XML ON;
GO
SELECT p.BusinessEntityID, p.FirstName
FROM Person.Person p
ORDER BY 1/0;
Neste caso, você pode ver que não há operação de classificação. O Compute Scalar tenta calcular 1/0, mas falha. Como essas duas configurações estão desativadas, o erro "divisão por zero" é suprimido para que a consulta seja concluída (com uma ordem de classificação não determinística).
Uma solução melhor para a classificação dinâmica é discutida no artigo de Erland Sommarskog, Condições de pesquisa dinâmica em T ‑ SQL . A essência dessa solução é usar uma CASEinstrução para transformar a coluna de classificação de entrada do usuário em um valor de coluna conhecido:
SELECT @sql += ' ORDER BY ' +
CASE @sortcol WHEN 'OrderID' THEN 'o.OrderID'
WHEN 'EmplyoeeID' THEN 'o.EmployeeID'
WHEN 'ProductID' THEN 'od.ProductID'
WHEN 'CustomerName' THEN 'c.CompanyName'
WHEN 'ProductName' THEN 'p.ProductName'
ELSE 'o.OrderID'
END + CASE @isdesc WHEN 0 THEN ' ASC' ELSE ' DESC' END
Isso evita que valores inesperados afetem a execução da consulta e ajuda a proteger contra injeção de SQL.
Não sei o quão viável essa abordagem é para você, já que você parece estar tentando manter a compatibilidade entre várias plataformas de banco de dados.