Automic Workload Automation

Expand all | Collapse all

XML VARA objects & Xpath expressions

  • 1.  XML VARA objects & Xpath expressions

    Posted 03-04-2019 10:34 AM

    Both GET_VAR and PREP_PROCESS_VAR_XML allow one read at subset of data from XML VARA object by specifying an Xpath or XQuery expression.

     

    I have discovered that not all valid Xpath expressions will work in these commands. The reason is related to how the expression is inserted into the SQL statement before being executed against the DB.

     

    In the examples below, I used GET_VAR.

     

    Xpath expression

    //@*/concat(name(.),';',.)

    This Xpath expression should generate a semicolon-delimited list of attribute names and values.

     

    Result

    U00003758 Error in xml-variable:
    'U00003590 UCUDB - DB error: 'OCIStmtExecute', 'ERROR   ', '',
    'ORA-31013: Invalid XPATH expression'

     

    Query made by WP to the DB

    SELECT T.X OVW_VALUEX
    FROM OVW ,
    XMLTABLE ('//@*/concat(name(.),'=',.)'
              passing OVW_ValueX COLUMNS X varchar2(4000) PATH '.')
              AS T
    WHERE OVW_OH_Idnr = ?
    AND OVW_VValue = ?

    (Oracle SQL; Newline characters & leading spaces added to improve readability.)

     

    A few things to note here:

    • It seems the Xpath expression is inserted directly into the SQL. This is risky and presents a risk of SQL injection.
    • The XML is being cast to a VARCHAR, effectively limiting the length of XML that can be processed. (See KB000121626.)
    • In this context, the single quotation mark character preceding the semicolon in the expression effectively ends the Xpath expression.

    I was able to get this particular expression working by replacing the single quotation mark characters in the expression with double quotation mark characters. I.e.,

    //@*/concat(name(.),";",.)

    This worked fine, but stopped working again after I upgraded the system.

    Newer versions of the Automation Engine use a different SQL statement for GET_VAR:

    select * from
       (SELECT to_clob(extract(OVW_ValueX,'//@*/concat(name(.),";",.)')) OVW_VALUEX,
        OVW_OH_Idnr, OVW_VValue, OVW_OH_Client, OVW_VRName
    FROM  OVW
    WHERE OVW_OH_Idnr = ?
    AND OVW_VValue = ? )
    where rownum < ?

    This query should eliminate the aforementioned problem with large XML.

     

    From the trace, I can see that the SQL is being built in multiple stages:

    1. The Xpath expression is inserted into this SQL fragment:
      to_clob(extract(OVW_ValueX,'?'))
      This results in the string:
      to_clob(extract(OVW_ValueX,'//@*/concat(name(.),";",.)'))
    2. This string is inserted into the first bind parameter of the main SQL query (The other 3 parameters are the object ID of the VARA, the VARA key, and the number 2):
      select * from (SELECT ? OVW_VALUEX, OVW_OH_Idnr, OVW_VValue, OVW_OH_Client, OVW_VRName FROM OVW WHERE OVW_OH_Idnr = ? AND OVW_VValue = ? ) where rownum < ?
    3. Finally, this SQL is run against the DB.

     

    My testing revealed that it’s easy to mess up the resultant SQL by manipulating the Xpath expression. This causes errors like these:

    • U00003590 UCUDB - DB error: 'OCIStmtExecute', 'ERROR   ', '', 'ORA-31013: Invalid XPATH expression'
    • U00003590 UCUDB - DB error: 'OCIBindByPos', 'ERROR   ', '', 'ORA-01036: illegal variable name/number'
    • UCUDB - DB error: 'OCIStmtExecute', 'ERROR   ', '', 'ORA-00936: missing expression'

    Moreover, a great number of valid Xpath expressions simply do not work. I opened a case with CA support about this, but they simply told me that it depends on the back-end database, and that it’s up to me to find a way to get the Xpath expressions to work.

     

    I have not tested whether SQL injection is possible.  It is not.



  • 2.  Re: XML VARA objects & Xpath expressions

    Posted 03-04-2019 11:55 AM

    I did a quick test of SQL injection, and the replacement was rejected.

    U00029108 UCUDB: SQL_ERROR    Database handles  DB-HENV: c095c0  DB-HDBC: c7f3f8
    U00003591 UCUDB - DB error info: OPC: 'OCIBindByPos' Return code: 'ERROR'
    U00003592 UCUDB - Status: '' Native error: '1036' Msg: 'ORA-01036: illegal variable name/number'

    This is good, obviously.