หารด้วยศูนย์ใน ORDER BY CLAUSE

Question

ฉันเพิ่งได้รับผลการทดสอบ PEN จากเว็บแอปพลิเคชัน ผู้ทดสอบระบุว่าพวกเขาสามารถใช้คำสั่ง ORDER BYของคำสั่ง SQL แบบไดนามิกเพื่อดึงข้อมูล ชื่อของคอลัมน์ที่ควรจัดเรียงจะถูกส่งไปยังคิวรีโดยแอป 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 ส่งข้อผิดพลาดในที่สุดฉันสงสัยว่ามีการตั้งค่า Oracle ที่ป้องกันไม่ให้ดำเนินการตามคำสั่ง ORDER BY หรือไม่ประเมินผลการหารด้วยศูนย์ มีการตั้งค่าดังกล่าวหรือไม่? เป็นเรื่องของ Oracle Version ที่ใช้งานอยู่หรือไม่? ฉันอยู่12.1.0.2.0ที่ไหนก็ไม่มีข้อยกเว้น มีการตั้งค่าดังกล่าวใน SQLServer ด้วยหรือไม่?

แน่นอนฉันจะแก้ไขการสร้างคำสั่งดังนั้นจึงตรวจสอบว่าค่าที่ส่งผ่านเป็นนามแฝงคอลัมน์ที่พร้อมใช้งานสำหรับการเรียงลำดับหรือไม่

Answer 1

Oracle:

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>

Answer 2

สำหรับ1/0เฉพาะคุณสามารถป้องกันข้อผิดพลาดโดยการทำงานดังต่อไปนี้SETงบ (ทราบว่าผมไม่แนะนำให้นี้เป็น "การแก้ปัญหา" ในการแก้ไขปัญหาของคุณเช่นการเปลี่ยนการตั้งค่าเหล่านั้นออกมากอาจทำให้เกิดความสับสนและซ่อนข้อผิดพลาดที่สำคัญ):

SET ANSI_WARNINGS, ARITHABORT OFF;

ORDER BYข้อสนับสนุนการระบุคอลัมน์ในรายการเลือกโดยตำแหน่งลำดับของมันจะเรียงลำดับตาม กล่าวอีกนัยหนึ่ง " ORDER BY 1" หมายถึงการเรียงลำดับตามรายการแรกในรายการเลือก

ตัวอย่างนี้ใช้ฐานข้อมูลตัวอย่าง "AdventureWorks" จาก Microsoft:

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

SQL Server ไม่สนับสนุนนิพจน์คงที่แม้ว่า:

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

ข่าวสารเกี่ยวกับ 408 ระดับ 16 สถานะ 1 บรรทัด 18
พบนิพจน์คงที่ในรายการ ORDER BY ตำแหน่ง 1

ในกรณีของคุณ1/0คือนิพจน์คงที่ อย่างไรก็ตามเนื่องจากการคำนวณนั้นจะทำให้เกิดข้อผิดพลาด SQL Server จึงถือว่าแตกต่างกัน คุณสามารถอ่านเกี่ยวกับเรื่องนี้ได้ในบทความของ Itzik Ben Gan หมายเลขแถวที่ไม่มีลำดับขั้นต่ำ :

สิ่งที่เกิดขึ้นคือในแง่หนึ่ง SQL Server ไม่สามารถใช้การพับแบบคงที่ดังนั้นการจัดลำดับจะขึ้นอยู่กับนิพจน์ที่ไม่ใช่ค่าคงที่เดียว ในทางกลับกันเครื่องมือเพิ่มประสิทธิภาพจะคิดว่าค่าการสั่งซื้อเหมือนกันสำหรับทุกแถวดังนั้นจึงไม่สนใจนิพจน์การสั่งซื้อทั้งหมด

คุณจะเห็นได้ว่าในแผนการดำเนินการหากเราเรียกใช้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แต่ล้มเหลว เนื่องจากการตั้งค่าทั้งสองนี้ปิดอยู่ข้อผิดพลาด "หารด้วยศูนย์" จึงถูกระงับเพื่อให้การสืบค้นเสร็จสมบูรณ์

---

วิธีการแก้ปัญหาที่ดีกว่าสำหรับการเรียงลำดับแบบไดนามิกที่กล่าวถึงในบทความ 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

ฉันไม่รู้ว่าแนวทางนี้เหมาะกับคุณแค่ไหนเนื่องจากดูเหมือนว่าคุณพยายามรักษาความเข้ากันได้ระหว่างแพลตฟอร์มฐานข้อมูลหลายแพลตฟอร์ม