Oracle DBMS_XSLPROCESSOR Built-In Package
Versions 10.1 - 19.3

Security Advisory
Far too often we find that powerful tools created for legitimate business purposes can be misused to breach security.

In the case of the DBMS_XSLPROCESSOR package, documented by Oracle for customer use, the stated purpose is to provide an interface to manage the contents and structure of XML documents. Sounds perfectly harmless and as you will see below, EXECUTE is granted to PUBLIC.

The vulnerability introduced with this package relates to a single object named CLOB2FILE. Simply put ... if I can get data from the database into a variable of CLOB type ... I can use this capability to write it to a file system from which it can later be retrieved.

In tests we have used this package to export 200,000 lines of Oracle Database source code in less than one second.
 
Recommended Security Rules

 NEVER
  • Allow this package in a production database with EXECUTE granted to PUBLIC ... revoke the default Oracle grant
 WITH GREAT CARE
  • Grant EXECUTE on this package to specific application users only after it is demonstrated a compelling need
 CAUTIONS
  • EXECUTE grants, even when not to PUBLIC are equivalent to punching a hole through a firewall. Make the decision with great care and document the justification.
 
How Oracle Works
A database, every database, is of zero value if you cannot import data from one or more sources and if you cannot export data in the form of backups, integration APIs, files, and reports. Oracle is the world's most feature rich database environment and you cannot say that without simultaneously acknowledging that some of that power comes from the richness of features such as the one provided by DBMS_XSLPROCESSOR.

Using a feature rich environment however, comes with responsibilities. If there is a means for getting data out of the database it is your responsibility as an implementer to be aware of it, to understand how it works, and to secure it. This page will help you perform that function.
 
DBMS_XSLPROCESSOR Package Information
AUTHID CURRENT_USER
Constants
Name Data Type Value
 Internal Error
INTERNAL_ERR NUMBER -20000
 Other Errors
PROCESSOR_ERR NUMBER -20100
FILE_ERR NUMBER -20101
CONN_ERR NUMBER -20102
NULL_ERR NUMBER -20103
Data Types -- processor interface type
TYPE Processor IS RECORD(id IN dbms_xmldom.domtype);

-- stylesheet interface type
TYPE Stylesheet IS RECORD(id dbms_xmldom.domtype);
Dependencies
DBMS_CLOBUTIL DBMS_XDBUTIL_INT UTL_FILE
DBMS_CSX_INT DBMS_XMLDOM UTL_RAW
DBMS_CUBE DBMS_XSLPROCESSOR_LIB XMLDOM
DBMS_LOB URITYPE XSLPROCESSOR
Documented Yes
Exceptions
Error Code Reason
ORA-20000 INTERNAL_ERR
ORA-20100 PROCESSOR_ERR
ORA-20101 FILE_ERR
ORA-20102 CONN_ERR
ORA-20103 NULL_ERR
First Available 10.1
Security Model Owned by XDB with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmsxslp.sql
Subprograms
 
CLOB2FILE
Write from a CLOB to a file with given character encoding.

If csid is zero or not provided (NULL) the file will be written in the database's character set.
dbms_xslprocessor.clob2file(
cl        IN CLOB,
flocation IN VARCHAR2,      -- file directory
fname     IN VARCHAR2,      -- file name
csid      IN NUMBER := 0);  -- character set id of the file
col directory_path format a50

SELECT *
FROM all_directories;

EXPLAIN PLAN
SET STATEMENT_ID = 'c2f' FOR
SELECT DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;

SELECT dbms_xplan.display_plan(statement_id => 'c2f') AS XPLAN
FROM dual;

set serveroutput on

DECLARE
 xlob CLOB;
BEGIN
  SELECT dbms_xplan.display_plan(statement_id => 'c2f') AS XPLAN
  INTO xlob
  FROM dual;

  dbms_xslprocessor.clob2file(xlob, 'EXT', 'democlob.txt');

  xlob := dbms_xslprocessor.read2clob('EXT', 'democlob.txt');
  dbms_output.put_line(xlob);
END;
/
 
FREEPROCESSOR
Free XSL Processor dbms_xslprocessor.freeProcessor(p IN processor);
DECLARE
 proc dbms_xslprocessor.processor;
BEGIN
  proc := dbms_xslprocessor.newprocessor;
  dbms_xslprocessor.freeProcessor(proc);
END;
/
 
FREESTYLESHEET
Frees a stylesheet object dbms_xslprocessor.freeStyleSheet(ss IN stylesheet);
TBD
 
