Question: Using regexp_replace to prevent SQL injection


Using regexp_replace to prevent SQL injection

Answers 4
Added at 2017-09-07 23:09

We have thousands of oracle packages that contain a map_products procedure.

We have a table that stores the list of oracle packages a customer would like that map_products run for.

The process that runs them uses dynamic SQL like this:

select sanitize(package_name) 
  into v_package_name 
from   custom_plugins 
where  id = p_id;

execute immediate '

The sanitize function above is meant to prevent SQL injection.

Here is the function definition:

function sanitize(p_string in varchar2) return varchar2
    return regexp_replace(upper(p_string), 

Now we realize this is a dangerous approach in the long run and are planning to redo the entire process. However, for the time being, is there any easy way that this regexp_replace can be circumvented allowing SQL injection?

More specifically, we want to make sure that a semicolon cannot be passed in. Does the above ensure that?

Answers to

Using regexp_replace to prevent SQL injection

nr: #1 dodano: 2017-09-08 00:09

Use the string:

DRDROPOP your_package_name

The replace will only replace DROP once leaving you with:

DROP your_package_name
nr: #2 dodano: 2017-09-08 00:09

I suggest whitelisting instead of regular expressions.

Check the input against the system tables.

select object_name
from dba_objects
where owner = 'SYS'
and object_type = 'PACKAGE'
and object_name = :p_string;

If you can't find a match, then it isn't a known package, so don't use it.

nr: #3 dodano: 2017-09-08 00:09

I think removing the ; will be a first approach to avoid the sql injection. But please consider this possible strategy:

  1. Extract with regexp a valid package name from the input(for example, checking that you have alphanumeric characters, or "_", or any other character allowed in a package name but nothing else).
  2. With the output from previous step, check that the package is actually an object in the database (querying user_object).
  3. At this point you will have a valid package name (if any) and you could use it in the dynamic statement.

You already said that your approach was dangerous. I just want to make sure that you're aware that it'd be problematic with a package with a name containing any of the words in your regexp (salaryUPDATEr, for example)

nr: #4 dodano: 2017-09-08 20:09

As the other posters above have commented blacklisting is going to be quite easy to bypass.

Using whitelist validation is always ideal. If that's not feasible, and since this is Oracle, the best option is to use the built in dbms_assert.enquote_name - this safely enquotes the value (and checks for embedded quotes).

For more info on DBMS_ASSERT see:

Or for much more in depth on preventing SQL injection see:

Source Show
◀ Wstecz