Why the last PL/SQL statement fails using dbms_assert.enquote_literal?

Question

The firs and second "put_line" statements in the below PL/SQL block will succeed but the last one fails. Why? Could it be a bug?

declare
  x varchar2(100);
begin
  x := 'Test'''; 
  dbms_output.put_line('x is: ' || x || ', enquoted x is: ' || dbms_assert.enquote_literal(replace(x, '''', ''''''))); 

  x := 'Te''st';
  dbms_output.put_line('x is: ' || x || ', enquoted x is: ' || dbms_assert.enquote_literal(replace(x, '''', '''''')));  

  x := '''Test';
  dbms_output.put_line('x is: ' || x || ', enquoted x is: ' || dbms_assert.enquote_literal(replace(x, '''', ''''''))); 
end;
/

The error is:

Error report:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.DBMS_ASSERT", line 317
ORA-06512: at "SYS.DBMS_ASSERT", line 381
ORA-06512: at line 11
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    
*Action:

Any idea?

Answer 1

Can not tell you that why this is happening, but you can try as below to handle this:

    set serveroutput on;
declare
  x varchar2(100);
begin
  x := 'Test'''; 
  dbms_output.put_line('x is: ' || x || ', enquoted x is: ' || replace(dbms_assert.enquote_literal(replace(x, '''', '''''')), ''' ', '''')); 

  x := 'Te''st';
  dbms_output.put_line('x is: ' || x || ', enquoted x is: ' || replace(dbms_assert.enquote_literal(replace(x, '''', '''''')), ''' ', ''''));  

  x := '''Test';

  dbms_output.put_line('x is: ' || x || ', enquoted x is: ' 
  || replace(dbms_assert.enquote_literal(replace(x, '''', ' ''''')), ''' ', '''')
  ); 
end;
/

x is: Test', enquoted x is: 'Test'''
x is: Te'st, enquoted x is: 'Te''st'
x is:  'Test, enquoted x is: '''Test'
PL/SQL procedure successfully completed.

Answer 2

it is mentioned on https://avoidsqlinjection.wordpress.com/category/5-filtering-input-with-dbms_assert/ that When using ENQUOTE_LITERAL, remember to escape single quotes in the input. but not explained very well.

On oracle docs http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_assert.htm#ARPLS65379

 Usage Notes    
        Verify that all single quotes except leading and trailing characters are paired with adjacent single quotes.    
        No additional quotes are added if the name was already in quotes.

This question is good example that ENQUOTE_LITERAL will not qoute strings which are already quoted.But whatever mentioned above only limits us for ENQUOTE_LITERAL.So what is solution for this. As @Vinish Kapoor does a trick in his answer that you can see. So in case of limitations we can convert string into some other pattern and replace it back to normal. you can use below also

 dbms_output.put_line('x is: ' || x || ', enquoted x is: ' || replace(dbms_assert.enquote_literal(replace(x, '''', '#')), '#', ''''));

or this one

 dbms_output.put_line('x is: ' || x || ', enquoted x is: ' || replace(dbms_assert.enquote_literal(replace(x, '''', '~')), '~', ''''));

beacuse leading and trailing single qoutes are causing problem we can conver them into # or ~ and after enquote_literal has done its work we can replace them back to single qoutes.