Разделить на ноль в ORDER BY CLAUSE
Я только что получил результат 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? Я 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» означает упорядочивание по первому элементу в списке выбора.
В этом примере используется образец базы данных «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 обрабатывает ее по-другому. Вы можете прочитать об этом в статье Ицика Бен Гана Номера строк с недетерминированным порядком :
С одной стороны, 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, но терпит неудачу. Поскольку эти два параметра отключены, ошибка «делить на ноль» подавляется, поэтому запрос завершается (с недетерминированным порядком сортировки).
Лучшее решение для динамической сортировки обсуждается в статье Эрланда Соммарскога « Условия динамического поиска в 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.
Я не знаю, насколько этот подход жизнеспособен для вас, поскольку вы, похоже, пытаетесь поддерживать совместимость между несколькими платформами баз данных.