Oracle | DBMS

Oracle DBMS_ASSERT Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows

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' ORDER BY 1;

Returns 510 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');

      

      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;

      /

      *

      ORA-44004: invalid qualified SQL name

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');

      

      DBMS_ASSERT.SCHEMA_NAME('UWCLASS')

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

      UWCLASS

      

      SELECT dbms_assert.schema_name('UWCLASZ');

             *

      ORA-44001: invalid schema

      
      

      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');

      

      DBMS_ASSERT.SIMPLE_SQL_NAME('SERVERS1')

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

      SERVERS1

      

      SELECT dbms_assert.simple_sql_name('1SERVERS');

             *

      ORA-44003: invalid SQL name

SIMPLE_SQL_NAME_LIST

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');

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');

      

      DBMS_ASSERT.SQL_OBJECT_NAME('UWCLASS.SERVERS')

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

      UWCLASS.SERVERS

      

      SELECT dbms_assert.sql_object_name('UWCLASS.SERVERZ');

             *

      ORA-44002: invalid object name