Functions and usage of the DBMS_ASSERT package - PolarDB

DBMS_ASSERT is a built-in package that validates input values before they are used in dynamically constructed SQL statements. Use it to guard against SQL injection by verifying that identifiers and string literals conform to expected formats.

All functions share a common behavior: if the input meets the assertion condition, the function returns the input value (sometimes modified). If the condition is not met, the function raises an exception.

Subprograms

Subprogram	Description
ENQUOTE_LITERAL	Encloses a string literal with a single quotation mark (')
ENQUOTE_NAME	Encloses a string with a double quotation mark (") and checks whether it is a valid SQL identifier
NOOP	Returns the input value without any validation
QUALIFIED_SQL_NAME	Verifies whether an input string is a valid qualified SQL name
SCHEMA_NAME	Verifies whether an input string is an existing schema name
SIMPLE_SQL_NAME	Verifies whether an input string is a simple SQL name
SQL_OBJECT_NAME	Verifies whether an input string is the name of an existing SQL object

ENQUOTE_LITERAL

Encloses a string literal with a single quotation mark (').

Syntax

DBMS_ASSERT.ENQUOTE_LITERAL (
   str            VARCHAR2)
RETURN VARCHAR2;

Parameters

Parameter	Description
str	The string to enclose with a single quotation mark (')

Return value

Returns the input string enclosed with a single quotation mark (').

Example

SELECT DBMS_ASSERT.ENQUOTE_LITERAL('Hello PolarDB') FROM dual;
 enquote_literal
-----------------
 'Hello PolarDB'
(1 row)

ENQUOTE_NAME

Encloses a string with a double quotation mark (") and checks whether it is a valid SQL identifier. By default, the function converts the input to uppercase before enclosing it.

Syntax

DBMS_ASSERT.ENQUOTE_NAME (
   str            VARCHAR2,
   capitalize     BOOLEAN DEFAULT TRUE)
RETURN VARCHAR2;

Parameters

Parameter	Description
str	The string to enclose with a double quotation mark (")
capitalize	(Optional) Specifies whether to convert the string to uppercase before enclosing it. Default: `TRUE`. Set to `FALSE` to preserve the original case.

Usage notes

When capitalize is TRUE (the default), letters in str are converted to uppercase before being enclosed.
When capitalize is FALSE, the string is enclosed as-is, preserving its original case.

Return value

Returns the input string enclosed with a double quotation mark (").

Example

-- Default behavior: converts to uppercase
SELECT DBMS_ASSERT.ENQUOTE_NAME('Hello PolarDB') FROM dual;
  enquote_name
-----------------
 "HELLO POLARDB"
(1 row)

NOOP

Returns the input value without any validation. Use this function as a placeholder when you want to signal that a value has been reviewed and intentionally left unchecked.

Syntax

DBMS_ASSERT.NOOP (
   str      VARCHAR2)
RETURN      VARCHAR2;

Parameters

Parameter	Description
str	The input string

Return value

Returns the input string unchanged.

Example

SELECT DBMS_ASSERT.NOOP('Hello PolarDB') FROM dual;
     noop
---------------
 Hello PolarDB
(1 row)

QUALIFIED_SQL_NAME

Verifies whether an input string is a valid qualified SQL name.

Syntax

DBMS_ASSERT.QUALIFIED_SQL_NAME (
   str      VARCHAR2)
RETURN      VARCHAR2;

Parameters

Parameter	Description
str	The input string to validate

Return value

Returns the input string if it is a valid qualified SQL name. Raises an exception if validation fails.

Example

-- Valid qualified SQL name
SELECT DBMS_ASSERT.QUALIFIED_SQL_NAME('HelloPolarDB') FROM dual;
 qualified_sql_name
--------------------
 HelloPolarDB
(1 row)

-- Invalid: unquoted name contains a space
SELECT DBMS_ASSERT.QUALIFIED_SQL_NAME('Hello PolarDB') FROM dual;
ERROR:  invalid qualified SQL name

SCHEMA_NAME

Verifies whether an input string matches an existing schema name in the database.

Syntax

DBMS_ASSERT.SCHEMA_NAME (
   str      VARCHAR2)
RETURN      VARCHAR2;

Parameters

Parameter	Description
str	The input string to validate

Usage notes

The function checks the database catalog at runtime, so the schema must exist when the function is called.

Return value

Returns the input string if it matches an existing schema name. Raises an exception if no matching schema is found.

Example

-- Existing schema name
SELECT DBMS_ASSERT.SCHEMA_NAME('pg_catalog') FROM dual;
 schema_name
-------------
 pg_catalog
(1 row)

-- Non-existing schema name
SELECT DBMS_ASSERT.SCHEMA_NAME('pg_catalog__') FROM dual;
ERROR:  invalid schema name

SIMPLE_SQL_NAME

Verifies whether an input string is a simple (single-component) SQL name.

Syntax

DBMS_ASSERT.SIMPLE_SQL_NAME (
   str      VARCHAR2)
RETURN      VARCHAR2;

Parameters

Parameter	Description
str	The input string to validate

Return value

Returns the input string if it is a valid simple SQL name. Raises an exception if validation fails.

Example

-- Valid simple SQL name
SELECT DBMS_ASSERT.SIMPLE_SQL_NAME('hellopolar') FROM dual;
 simple_sql_name
-----------------
 hellopolar
(1 row)

-- Invalid: contains a space
SELECT DBMS_ASSERT.SIMPLE_SQL_NAME('hello polar') FROM dual;
ERROR:  invalid simple SQL name

SQL_OBJECT_NAME

Verifies whether an input string is the name of an existing SQL object in the database.

Syntax

DBMS_ASSERT.SQL_OBJECT_NAME (
   str      VARCHAR2)
RETURN      VARCHAR2;

Parameters

Parameter	Description
str	The input string to validate

Usage notes

The function checks the database catalog at runtime, so the object must exist when the function is called.

Return value

Returns the input string if it is the name of an existing SQL object. Raises an exception if no matching object is found.

Example

CREATE TABLE test(a int);

-- Valid SQL object name
SELECT DBMS_ASSERT.SQL_OBJECT_NAME('test') FROM dual;
 sql_object_name
-----------------
 test
(1 row)

-- Invalid: object does not exist
SELECT DBMS_ASSERT.SQL_OBJECT_NAME('notest') FROM dual;
ERROR:  invalid object name

DROP TABLE test;