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!