Dividir por cero en ORDEN POR CLAUSULA
Acabo de recibir un resultado de la prueba PEN de una aplicación web. El evaluador declaró que podían usar la cláusula ORDER BY de una declaración SQL dinámica para recuperar información. Una aplicación pasa el nombre de la columna que debe ordenarse a la consulta. El evaluador modificó el valor para que contenga una declaración compleja que se parece a CASE WHEN ... THEN 1 ELSE 1/0 END.
Sin embargo, construí un caso de prueba muy simple. Tengo una base de datos Oracle y un SQLServer. Ambos contienen las mismas tablas. Yo consulto
SELECT * FROM users ORDER BY 1/0
Cuando ejecuto esto en Oracle, la consulta se ejecuta bien. En SQLServer obtengo un error 8134 Divide by zero error encountered..
Como el PEN-Tester estaba usando la aplicación en un servidor Oracle diferente al que estoy usando ahora e informan que estaban abusando del hecho de que Oracle finalmente arrojó errores, me pregunto si hay una configuración de Oracle que evita la ejecución de cláusulas ORDER BY que evaluar a una división por cero. ¿Existe tal escenario? ¿Es una cuestión de la versión de Oracle en uso? Estoy en 12.1.0.2.0donde no se lanza ninguna excepción. ¿Existe también esa configuración en SQLServer?
Por supuesto, modificaré la generación de la declaración, para que verifique si los valores que se pasan son alias de columna disponibles para ordenar.
Respuestas
Oráculo:
order by 1/0 tiene éxito, porque esa cláusula por sí sola no tiene sentido, el optimizador la elimina automáticamente de la consulta en el momento del análisis y nunca se ejecuta.
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%')
No se realizó ningún tipo.
Agregue algo más y fallará:
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>
O:
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>
Además, si el valor no se fija en el momento del análisis (por ejemplo, es una variable):
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/0específicamente, se puede evitar el error mediante la ejecución de las siguientes SETdeclaraciones (nota que no recomiendo esto como una "solución" a su problema, como convertir esos ajustes apagado puede causar mucha confusión y ocultar errores importantes):
SET ANSI_WARNINGS, ARITHABORT OFF;
La ORDER BYcláusula admite especificar una columna en la lista de selección por su posición ordinal para ordenar. En otras palabras, " ORDER BY 1" significa ordenar por el primer elemento de la lista de selección.
Este ejemplo utiliza la base de datos de muestra "AdventureWorks" de Microsoft:
SELECT p.BusinessEntityID, p.FirstName
FROM Person.Person p
ORDER BY 2;
Sin embargo, SQL Server no admite expresiones constantes :
SELECT p.BusinessEntityID, p.FirstName
FROM Person.Person p
ORDER BY 2-1;
Msg 408, Nivel 16, Estado 1, Línea 18
Se encontró una expresión constante en la lista ORDER BY, posición 1.
En tu caso, 1/0es una expresión constante. Sin embargo, dado que calcular eso daría lugar a un error, SQL Server lo trata de manera diferente. Puede leer sobre eso en el artículo de Itzik Ben Gan Números de fila con orden no determinista :
Lo que pasa es que, por un lado, SQL Server no aplica el plegado constante y, por tanto, el ordenamiento se basa en una expresión que no es una única constante. Por otro lado, el optimizador calcula que el valor de orden es el mismo para todas las filas, por lo que ignora la expresión de orden por completo.
Puede ver eso en el plan de ejecución si ejecutamos la 1/0versión de la consulta con esas dos configuraciones desactivadas :
SET ANSI_WARNINGS, ARITHABORT OFF;
GO
SET STATISTICS XML ON;
GO
SELECT p.BusinessEntityID, p.FirstName
FROM Person.Person p
ORDER BY 1/0;
En este caso, puede ver que no hay una operación de clasificación. Compute Scalar intenta calcular 1/0, pero falla. Dado que esas dos configuraciones están desactivadas, el error "dividir por cero" se suprime para que la consulta se complete (con un orden de clasificación no determinista).
En el artículo Condiciones de búsqueda dinámica en T-SQL de Erland Sommarskog se analiza una mejor solución para la clasificación dinámica . La esencia de esa solución es usar una CASEdeclaración para transformar la columna de clasificación de entrada del usuario en un valor de columna conocido:
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
Esto evita que los valores inesperados afecten la ejecución de la consulta y ayuda a proteger contra la inyección de SQL.
No sé qué tan viable es este enfoque para usted, ya que parece estar tratando de mantener la compatibilidad entre múltiples plataformas de bases de datos.