Oracle | DBMS

Oracle DBMS_ASSERT Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Tools to prevent SQL Injection attacks

AUTHID CURRENT_USER

Dependencies SELECT name FROM dba_dependencies WHERE referenced_name = 'DBMS_ASSERT' UNION SELECT referenced_name FROM dba_dependencies WHERE name = 'DBMS_ASSERT';

Returns 465 objects

Documented Yes: Packages and Types Reference

Exceptions

Error Code	Reason
ORA-44001	INVALID_SCHEMA_NAME
ORA-44002	INVALID_OBJECT_NAME
ORA-44003	INVALID_SQL_NAME
ORA-44004	INVALID_QUALIFIED_SQL_NAME

First Available 10.2

Pragma PRAGMA SUPPLEMENTAL_LOG_DATA (default, READ_ONLY);

Security Model Owned by SYS with EXECUTE granted to PUBLIC, AUDSYS, DBSFWUSER. and DVSYS

Source {ORACLE_HOME}/rdbms/admin/dbmsasrt.sql

Subprograms

ENQUOTE_LITERAL	QUALIFIED_SQL_NAME	SIMPLE_SQL_NAME_LIST
ENQUOTE_NAME	SCHEMA_NAME	SQL_OBJECT_NAME
NOOP	SIMPLE_SQL_NAME

ENQUOTE_LITERAL

Enquote a string literal. Add leading and trailing single quotes to a string literal dbms_assert.enquote_literal(str IN VARCHAR2) RETURN VARCHAR2;

set serveroutput on

      

      DECLARE

       table_name user_tables.table_name%TYPE := 'SERVERS';

      BEGIN

       
      dbms_output.put_line(table_name);

        table_name := dbms_assert.enquote_literal(table_name);

        dbms_output.put_line(table_name);

      END;

      /

      SERVERS

      'SERVERS'

      

      PL/SQL procedure successfully completed.

ENQUOTE_NAME

This function encloses a name in double quotes dbms_assert.enquote_name( str IN VARCHAR2, capitalize IN BOOLEAN DEFAULT TRUE) RETURN VARCHAR2;

set serveroutput on

      

      DECLARE

       table_name user_tables.table_name%TYPE := 'SERVERS';

      BEGIN

      dbms_output.put_line(table_name);

        table_name := dbms_assert.enquote_name(table_name, FALSE);

        dbms_output.put_line(table_name);

      END;

      /

      SERVERS

      "SERVERS"

      

      PL/SQL procedure successfully completed.

NOOP

Returns the value without any checking. This should be used only for proof-of-concept where the use of other DBMS_ASSERT functionality is being considered.

Overload 1 dbms_assert.noop(str IN VARCHAR2 CHARACTER SET ANY_CS) RETURN VARCHAR2 CHARACTER SET str%CHARSET;

SELECT dbms_assert.noop('SERVERS')

      FROM dual;

      

      DBMS_ASSERT.NOOP('SERVERS')

      ----------------------------

      SERVERS

Overload 2 dbms_assert.noop(str IN CLOB CHARACTER SET ANY_CS) RETURN CLOB CHARACTER SET str%CHARSET;

DECLARE

       cin  CLOB := 'SERVERS';

       cout CLOB;

      BEGIN

        cout := dbms_assert.noop(cin);

        dbms_output.put_line(cout);

      END;

      /

      SERVERS

      

      PL/SQL procedure successfully completed.

QUALIFIED_SQL_NAME

Verify that the input string is a qualified SQL name dbms_assert.qualified_sql_name(str IN VARCHAR2 CHARACTER SET ANY_CS) RETURN VARCHAR2 CHARACTER SET str%CHARSET;

set serveroutput on

      

      DECLARE

       table_name user_tables.table_name%TYPE := 'SERVERS';

      BEGIN

      dbms_output.put_line(table_name);

        table_name := dbms_assert.qualified_sql_name(table_name);

        dbms_output.put_line(table_name);

      END;

      /

      SERVERS

      SERVERS

      

      PL/SQL procedure successfully completed.

      

      DECLARE

       table_name user_tables.table_name%TYPE := 'BEGIN GRANT DBA TO myuser END;';

      BEGIN

        dbms_output.put_line(table_name);

        table_name := dbms_assert.qualified_sql_name(table_name);

        dbms_output.put_line(table_name);

      END;

      /

      BEGIN GRANT DBA TO myuser END;

      DECLARE

      *

      ERROR at line 1:

      ORA-44004: invalid qualified SQL name

      ORA-06512: at "SYS.DBMS_ASSERT", line 375

      ORA-06512: at "SYS.DBMS_ASSERT", line 315

      ORA-06512: at line 5

SCHEMA_NAME

