Teilen Sie in ORDER BY CLAUSE durch Null
Ich habe gerade ein PEN-Testergebnis von einer Webanwendung erhalten. Der Tester gab an, dass er die ORDER BY- Klausel einer dynamischen SQL-Anweisung zum Abrufen von Informationen verwenden konnte. Der Name der Spalte, die sortiert werden soll, wird von einer App an die Abfrage übergeben. Der Tester hat den Wert so geändert, dass er eine komplexe Anweisung enthält, die aussieht CASE WHEN ... THEN 1 ELSE 1/0 END.
Ich habe jedoch einen sehr einfachen Testfall erstellt. Ich habe eine Oracle-Datenbank und einen SQLServer. Beide enthalten die gleichen Tabellen. Ich frage
SELECT * FROM users ORDER BY 1/0
Wenn ich dies in Oracle ausführe, wird die Abfrage einwandfrei ausgeführt. In SQLServer erhalte ich eine Fehlermeldung 8134 Divide by zero error encountered..
Da der PEN-Tester die App auf einem anderen Oracle-Server verwendet hat als ich jetzt und sie berichten, dass sie die Tatsache missbraucht haben, dass Oracle letztendlich Fehler ausgelöst hat, frage ich mich, ob es eine Oracle-Einstellung gibt, die die Ausführung von ORDER BY- Klauseln verhindert zu einer Division durch Null auswerten. Gibt es eine solche Einstellung? Handelt es sich um die verwendete Oracle-Version? Ich bin dort, 12.1.0.2.0wo keine Ausnahme geworfen wird. Gibt es eine solche Einstellung auch in SQLServer?
Ich werde natürlich die Generierung der Anweisung ändern, damit überprüft wird, ob die übergebenen Werte Spaltenaliasnamen sind, die zum Sortieren verfügbar sind.
Antworten
Orakel:
order by 1/0 Dies ist erfolgreich, da diese Klausel allein bedeutungslos ist. Das Optimierungsprogramm entfernt sie automatisch zur Analysezeit aus der Abfrage und wird nie ausgeführt.
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%')
Es wurde überhaupt keine Sortierung durchgeführt.
Fügen Sie etwas anderes hinzu und es wird fehlschlagen:
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>
Oder:
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>
Auch wenn der Wert zur Analysezeit nicht festgelegt ist (z. B. eine 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>
Für 1/0Insbesondere können Sie den Fehler , indem Sie die folgenden verhindern SETAnweisungen (beachten Sie, dass ich empfehlen , dies nicht als eine „Lösung“ für das Problem, wie diese Einstellungen ausschalten viel Verwirrung verursachen können und verstecken wichtige Fehler):
SET ANSI_WARNINGS, ARITHABORT OFF;
Die ORDER BYKlausel unterstützt die Angabe einer Spalte in der Auswahlliste anhand ihrer Ordnungsposition, nach der sortiert werden soll. Mit anderen Worten ORDER BY 1bedeutet " ", nach dem ersten Element in der Auswahlliste zu bestellen.
In diesem Beispiel wird die Beispieldatenbank "AdventureWorks" von Microsoft verwendet:
SELECT p.BusinessEntityID, p.FirstName
FROM Person.Person p
ORDER BY 2;
SQL Server unterstützt jedoch keine konstanten Ausdrücke :
SELECT p.BusinessEntityID, p.FirstName
FROM Person.Person p
ORDER BY 2-1;
Nachricht 408, Ebene 16, Status 1, Zeile 18
In der ORDER BY-Liste, Position 1, wurde ein konstanter Ausdruck gefunden.
In Ihrem Fall 1/0ist ein konstanter Ausdruck. Da die Berechnung jedoch zu einem Fehler führen würde, behandelt SQL Server diesen anders. Sie können darüber in Itzik Ben Gans Artikel Zeilennummern mit nicht deterministischer Reihenfolge lesen :
Was passiert, ist, dass SQL Server einerseits keine konstante Faltung anwendet und die Reihenfolge daher auf einem Ausdruck basiert, der keine einzelne Konstante ist. Auf der anderen Seite stellt der Optimierer fest, dass der Ordnungswert für alle Zeilen gleich ist, sodass der Ordnungsausdruck insgesamt ignoriert wird.
Sie können dies im Ausführungsplan sehen, wenn wir die 1/0Version der Abfrage mit diesen beiden Einstellungen ausführen :
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 diesem Fall sehen Sie, dass keine Sortieroperation vorhanden ist. Der Berechnungsskalar versucht zu berechnen 1/0, schlägt jedoch fehl. Da diese beiden Einstellungen deaktiviert sind, wird der Fehler "Teilen durch Null" unterdrückt, sodass die Abfrage abgeschlossen ist (mit einer nicht deterministischen Sortierreihenfolge).
Eine bessere Lösung für die dynamische Sortierung wird in Erland Sommarskogs Artikel Dynamische Suchbedingungen in T-SQL erläutert . Der Kern dieser Lösung besteht darin, eine CASEAnweisung zu verwenden, um die Sortierspalte für Benutzereingaben in einen bekannten Spaltenwert umzuwandeln:
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
Dies verhindert, dass unerwartete Werte die Ausführung von Abfragen beeinflussen, und schützt vor SQL-Injection.
Ich weiß nicht, wie praktikabel dieser Ansatz für Sie ist, da Sie anscheinend versuchen, die Kompatibilität zwischen mehreren Datenbankplattformen aufrechtzuerhalten.