2021 – SQL Injection and the API Inserts

by

One of the requirements for the simple PLSQL API’s being created in these posts is that all errors must be ignored. Don’t laugh – I’ve often been given that requirement in the real world. That does not leave me with much to do at this point so I’ll use the time to take care of an important potential problem:

  • SQL Injection
    • Occurs when parameters values unexpectedly alter the execution od code
    • The simplest way to describe this is any input parameters must be…simple

SQL Injection (RDBMS API)

One way check if parameters are simple is to call the aptly named SIMPLE_SQL_NAME procedure in the DBMS_ASSERT package. If it determines the is not simple enough it raises an exception.

One of the rules this function checks is this:

The name must begin with an alphabetic character. It may contain alphanumeric characters as well as the characters _, $, and # in the second and subsequent character positions.

Here are some examples:

BEGIN
  DBMS_OUTPUT.PUT_LINE(DBMS_ASSERT.SIMPLE_SQL_NAME('Safe'));
END;
Safe

BEGIN
  DBMS_OUTPUT.PUT_LINE(DBMS_ASSERT.SIMPLE_SQL_NAME('$NotSafe'));
END;
Raises ORA-44003: invalid SQL name

The $ at the start of the second example caused the exception to be thrown aka asserted.

For a full list of what is considered invalid please consult the Oracle documentation.

I need augment the INSERT procedures to catch SQL injection (yaayyyy!) but still to fail silently (booo!). For example this call must not create any records.

BEGIN
  patient_pkg.add_patient('$NotSafe',1,'MRN123');
END;

Here is the new code.

PROCEDURE add_patient ( p_name VARCHAR2,
                        p_os        NUMBER,
                        p_mrn    VARCHAR2 ) AS
BEGIN
  INSERT INTO patient( patient_id,
                       patient_name)
  VALUES( patient_seq.nextval,
          DBMS_ASSERT.SIMPLE_SQL_NAME(p_name));
  INSERT INTO patient_mrn( patient_id,
                           ordering_system,
                           patient_mrn)
  VALUES( patient_seq.currval,
          p_os,
          DBMS_ASSERT.SIMPLE_SQL_NAME(p_mrn));
EXCEPTION
  WHEN OTHERS THEN NULL;
END;

Now to test.

BEGIN
  patient_pkg.add_patient('Safe','1,'MRN123');
  patient_pkg.add_patient('$NotSafe',1,'MRN123');
END;

And then query the entries.

SELECT *
  FROM patient
  WHERE patient_name LIKE '%Safe';

PATIENT_ID PATIENT_NAME
---------- ------------
        10 Safe

Looks like it works!

I wonder what will happen if someone tries to inject JSON?

BEGIN
  DBMS_OUTPUT.PUT_LINE(DBMS_ASSERT.SIMPLE_SQL_NAME('{"Name":"Bobby Tables"}'));
END;
Raises ORA-44003: invalid SQL name

That fails because of the { as the first character.

SQL Injection (JSON API)

Now for the JSON API. I’ve added calls to SIMPLE_SQL_NAME to ensure SQL injections do not occur.

v := JSON_OBJECT( KEY 'OS' VALUE p_os,
                   KEY 'MRN' VALUE DBMS_ASSERT.SIMPLE_SQL_NAME(p_mrn) );
v := JSON_ARRAY(v FORMAT JSON);
v := JSON_OBJECT( KEY 'pname' VALUE DBMS_ASSERT.SIMPLE_SQL_NAME(p_name),
                  KEY 'mrns' VALUE v FORMAT JSON);
INSERT INTO json_patient(patient_data) VALUES(v);

Now to test.

BEGIN
  jpatient_pkg.add_patient('Safe',1,'MRN123');
  jpatient_pkg.add_patient('$NotSafe',1,'MRN123');
END;

And only one row (the safe one) is returned by the query!

PATIENT_DATA
---------------------------------------------------------
{"pname" : "Safe","mrns" : [{"OS" : 1,"MRN" : "MRN123"}]}

Next week I’ll make the queries safe.

Thanks for reading!

Leave a Comment