Divide by zero in ORDER BY CLAUSE

Question

I just got a PEN-Test result from a web application. The tester stated they were able to use the ORDER BY clause of a dynamic SQL statement to retrieve information. The name of the column that should be sorted is passed into the query by an app. The tester modified the value so it contains a complex statement that looks like CASE WHEN ... THEN 1 ELSE 1/0 END.

However, I built a very simple test case. I have an Oracle database and a SQLServer. Both contain the same tables. I query

SELECT * FROM users ORDER BY 1/0

When I execute this in Oracle the query executes fine. In SQLServer I get an error 8134 Divide by zero error encountered..

As the PEN-Tester was using the app on a different Oracle server than I'm using now and they report they were abusing the fact that Oracle eventually threw errors, I wonder if there's an Oracle setting that prevents the execution of ORDER BY clauses that evaluate to a division by zero. Is there such a setting? Is it a matter of Oracle Version in use? I'm on 12.1.0.2.0 where no exception is thrown. Is there such a setting in SQLServer, too?

I will of course modify the generation of the statement, so it checks if the values that are passed in are column aliases available for sorting.

Answer 1

Oracle:

order by 1/0 succeeds, because that clause alone is meaningless, the optimizer automatically eliminates it from the query at parse time and it never gets executed.

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 sort at all was performed.

Add something else and it will fail:

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>

Or:

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>

Also if the value is not fixed at parse time (e.g it is a 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>

Answer 2

For 1/0 specifically, you can prevent the error by running the following SET statements (note that I don't recommend this as a "solution" to your problem, as turning those settings off can cause a lot of confusion and hide important errors):

SET ANSI_WARNINGS, ARITHABORT OFF;

The ORDER BY clause supports specifying a column in the select list by it's ordinal position to sort by. In other words, "ORDER BY 1" means to order by the first item in the select list.

This example uses the "AdventureWorks" sample database from Microsoft:

SELECT p.BusinessEntityID, p.FirstName 
FROM Person.Person p 
ORDER BY 2;

SQL Server does not support constant expressions though:

SELECT p.BusinessEntityID, p.FirstName 
FROM Person.Person p 
ORDER BY 2-1;

Msg 408, Level 16, State 1, Line 18
A constant expression was encountered in the ORDER BY list, position 1.

In your case, 1/0 is a constant expression. However, since calculating that would result in an error, SQL Server treats it differently. You can read about that in Itzik Ben Gan's article Row numbers with nondeterministic order:

What happens is that on one hand, SQL Server fails to apply constant folding, and therefore the ordering is based on an expression that is not a single constant. On the other hand, the optimizer figures that the ordering value is the same for all rows, so it ignores the ordering expression altogether.

You can see that in the execution plan if we run the 1/0 version of the query with those two settings off:

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 this case, you can see there is not sort operation. The Compute Scalar attempts to calculate 1/0, but fails. Since those two settings are off, the "divide by zero" error is suppressed so the query completes (with a nondeterministic sort order).

---

A better solution for dynamic sorting is discussed in Erland Sommarskog's article Dynamic Search Conditions in T‑SQL. The gist of that solution is to use a CASE statement to transform the user input sort column into a known column value:

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

This prevents unexpected values from affecting query execution, and helps protect against SQL injection.

I don't know how viable this approach is for you, since you appear to be trying to maintain compatibility between multiple database platforms.