By: Steven Feuerstein On: March 18, 2022 In: Feuertips Comments: 0

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 documentation

DBMS_ASSERT – Sanitize User Input to Help Prevent SQL Injection (ORACLE-BASE)

Avoid SQL injection with DBMS_ASSERT (Mark Hoxey)

Oracle LiveSQL demo script


Every other Wednesday at 11 am ET, I go live on YouTube and Facebook with Michelle Skamene to provide you with a not-to-be-missed PL/SQL tip and fun conversation. Sure, we record it so you can always watch later, but live is so much more rewarding!
One participant will be selected at random to choose from three organizations dedicated to the preservation of our planet and its biodiversity.
Insum will then make a donation of $25 to that group in your name.
What could be better than levelling up your PL/SQL tips and helping one of these worthy organizations? Join us every other Wednesday at 11!
Share this:

Leave reply:

Your email address will not be published. Required fields are marked *