Security considerations for data conversion in Oracle mode

In OceanBase Database in Oracle mode, a format model is a character text that describes the format of datetime or numeric data stored in a string. When you convert data, you must pay attention to the following security considerations.

When you implicitly convert a datetime value to text or explicitly convert it to text without specifying a format model, the format model is defined by the corresponding global session system variables, such as NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, or NLS_TIMESTAMP_TZ_FORMAT. You can specify the values of these variables in the client environment or in an ALTER SESSION statement.

If you explicitly convert a datetime value associated with a dynamic SQL statement to text without specifying a format model, the dependency of the format model on system variables can negatively impact database security.

A dynamic SQL statement is a SQL statement generated by a PL/SQL program or stored procedure. OceanBase Database uses the EXECUTE IMMEDIATE statement to process most dynamic SQL statements. You can also use the PL/SQL system package DBMS_SQL to execute dynamic SQL statements under certain conditions.

The following example shows how to convert the value of the start_date column, whose data type is DATE, to text by using the format model specified by the system variable NLS_DATE_FORMAT, and then pass the result to an SQL statement. A datetime format model can be simply a text string enclosed in double quotation marks.

SELECT last_name FROM employees WHERE hiredate > '' || start_date || '';

The implicit and explicit conversion of numeric values may also encounter similar issues. This is because the conversion results may depend on the session system variable NLS_NUMERIC_CHARACTERS, which defines the decimal separator and thousand separator. If you define the decimal separator as a single quotation mark or double quotation mark, SQL injection may occur.