DBMS_ASSERT Package

Today I’ll like to talk about the dbms_assertion package. It is used in many differents ways. I usually use it for prevent SQL injection, it must to be a best practies for developers.

There are many kinds of SQL injecto, being the most simple case introduce in a windows field the date requested by the app more a little of sql. Suppose that your app required an username, in the usual way of work you type de username

USERNAME: SCOTT

The injection would be (I have assumed a very simple case where the app recive the data in a kind the raw value);

USERNAME: SCOTT UNION ALL SELECT * FROM DBA_USERS

Depend on your app and how it work this can be a serious problem. DBMS_ASSERT package prevent this by “sanitizing” the SQL.

 
Some examples can be like those;

 
REM
REM In this first case, check quotes
REM

WEUP*ANTO> BEGIN
  2  DBMS_OUTPUT.PUT_LINE (DBMS_ASSERT.ENQUOTE_LITERAL ('DUMMY'));
  3  END;
  4  /
'DUMMY'

Transcurrido: 00:00:00.04
WEUP*ANTO> BEGIN
  2  DBMS_OUTPUT.PUT_LINE (DBMS_ASSERT.ENQUOTE_LITERAL (''''DUMMY''''));
  3  END;
  4  /
DBMS_OUTPUT.PUT_LINE (DBMS_ASSERT.ENQUOTE_LITERAL (''''DUMMY''''));
                                                     *
ERROR en línea 2:
ORA-06550: línea 2, columna 54:
PLS-00103: Encountered the symbol "DUMMY" when expecting one of the following:
) , * & = - + < / > at in is mod remainder not rem =>
<an exponent (**)> <> or != or ~= >= <= <> and or like like2
like4 likec as between from using || multiset member
submultiset
The symbol ", was inserted before "DUMMY" to continue.

REM
REM In the nex case check a valid name for an object
REM 

Transcurrido: 00:00:00.08
WEUP*ANTO>  BEGIN
  2  DBMS_OUTPUT.PUT_LINE (DBMS_ASSERT.QUALIFIED_SQL_NAME ('SCOTT.TABLE_ONE'));
  3  END;
  4  /
SCOTT.TABLE_ONE

WEUP*ANTO>  BEGIN
  2  DBMS_OUTPUT.PUT_LINE (DBMS_ASSERT.QUALIFIED_SQL_NAME ('SCOTT TABLE_ONE'));
  3  END;
  4  /
 BEGIN
*
ERROR en línea 1:
ORA-44004: nombre de SQL cualificado no válido
ORA-06512: en "SYS.DBMS_ASSERT", línea 315
ORA-06512: en línea 2

HTH – Antonio NAVARRO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s