2021 – JSON Path Expression (JPE) Injection

In this post I’ll explain how to make Dynamic SQL with JSON Path Expressions (JPE) safe from SQL Injection. Or would it be JPE injection? Let’s figure that out later. For now lets get into the code! Here is the function that queries the patient name for a given Ordering System (OS) and Medical Record Number (MRN).

FUNCTION get_pname ( p_os NUMBER,
                     p_mrn VARCHAR2 )
         RETURN VARCHAR2 IS
  v_sql VARCHAR2(1000);
  v_ret_val VARCHAR2(30);
BEGIN
  v_sql := 'SELECT JSON_VALUE(patient_data,''' || '$.pname' || 
           ''') FROM json_patient WHERE ';
  v_sql := v_sql || 'JSON_EXISTS(patient_data,''' || 
           '$.mrns[*]?(@.OS == ' || p_os || 
           ' && @.MRN == "' || p_mrn || '")' || '''' || ')';
  EXECUTE IMMEDIATE v_sql INTO v_ret_val;
  RETURN(v_ret_val);
EXCEPTION
  WHEN OTHERS THEN
    -- just ignore all errors for now :(
    NULL;
END;

And a demo call using an MRN (MRN123) and Ordering System (1) for a record that exists in the DB.

BEGIN
  DBMS_OUTPUT.PUT_LINE(jpatient_pkg.get_pname(1,'MRN123'));
END;
Patient 1

And here’s what the dynamic SQL in the function would be.

SELECT JSON_VALUE(patient_data,'$.pname')
  FROM json_patient
 WHERE JSON_EXISTS(patient_data,'$.mrns[*]?(@.OS == 1 && @.MRN == "MRN123")';

I know that’s a lot to digest so within this post just focus on the matches – each left square bracket has a matching right square bracket, each double quote has a follow up double quote, etc. Because that is a factor in how injection could be done. Consider this call (note the missing double quote).

BEGIN
  DBMS_OUTPUT.PUT_LINE(jpatient_pkg.get_pname(1,'"Bobby Tables'));
END;

Here is the error that would raise:

ORA-40597: JSON path expression syntax error ('$.mrns[*]?(@.OS == 1 && @.MRN == ""Bobby Tables")')
JZN-00229: Missing parenthesis in parenthetical expression

This detailed error message is very helpful when debugging JPE because it includes the error (Missing parenthesis) and the actual broken JPE. This is very, very…wait for it…very helpful when testing and debugging an application but not something I would want to expose to the outside world because it might allow injection.

Here’s the SQL the function built.

SELECT JSON_VALUE(patient_data,'$.pname')
FROM json_patient
WHERE JSON_EXISTS(patient_data,'$.mrns[*]?(@.OS == 1 && @.MRN == ""Bobby Tables")';

The extra double quote at the start of the MRN parameter does not have a matching end double which throws off the JPE compiler which results in  ORA-40597 being thrown.

The solution is to use bind variables for the dynamic SQL which necessitates the USING clause for JSON_EXISTS to change the code to this.

v_sql := 'SELECT JSON_VALUE(patient_data,''' || 
          '$.pname' || ''') FROM json_patient WHERE ';
v_sql := v_sql || 'JSON_EXISTS(patient_data,''' || 
          '$.mrns[*]?(@.OS == $OS && @.MRN == $MRN)' || '''' || ' ';
v_sql := v_sql || 'PASSING :1 AS "OS", :2 AS "MRN")';
EXECUTE IMMEDIATE v_sql INTO v_ret_val USING p_os, p_mrn;

The bind variables (denoted by colons in the SQL string and the USING clause of the EXCUTE IMEMDIATE) combined with the PASSING clause for the JSON_EXISTS function ensure the passed parameters are only used as parameters. They do not become part of the JPE text itself. I appreciate that is a lot to digest and I will dig deeper into that in the next few posts.

-- now no injection Just an (unreported) No Data Found (NDF)
BEGIN
  DBMS_OUTPUT.PUT_LINE(jpatient_pkg.get_pname(1,'"ABC'));
END;

Thanks for reading!

Leave a Comment