NEWPROCESSOR
Returns a new processor instance dbms_xslprocessor.newProcessor RETURN processor;
See FREEPROCESSOR Demo Above
 
NEWSTYLESHEET
Create a new stylesheet using the given DOMDocument and base directory URL
Overload 1
dbms_xslprocessor.newStyleSheet(
xmldoc IN dbms_xmldom.DOMDocument,
refurl IN VARCHAR2)
RETURN stylesheet;
TBD
Create a new stylesheet using the given input file and base directory URLs
Overload 2
dbms_xslprocessor.newStyleSheet(
inp    IN VARCHAR2,
refurl IN VARCHAR2)
RETURN Stylesheet;
TBD
 
PROCESSXSL
Transforms input XML document using given DOMDocument and stylesheet

Overload 1
dbms_xslprocessor.processXSL(
p      IN processor,
ss     IN stylesheet,
xmldoc IN dbms_xmldom.DOMDocument)
RETURN dbms_xmldom.DOMDocumentFragment;
TBD
Transforms input XML document using given Doc as CLOB

Overload 2
dbms_xslprocessor.processXSL(
p  IN processor,
ss IN stylesheet,
cl IN CLOB)
RETURN dbms_xmldom.DOMDocumentFragment;
TBD
Transforms input XML document using given DOMDocument and stylesheet and writes output to a file

Overload 3
dbms_xslprocessor.processXSL(
p        IN processor,
ss       IN stylesheet,
xmldoc   IN dbms_xmldom.DOMDocument,
dir      IN VARCHAR2,
fileName IN VARCHAR2);
TBD
Transforms input XML document using given as URL and stylesheet and writes output to a file

Overload 4
dbms_xslprocessor.processXSL(
p        IN processor,
ss       IN stylesheet,
url      IN VARCHAR2,
dir      IN VARCHAR2,
fileName IN VARCHAR2);
TBD
Transforms input XML document using given DOMDocument and stylesheet and writes output to a buffer

Overload 5
dbms_xslprocessor.processXSL(
p      IN     processor,
ss     IN     stylesheet,
xmldoc IN     dbms_xmldom.DOMDocument,
buffer IN OUT VARCHAR2);
TBD
Transforms input XML document using given DOMDocument and stylesheet and writes output to a CLOB

Overload 6
dbms_xslprocessor.processXSL(
p      IN     processor,
ss     IN     stylesheet,
xmldoc IN     dbms_xmldom.DOMDocument,
cl     IN OUT CLOB);
TBD
Transforms input XML document using given DOMDocument and stylesheet and writes output to a CLOB. Provides information if style sheet output method is xml or not.

Overload 7
dbms_xslprocessor.processXSL(
p           IN     processor,
ss          IN     stylesheet,
xmldoc      IN     dbms_xmldom.DOMDocument,
cl          IN OUT CLOB,
isoutputxml    OUT BOOLEAN);
TBD
Transforms input XML document fragment using given DOMDocumentFragment and
stylesheet

Overload 8
dbms_xslprocessor.processXSL(
p      IN processor,
ss     IN stylesheet,
xmldf  IN dbms_xmldom.DOMDocumentFragment)
RETURN dbms_xmldom.DOMDocumentFragment;
TBD
Transforms input XML document fragment using given DOMDocumentFragment and stylesheet and writes output to a file

Overload 9
dbms_xslprocessor.processXSL(
p        IN processor,
ss       IN stylesheet,
xmldf    IN dbms_xmldom.DOMDocumentFragment,
dir      IN VARCHAR2,
fileName IN VARCHAR2);
TBD
Transforms input XML document fragment using given DOMDocumentFragment and stylesheet and writes output to a buffer

Overload 10
dbms_xslprocessor.processXSL(
p      IN     processor,
ss     IN     stylesheet,
xmldf  IN     dbms_xmldom.DOMDocumentFragment,
buffer IN OUT VARCHAR2);
TBD
Transforms input XML document fragment using given DOMDocumentFragment and stylesheet and writes output to a CLOB

Overload 11
dbms_xslprocessor.processXSL(
p     IN     processor,
ss    IN     stylesheet,
xmldf IN     dbms_xmldom.DOMDocumentFragment,
cl    IN OUT CLOB);
TBD
 
READ2CLOB
Read from a file to a CLOB and returns a CLOB dbms_xslprocessor.read2clob(
flocation IN VARCHAR2,
fname     IN VARCHAR2,
csid      IN NUMBER := 0)
RETURN CLOB;
See CLOB2FILE Demo Above
 
