Oracle DBMS_ASSERT
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
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 391 separate 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 and APEX_040200
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
DBMS_SQL
Native Dynamic SQL
Packages
SQL Injection
Security

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-2014 Daniel A. Morgan All Rights Reserved