Security considerations for data conversion

OceanBase Database uses format models to describe datetime stored in a string or character literals in the numeric format in Oracle mode. Note the following content during data conversion.

When a datetime value is converted to text, either by implicit conversion or by explicit conversion that does not specify a format model, the format model is defined by one of the globalization session variables NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, and NLS_TIMESTAMP_TZ_ FORMAT. You can specify the values of the variables in the client environment or by using an ALTER SESSION statement.

The dependency of format models on session variables can have a negative impact on database security when conversion without an explicit format model is applied to a datetime value that is being concatenated to text of a dynamic SQL statement.

Dynamic SQL statements are those whose text is generated by using a PL/SQL program or a stored procedure. OceanBase Database deals with most dynamic SQL statements by using the EXECUTE IMMEDIATE statement. You can use the PL/SQL system packageDBMS_SQL to run the dynamic SQL statements if the specified conditions are met.

In the following example, the start_date is of the DATE data type. The format model specified by the system variable NLS_DATE_FORMAT is used to convert the value of the start_date parameter to text and then send the conversion result to the SQL text. A datetime format model can consist of text enclosed in double quotation marks.

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

Implicit and explicit conversion for numeric values may also suffer from the similar problem, because the conversion result may depend on the session parameter NLS_NUMERIC_CHARACTERS. This parameter defines the decimal and group separator characters. If the decimal separator is defined to be a single quotation mark (') or double quotation mark ("), SQL injection may occur.