34 OCI-C Functions to Prevent SQL Injection

This chapter describes OCI-C APIs (corresponding to DBMS_ASSERT package) that quote and validate user supplied strings before embedding into SQL texts to protect against SQL injection.

SQL injection is a security vulnerability where an attacker manipulates an application’s SQL query by supplying crafted input (often through form fields, URL parameters, headers, etc.). If the application concatenates that input directly into SQL, the attacker can change the meaning of the query by any of the following:

• Reading sensitive/confidential data, such as, customer records
• Modifying or deleting data
• Bypassing authentication/authorization checks
• Executing administrative actions

DBMS_ASSERT is an Oracle supplied built-in PL/SQL package that helps in safely construct dynamic SQL by validating and sanitizing user input strings, especially object identifiers that cannot be bind variables (table names, column names, schema names, SQL fragments), thereby preventing SQL injection.

Commonly used DBMS_ASSERT routines include:

• DBMS_ASSERT.ENQUOTE_LITERAL — Enquotes (adds leading and trailing single quotes) a string literal.
• DBMS_ASSERT.ENQUOTE_NAME — Ensures that a string is enclosed by quotation marks, then checks that the result is a valid SQL identifier
• DBMS_ASSERT.QUALIFIED_SQL_NAME — Verifies that the input string is a qualified SQL name.
• DBMS_ASSERT.SIMPLE_SQL_NAME — Verifies that the input string is a simple SQL name.

Note:

See also DBMS_ASSERT for a Summary of DBMS_ASSERT subprograms.

OCI-C APIs corresponding to the DBMS_ASSERT functionality quotes and validates user supplied strings before embedding into SQL texts where bind variables cannot be used for identifiers.

DBMS_ASSERT in OCI-C: Equivalent APIs and Usage

OCI-C APIs introduced to provide functionality similar to the DBMS_ASSERT functions offer C/OCI developers a standard way to validate and/or sanitize SQL fragments/identifiers before using them in dynamic SQL. These APIs enforce the same rules as DBMS_ASSERT, so both PL/SQL and internal SQL/dynamic SQL features can share the same validation logic.

The following table lists the new OCI-C APIs introduced and their equivalent PL/SQL functions and C implementations:

OCI-C APIs	PL/SQL equivalent	Function class	Primary purpose
OCIEnquoteLiteral()	ENQUOTE_LITERAL	Quote and validate	Returns a syntactically valid SQL string literal
OCIEnquoteName()	ENQUOTE_NAME	Quote and validate	Returns a syntactically valid quoted SQL identifier
OCIQualifiedSqlNameCheck()	QUALIFIED_SQL_NAME	Validate only	Verifies that input is a qualified SQL name
OCISimpleSqlNameCheck()	SIMPLE_SQL_NAME	Validate only	Verifies a single SQL identifier (quoted or unquoted)