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 is now availble. If you haven't you downloaded already: Why?

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