How to correctly pass a string argument to XPATH in Oracle?

Question

How to pass a string argument to EXTRACTVALUE?

For instance:

EXTRACTVALUE(fooColumn, '/foo/bar[@baz = "arg"]/@value')

How to replace "arg" with a real argument :arg?

Naive solution:

EXTRACTVALUE(fooColumn, '/foo/bar[@baz = "' || :arg || '"]/@value')

How to prevent possible "XPath" injection here? Is there a XPath escape function?

Answer 1

EXTRACTVALUE is deprecated and you should be using XMLQUERY or XMLTABLE instead.

You can try using the DBMS_ASSERT package:

CREATE FUNCTION enquote_name (
  name IN VARCHAR2
) RETURN VARCHAR2
IS
BEGIN
  -- Wrap it in a function call to use the PL/SQL FALSE literal.
  RETURN DBMS_ASSERT.ENQUOTE_NAME(name, FALSE);
END;
/

Then:

SELECT XMLQUERY(
         ('/foo/bar[@baz='||ENQUOTE_NAME(:arg)||']/@value')
         PASSING fooColumn
         RETURNING CONTENT
       ) AS value
FROM   table_name

or

SELECT EXTRACTVALUE( foocolumn, '/foo/bar[@baz='||ENQUOTE_NAME(:arg)||']/@value')
         AS value
FROM   table_name

Which for the table:

CREATE TABLE table_name ( foocolumn ) AS
SELECT XMLTYPE(
'<foo><bar baz="abc" value="123" /><bar baz="def" value="456" /></foo>'
)
FROM DUAL;

Outputs, when :arg is def:

VALUE
456

If you try to use :arg as 'abc"][@value="123' then the query returns zero rows (or raises an exception for EXTRACTVALUE); but if you try passing the same value without wrapping it in the call to ENQUOTE_NAME (and include the double quotes that ENQUOTE_NAME would add) then it would perform the XPATH injection that you were trying to avoid.

db<>fiddle here