Verifies that the input string is an existing schema name dbms_assert.schema_name(str IN VARCHAR2 CHARACTER SET ANY_CS) RETURN VARCHAR2 CHARACTER SET str%CHARSET;

SELECT dbms_assert.schema_name('UWCLASS')

      FROM dual;

      

      DBMS_ASSERT.SCHEMA_NAME('UWCLASS')

      -----------------------------------

      UWCLASS

      

      SELECT dbms_assert.schema_name('UWCLASZ')

      FROM dual;

      SELECT dbms_assert.schema_name('UWCLASZ')

      *

      ERROR at line 1:

      ORA-44001: invalid schema

      ORA-06512: at "SYS.DBMS_ASSERT", line 410

      

      CREATE TABLE user_pwd (

      username VARCHAR2(30),

      password VARCHAR2(30));

      

      INSERT INTO user_pwd VALUES ('UWCLASS', 'UWCLASS');

      INSERT INTO user_pwd VALUES ('MORGAN', 'AceDir');

      COMMIT;

      

      CREATE OR REPLACE PROCEDURE ckpwd (usr IN VARCHAR2, pwd IN VARCHAR2) IS

       v_query  VARCHAR2(100);

       v_output PLS_INTEGER;

      BEGIN

        v_query := q'{SELECT COUNT(*) FROM user_pwd}' || ' ' ||

        q'{WHERE username = '}' || dbms_assert.schema_name(usr) ||

        q'{' AND password = '}' || pwd || q'{'}';

      

        dbms_output.put_line(CHR(10)||'Built the following statement: ' ||CHR(10)|| v_query);

      

        EXECUTE IMMEDIATE v_query INTO v_output;

      

        dbms_output.put_line(CHR(10) || usr || ' is authenticated');

        dbms_output.put_line(TO_CHAR(v_output));

      EXCEPTION

        WHEN dbms_assert.invalid_schema_name THEN

          dbms_output.put_line(CHR(10) || ' access denied');

      END ckpwd;

      /

      

      Procedure created.

      

      set serveroutput on

      

      exec ckpwd('UWCLASS', 'UWCLASS');

      

      Built the following statement:

      SELECT COUNT(*) FROM user_pwd WHERE username = 'UWCLASS' AND password = 'UWCLASS'

      

      UWCLASS is authenticated

      1

      

      PL/SQL procedure successfully completed.

      

      exec ckpwd('MORGAN', 'ACEDIR');

      

      access denied

      

      PL/SQL procedure successfully completed.

SIMPLE_SQL_NAME

Verifies that the input string is a simple SQL name dbms_assert.simple_sql_name(str IN VARCHAR2 CHARACTER SET ANY_CS) RETURN VARCHAR2 CHARACTER SET str%CHARSET;

SELECT dbms_assert.simple_sql_name('SERVERS1')

      FROM dual;

      

      DBMS_ASSERT.SIMPLE_SQL_NAME('SERVERS1')

      ----------------------------------------

      SERVERS1

      

      SELECT dbms_assert.simple_sql_name('1SERVERS')

      FROM dual;

      SELECT dbms_assert.simple_sql_name('1SERVERS')

      *

      ERROR at line 1:

      ORA-44003: invalid SQL name

      ORA-06512: at "SYS.DBMS_ASSERT", line 215

SIMPLE_SQL_NAME_LIST (new 20c)

Verifies that the input string is a comma delimited list of simple SQL names dbms_assert.simple_sql_name_list(str IN VARCHAR2 CHARACTER SET ANY_CS) RETURN VARCHAR2 CHARACTER SET Str%CHARSET;

SELECT dbms_assert.simple_sql_name_list('UWCLASS,SCOTT,HR,SH')

      FROM dual;

SQL_OBJECT_NAME

This function verifies that the input parameter string is a qualified SQL identifier of an existing SQL object dbms_assert.sql_object_name(str IN VARCHAR2 CHARACTER SET ANY_CS) RETURN VARCHAR2 CHARACTER SET str%CHARSET;

SELECT dbms_assert.sql_object_name('UWCLASS.SERVERS')

      FROM dual;

      

      DBMS_ASSERT.SQL_OBJECT_NAME('UWCLASS.SERVERS')

      -----------------------------------------------

      UWCLASS.SERVERS

      

      SELECT dbms_assert.sql_object_name('UWCLASS.SERVERZ')

      FROM dual;

      SELECT dbms_assert.sql_object_name('UWCLASS.SERVERZ')

      *

      ERROR at line 1:

      ORA-44002: invalid object name

      ORA-06512: at "SYS.DBMS_ASSERT", line 472

      ORA-06512: at "SYS.DBMS_ASSERT", line 467