ORDER BY CLAUSE에서 0으로 나누기
방금 웹 응용 프로그램에서 PEN-Test 결과를 얻었습니다. 테스터는 동적 SQL 문의 ORDER BY 절 을 사용하여 정보를 검색 할 수 있다고 말했습니다 . 정렬해야하는 열의 이름은 앱에 의해 쿼리로 전달됩니다. 테스터는 다음과 같은 복잡한 문을 포함하도록 값을 수정했습니다 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이 결국 오류를 던졌다는 사실을 악용하고 있다고보고하므로 ORDER BY 절의 실행을 방지하는 Oracle 설정이 있는지 궁금 합니다. 0으로 나눈 값으로 평가됩니다. 그런 설정이 있습니까? 사용중인 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;
SQL Server는 다음과 같은 상수 식을 지원하지 않습니다 .
SELECT p.BusinessEntityID, p.FirstName
FROM Person.Person p
ORDER BY 2-1;
Msg 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하지만 실패합니다. 이 두 설정이 꺼져 있으므로 "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 주입으로부터 보호하는 데 도움이됩니다.
여러 데이터베이스 플랫폼 간의 호환성을 유지하려는 것처럼 보이기 때문에이 접근 방식이 얼마나 실행 가능한지 모르겠습니다.