Oracle regexp_replace to prevent semicolons

Question

Is the following code sufficient to prevent any semicolon from ever remaining in v_str?

regexp_replace(v_str, ';')

Or is there a way someone can circumvent it?

For example the following snippet is not sufficient to block 'DROP' from being passed:

regexp_replace(v_str, 'DROP')

Someone can bypass this by passing in 'DRDROPOP'.

Seemingly, however, the semicolon prevention above is sufficient. Can you think of a way to break it?

Answer 1

Yes, that will give you the desired result. However, I think regular ole REPLACE is probably more efficient than using the regex_* functions.

WITH t AS
 (SELECT ';1234;abcd;;5678;;;efgh;;' x
    FROM dual)
SELECT x,
       REPLACE(x,
               ';'),
       regexp_replace(x,
                      ';')
  FROM t;

As others have stated this is NOT a good solution for preventing SQL injection, but I understand your need for a stepping stone.

Answer 2

For a quick temporary fix it can work, but it won't stop crafty users from pushing characters in your queries.

For instance :

'DROP DATABASE' || chr(59)
'DROP DATABASE' || chr(58+1)
...