All Products
Document Center

Security notes for data conversions

Last Updated: Jun 18, 2021

If date and time values are implicitly converted to text or explicitly converted to text without specifying a format model, the format model is defined by a global session parameter. The name of the parameter can be NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, or NLS_TIMESTAMP_TZ_ FORMAT. This depends on the type of source data. The values of these parameters can be specified in the client environment or the ALTER SESSION statement.

If date and time values in dynamically constructed SQL statements are explicitly converted without specifying a format model, database security is negatively affected when the format model selects a session parameter.

The dynamically constructed SQL statements are those generated by programs or stored procedures. To execute dynamically constructed SQL statements, the built-in procedural language (PL) package DBMS_SQL in ApsaraDB for OceanBase is required, or the SQL statement is relevant to the PL statement EXECUTE IMMEDIATE. However, these are not the only methods to execute the dynamically constructed SQL text.

In the following example, the data type of start_date is DATE. The format model that is specified in the session parameter NLS_DATE_FORMAT is used to convert the value of start_date to text. Then, the result is passed to the SQL text. The date and time format model can consist of text that is enclosed in double quotation marks (").

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

The user who sets globalization parameters for the format model that is used for explicit conversion can determine what text is generated by the previous conversion.

If the SQL statement is executed by a procedure, SQL injection may occur during the execution because the session variables are modified. Procedures that have higher permissions, such as Definer's Rights Procedure, may cause more impact on security.