During Feuertip #36, an attendee asked about using the dbms_assert package in some of the code I showed. It was a good idea then, and an even better idea was: do a Feuertip on dbms_assert.
So that’s what we’re going to do! And I say “we” because I’ve invited Anton Nielsen of Insum to do most of the work (and talking).
Oracle documentation says this about the package:
“The DBMS_ASSERT package provides an interface to validate properties of the input value.”
Which doesn’t really tell you very much. So let’s translate that to:
“The DBMS_ASSERT package helps you reduce the chance of SQL injection by validating text you intend to concatenate into a dynamically-constructed SQL statement.”
Validations of inputs include:
- It’s the name of a database object (and not, for example, something like
'where 1 = 1; delete from my_table') - It’s a valid “simple” SQL name (not dot qualified)
- It’s a valid “qualified” SQL name (e.g., schema.table@dblink)
- It’s a valid schema name
You can also make sure that the input is properly quoted and/or double quoted. And you can call the noop function to return the input value unchanged (I’m going to leave it to Anton to have fun explaining why you’d want to do this).
Here are some resources to learn more about dbms_assert.
DBMS_ASSERT – Sanitize User Input to Help Prevent SQL Injection (ORACLE-BASE)
Avoid SQL injection with DBMS_ASSERT (Mark Hoxey)
