ORDER BYCLAUSEでゼロ除算
WebアプリケーションからPENテストの結果を取得しました。テスターは、動的SQLステートメントのORDERBY句を使用して情報を取得できると述べました。並べ替える必要のある列の名前は、アプリによってクエリに渡されます。テスターは値を変更して、のような複雑なステートメントが含まれるようにしましたCASE WHEN ... THEN 1 ELSE 1/0 END。
ただし、非常に単純なテストケースを作成しました。OracleデータベースとSQLServerがあります。どちらにも同じテーブルが含まれています。問い合わせます
SELECT * FROM users ORDER BY 1/0
Oracleでこれを実行すると、クエリは正常に実行されます。SQLServerでエラーが発生します8134 Divide by zero error encountered.。
PEN-Testerは、現在使用しているものとは異なるOracleサーバーでアプリを使用しており、Oracleが最終的にエラーをスローしたという事実を悪用していると報告しているため、ORDERBY句の実行を妨げるOracle設定があるのではないかと思います。ゼロ除算に評価します。そのような設定はありますか？使用中のOracleバージョンの問題ですか？私は12.1.0.2.0例外がスローされないところにいます。SQLServerにもそのような設定はありますか？
もちろん、ステートメントの生成を変更するので、渡された値が並べ替えに使用できる列エイリアスであるかどうかを確認します。
回答
オラクル：
order by 1/0 その句だけでは意味がないため、オプティマイザは解析時にクエリから自動的に削除し、実行されることはありません。
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%')
ソートはまったく実行されませんでした。
他のものを追加すると失敗します：
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>
または：
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>
また、値が解析時に固定されていない場合（たとえば、変数である場合）：
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>
1/0具体的には、以下を実行して、エラーを防ぐことができSET文（私はそれらの設定をオフにすると、あなたの問題の「解決策」としてこれをお勧めしませんのでご注意を混乱と非表示に重要なエラーの多くを引き起こす可能性がありますが）。
SET ANSI_WARNINGS, ARITHABORT OFF;
このORDER BY句は、選択リスト内の列を、並べ替える順序位置で指定することをサポートしています。つまり、「ORDER BY 1」は、選択リストの最初の項目で注文することを意味します。
この例では、Microsoftの「AdventureWorks」サンプルデータベースを使用しています。
SELECT p.BusinessEntityID, p.FirstName
FROM Person.Person p
ORDER BY 2;
ただし、SQLServerは定数式をサポートしていません。
SELECT p.BusinessEntityID, p.FirstName
FROM Person.Person p
ORDER BY 2-1;
メッセージ408、レベル16、状態1、行18
ORDERBYリストの位置1で定数式が見つかりました。
あなたの場合、1/0は定数式です。ただし、それを計算するとエラーが発生するため、SQLServerでは処理が異なります。Itzik Ben Ganの記事「非決定的な順序の行番号」でそれについて読むことができます：
何が起こるかというと、一方でSQL Serverは定数畳み込みを適用できないため、順序は単一の定数ではない式に基づいています。一方、オプティマイザは、順序付けの値がすべての行で同じであると判断するため、順序付け式を完全に無視します。
これらの2つの設定をオフにし1/0てクエリのバージョンを実行すると、実行プランでそれを確認できます。
SET ANSI_WARNINGS, ARITHABORT OFF;
GO
SET STATISTICS XML ON;
GO
SELECT p.BusinessEntityID, p.FirstName
FROM Person.Person p
ORDER BY 1/0;
この場合、ソート操作がないことがわかります。Compute Scalarは計算を試み1/0ますが、失敗します。これらの2つの設定がオフになっているため、「ゼロ除算」エラーが抑制され、クエリが完了します（非決定性の並べ替え順序で）。
動的ソートのより良いソリューションについては、Erland Sommarskogの記事「T‑SQLの動的検索条件」で説明されています。その解決策の要点は、CASEステートメントを使用して、ユーザー入力の並べ替え列を既知の列値に変換することです。
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
これにより、予期しない値がクエリの実行に影響を与えるのを防ぎ、SQLインジェクションからの保護に役立ちます。
複数のデータベースプラットフォーム間の互換性を維持しようとしているように見えるため、このアプローチがどれほど実行可能かはわかりません。