Oracle DBMS_ASSERT
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
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 432 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
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
Security
SQL Injection
What's New In 12cR1
What's New In 12cR2

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