Dividere per zero in ORDINE PER CLAUSOLA
Ho appena ricevuto un risultato del test PEN da un'applicazione web. Il tester ha dichiarato di essere in grado di utilizzare la clausola ORDER BY di un'istruzione SQL dinamica per recuperare le informazioni. Il nome della colonna che dovrebbe essere ordinata viene passato alla query da un'app. Il tester ha modificato il valore in modo che contenga un'istruzione complessa simile CASE WHEN ... THEN 1 ELSE 1/0 END.
Tuttavia, ho creato un test case molto semplice. Ho un database Oracle e un SQLServer. Entrambi contengono le stesse tabelle. Mi chiedo
SELECT * FROM users ORDER BY 1/0
Quando lo eseguo in Oracle, la query viene eseguita correttamente. In SQLServer ottengo un errore 8134 Divide by zero error encountered..
Poiché il PEN-Tester stava utilizzando l'app su un server Oracle diverso da quello che sto utilizzando ora e riferiscono di aver abusato del fatto che Oracle alla fine ha generato errori, mi chiedo se esiste un'impostazione Oracle che impedisce l'esecuzione delle clausole ORDER BY restituire una divisione per zero. Esiste un ambiente del genere? È una questione di versione Oracle in uso? Sono su 12.1.0.2.0dove non viene lanciata alcuna eccezione. Esiste una tale impostazione anche in SQLServer?
Ovviamente modificherò la generazione dell'istruzione, quindi controlla se i valori passati sono alias di colonna disponibili per l'ordinamento.
Risposte
Oracolo:
order by 1/0 riesce, poiché quella clausola da sola è priva di significato, l'ottimizzatore la elimina automaticamente dalla query in fase di analisi e non viene mai eseguita.
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%')
Non è stato eseguito alcun tipo.
Aggiungi qualcos'altro e fallirà:
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>
Anche se il valore non è fisso in fase di analisi (ad esempio è una variabile):
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>
In 1/0particolare, puoi prevenire l'errore eseguendo le seguenti SETistruzioni (tieni presente che non lo consiglio come "soluzione" al tuo problema, poiché disattivare queste impostazioni può causare molta confusione e nascondere errori importanti):
SET ANSI_WARNINGS, ARITHABORT OFF;
La ORDER BYclausola supporta la specifica di una colonna nell'elenco di selezione in base alla sua posizione ordinale in base alla quale ordinare. In altre parole, " ORDER BY 1" significa ordinare in base al primo elemento nell'elenco di selezione.
Questo esempio utilizza il database di esempio "AdventureWorks" di Microsoft:
SELECT p.BusinessEntityID, p.FirstName
FROM Person.Person p
ORDER BY 2;
SQL Server non supporta però le espressioni costanti :
SELECT p.BusinessEntityID, p.FirstName
FROM Person.Person p
ORDER BY 2-1;
Msg 408, livello 16, stato 1, riga 18
È stata rilevata un'espressione costante nell'elenco ORDER BY, posizione 1.
Nel tuo caso, 1/0è un'espressione costante. Tuttavia, poiché il calcolo potrebbe generare un errore, SQL Server lo considera in modo diverso. Puoi leggere a riguardo nell'articolo di Itzik Ben Gan Numeri di riga con ordine non deterministico :
Quello che succede è che da un lato SQL Server non riesce ad applicare la piegatura costante e quindi l'ordinamento si basa su un'espressione che non è una singola costante. D'altra parte, l'ottimizzatore calcola che il valore di ordinamento è lo stesso per tutte le righe, quindi ignora completamente l'espressione di ordinamento.
Puoi vederlo nel piano di esecuzione se eseguiamo la 1/0versione della query con queste due impostazioni disattivate :
SET ANSI_WARNINGS, ARITHABORT OFF;
GO
SET STATISTICS XML ON;
GO
SELECT p.BusinessEntityID, p.FirstName
FROM Person.Person p
ORDER BY 1/0;
In questo caso, puoi vedere che non è presente alcuna operazione di ordinamento. Compute Scalar tenta di calcolare 1/0, ma non riesce. Poiché queste due impostazioni sono disattivate, l'errore "divide per zero" viene eliminato in modo che la query venga completata (con un ordinamento non deterministico).
Una soluzione migliore per l'ordinamento dinamico è discussa nell'articolo di Erland Sommarskog Condizioni di ricerca dinamica in T ‑ SQL . L'essenza di tale soluzione è utilizzare CASEun'istruzione per trasformare la colonna di ordinamento dell'input dell'utente in un valore di colonna noto:
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
Ciò impedisce che valori imprevisti influiscano sull'esecuzione delle query e aiuta a proteggere dall'iniezione SQL.
Non so quanto sia fattibile questo approccio per te, dal momento che sembra che tu stia cercando di mantenere la compatibilità tra più piattaforme di database.