Divide by zero in ORDER BY CLAUSE 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.
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&gt; 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&gt; 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(&quot;USERNAME&quot; LIKE 'SYS%')

No sort at all was performed.
Add something else and it will fail:
SQL&gt; 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&gt;

Or:
SQL&gt;  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&gt; 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&gt;

Also if the value is not fixed at parse time (e.g it is a variable) :
SQL&gt; variable B1 number
SQL&gt; exec :B1 := 0;

PL/SQL procedure successfully completed.

SQL&gt; 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&gt;