REMOVEPARAM
Remove a top-level stylesheet parameter dbms_xslprocessor.removeParam(
ss   IN stylesheet,
name IN VARCHAR2);
TBD
 
RESETPARAMS
Resets the top-level stylesheet parameters dbms_xslprocessor.resetParams(ss IN stylesheet);
TBD
 
SELECTNODES
Selects nodes from the tree which match the given pattern dbms_xslprocessor.selectNodes(
n         IN dbms_xmldom.DOMNode,
pattern   IN VARCHAR2,
namespace IN VARCHAR2 := NULL)
RETURN dbms_xmldom.DOMNodeList;
See ValueOf Overload 1 Demo Below
 
SELECTSINGLENODE
Selects the first node from the tree that matches the given pattern dbms_xslprocessor.selectSingleNode(
n         IN dbms_xmldom.DOMNode,
pattern   IN VARCHAR2,
namespace IN VARCHAR2 := NULL)
RETURN dbms_xmldom.DOMNode;
TBD
 
SETERRORLOG
Sets errors to be sent to the specified file dbms_xslprocessor.setErrorLog(
p        IN processor,
fileName IN VARCHAR2);
Deprecated
 
SETPARAM
Sets the value of a top-level stylesheet parameter. The parameter value is expected to be a valid XPath expression (note that string literal values would therefore have to be explicitly quoted) dbms_xslprocessor.setParam(
ss   IN stylesheet,
name IN VARCHAR2,
val  IN VARCHAR2);
TBD
 
SHOWWARNINGS
Sets warnings TRUE - on, FALSE - off dbms_xslprocessor.showWarnings(
p   IN processor,
yes IN BOOLEAN);
TBD
 
TRANSFORMNODE
Transforms a node in the tree using the given stylesheet dbms_xslprocessor.transformNode(
n  IN dbms_xmldom.DOMNode,
ss IN stylesheet)
RETURN dbms_xmldom.DOMDocumentFragment;
TBD
 
VALUEOF
Retrieves the value of the first node from the tree that matches the given pattern

Overload 1
dbms_xslprocessor.valueOf(
n         IN  dbms_xmldom.DOMNode,
pattern   IN  VARCHAR2,
val       OUT VARCHAR2,
namespace IN  VARCHAR2 := NULL);
DECLARE
 TYPE tab_type IS TABLE OF tab_ide%ROWTYPE;
 t_tab tab_type := tab_type();

 v_n             dbms_xmldom.DOMNode;
 v_dom           xmldom.domdocument;
 v_item          xmldom.domnodelist;
 v_node          xmldom.DOMNode;
 v_demo_end_dest VARCHAR2(40) := '/UW/info/dest/enderDest';

 FUNCTION f_doc_dom RETURN xmldom.domdocument IS
  v_xml    XMLTYPE;
  v_parser dbms_xmlparser.parser;
  v_xmldoc XMLDOM.DOMDocument;
 BEGIN
   SELECT xml
   INTO v_xml
   FROM tbl_xml;

   v_parser := dbms_xmlparser.newParser;
   dbms_xmlparser.setValidationMode(v_parser, FALSE);
   dbms_xmlparser.parseCLOB(v_parser, v_xml.getCLOBVal());
   V_XMLDOC := dbms_xmlparser.getDocument(v_parser);
   dbms_xmlparser.freeParser(v_parser);
   RETURN v_xmldoc;
 EXCEPTION
   WHEN OTHERS THEN
     RAISE;
 END f_doc_dom;
BEGIN
  v_dom := f_doc_dom;
  v_node := xmldom.makenode(v_dom);
  v_item := dbms_xslprocessor.selectNodes(v_node,'//uw/info/ide');

  FOR cur_emp IN 0 .. dbms_xmldom.getLength(v_item)-1 LOOP
    t_tab.EXTEND;
    v_n := dbms_xmldom.item(v_item, cur_emp);

    dbms_xslprocessor.valueOf(v_n, v_demo_end_dest || 'xCpl/text()', t_tab(t_tab.last).enddest_xcpl);
  END LOOP;
END;
/
Overload 2 dbms_xslprocessor.valueOf(
n         IN xmldom.DOMNode,
pattern   IN VARCHAR2,
namespace IN VARCHAR2 := NULL)
RETURN VARCHAR2;
See DBMS_XMLPARSER demo using the link below.

Related Topics
DBMS_SQL
DBMS_XMLPARSER
Object Privileges
System Privileges