Oracle DBMS_ASSERT
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.
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 440 objects
Documented Yes
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.0.1
Security Model Owned by SYS with EXECUTE granted to PUBLIC AUDSYS, DBSFWUSER. and DVSYS
Source {ORACLE_HOME}/rdbms/admin/dbmsasrt.sql
Subprograms
 
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;
/
 
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);
  dbms_output.put_line(table_name);
END;
/
 
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;
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;
/
 
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;
/

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;
/
 
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;

SELECT dbms_assert.schema_name('UWCLASZ')
FROM dual;

CREATE TABLE user_pwd (
username VARCHAR2(30),
password VARCHAR2(30));

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

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;
/

set serveroutput on

exec ckpwd('UWCLASS', 'UWCLASS');
exec ckpwd('MORGAN', 'ACEDIR');
 
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;

SELECT dbms_assert.simple_sql_name('1SERVERS')
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;

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

Related Topics
Built-in Functions
Built-in Packages
DBMS_SQL
OWM_ASSERT_PKG
Native Dynamic SQL
Security
SQL Injection
What's New In 12cR2
What's New In 18cR3

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved