Bagilah dengan nol di ORDER BY CLAUSE
Saya baru saja mendapat hasil PEN-Test dari aplikasi web. Penguji menyatakan bahwa mereka dapat menggunakan klausa ORDER BY dari pernyataan SQL dinamis untuk mengambil informasi. Nama kolom yang harus diurutkan diteruskan ke dalam kueri oleh aplikasi. Penguji memodifikasi nilainya sehingga berisi pernyataan kompleks yang terlihat seperti itu CASE WHEN ... THEN 1 ELSE 1/0 END
.
Namun, saya membuat kasus uji yang sangat sederhana. Saya memiliki database Oracle dan SQLServer. Keduanya berisi tabel yang sama. Saya bertanya
SELECT * FROM users ORDER BY 1/0
Ketika saya menjalankan ini di Oracle, kueri dijalankan dengan baik. Di SQLServer saya mendapatkan error 8134 Divide by zero error encountered.
.
Karena PEN-Tester menggunakan aplikasi pada server Oracle yang berbeda dari yang saya gunakan sekarang dan mereka melaporkan bahwa mereka menyalahgunakan fakta bahwa Oracle pada akhirnya melakukan kesalahan, saya bertanya-tanya apakah ada pengaturan Oracle yang mencegah eksekusi klausa ORDER BY yang mengevaluasi ke pembagian dengan nol. Apakah ada pengaturan seperti itu? Apakah ini masalah Versi Oracle yang digunakan? Aku berada di 12.1.0.2.0
tempat yang tidak terkecuali dilemparkan. Apakah ada pengaturan seperti itu di SQLServer juga?
Saya tentu saja akan memodifikasi pembuatan pernyataan, sehingga memeriksa apakah nilai yang diteruskan adalah alias kolom tersedia untuk diurutkan.
Jawaban
Peramal:
order by 1/0
berhasil, karena klausa itu sendiri tidak ada artinya, pengoptimal secara otomatis menghilangkannya dari kueri pada waktu parse dan tidak pernah dijalankan.
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%')
Tidak ada jenis sama sekali yang dilakukan.
Tambahkan sesuatu yang lain dan itu akan gagal:
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>
Atau:
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>
Juga jika nilainya tidak ditetapkan pada waktu parse (misalnya, ini adalah variabel):
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>
Untuk 1/0
khusus, Anda dapat mencegah kesalahan dengan menjalankan berikut SET
pernyataan (catatan bahwa saya tidak merekomendasikan ini sebagai "solusi" untuk masalah Anda, seperti mengubah pengaturan tersebut off dapat menyebabkan banyak kebingungan dan menyembunyikan kesalahan penting):
SET ANSI_WARNINGS, ARITHABORT OFF;
The ORDER BY
mendukung klausa menentukan kolom dalam daftar pilih dengan itu posisi ordinal sortir. Dengan kata lain, " ORDER BY 1
" berarti memesan berdasarkan item pertama dalam daftar pilihan.
Contoh ini menggunakan database contoh "AdventureWorks" dari Microsoft:
SELECT p.BusinessEntityID, p.FirstName
FROM Person.Person p
ORDER BY 2;

SQL Server tidak mendukung ekspresi konstan :
SELECT p.BusinessEntityID, p.FirstName
FROM Person.Person p
ORDER BY 2-1;
MSG 408, Level 16, Status 1, Baris 18
Ekspresi konstan ditemukan dalam daftar ORDER BY, posisi 1.
Dalam kasus Anda, 1/0
adalah ekspresi konstan. Namun, karena menghitung itu akan menghasilkan kesalahan, SQL Server memperlakukannya secara berbeda. Anda dapat membaca tentang itu di artikel Itzik Ben Gan Nomor baris dengan urutan nondeterministik :
Apa yang terjadi adalah bahwa di satu sisi, SQL Server gagal menerapkan pelipatan konstan, dan oleh karena itu pengurutan didasarkan pada ekspresi yang bukan merupakan konstanta tunggal. Di sisi lain, pengoptimal memperkirakan bahwa nilai pengurutan sama untuk semua baris, sehingga mengabaikan ekspresi pengurutan sama sekali.
Anda dapat melihatnya di rencana eksekusi jika kami menjalankan 1/0
versi kueri dengan kedua setelan tersebut dinonaktifkan :
SET ANSI_WARNINGS, ARITHABORT OFF;
GO
SET STATISTICS XML ON;
GO
SELECT p.BusinessEntityID, p.FirstName
FROM Person.Person p
ORDER BY 1/0;

Dalam kasus ini, Anda dapat melihat tidak ada operasi pengurutan. The Compute Scalar mencoba menghitung 1/0
, tetapi gagal. Karena kedua setelan tersebut tidak aktif, kesalahan "bagi dengan nol" disembunyikan sehingga kueri selesai (dengan urutan sortir non-deterministik).
Solusi yang lebih baik untuk penyortiran dinamis dibahas dalam artikel Erland Sommarskog tentang Kondisi Penelusuran Dinamis di T ‑ SQL . Inti dari solusi itu adalah menggunakan CASE
pernyataan untuk mengubah kolom sortir masukan pengguna menjadi nilai kolom yang diketahui:
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
Ini mencegah nilai tak terduga memengaruhi eksekusi kueri, dan membantu melindungi dari injeksi SQL.
Saya tidak tahu seberapa layak pendekatan ini untuk Anda, karena Anda tampaknya berusaha menjaga kompatibilitas antara beberapa platform database.