G-9010: Always use a format model in string to date/time conversion functions.

Major

Changeability, Maintainability, Reliability, Security, Testability

Reason

Converting from strings to date or timestamp datatypes (using to_date, to_timestamp, to_timestamp_tz or cast to any of those datatypes) in practice always expects a fixed format (unlike converting to strings that can be fixed as well as allow the session to decide). Therefore it is a bad idea to allow this conversion to rely on the session NLS settings (nls_date_format, nls_timestamp_format and nls_timestamp_tz_format) as this makes the code vulnerable to changes in session and/or server configuration. It is even possible to utilize session nls_date_format for SQL injection if you use dynamic SQL.

Using an explicit format model for string to date or timestamp conversion avoids this inappropriate dependability on configurable NLS parameters.

Example (bad)

create package body employee_api is
   procedure set_dob(in_employee_id in employees.employee_id%type
                    ,in_dob_str     in varchar2) is
   begin
      update employees
         set date_of_birth = to_date(in_dob_str)
       where employee_id = in_employee_id;
   end set_dob;
end employee_api;
/

Example (good)

create package body employee_api is
   procedure set_dob(in_employee_id in employees.employee_id%type
                    ,in_dob_str     in varchar2) is
   begin
      update employees
         set date_of_birth = to_date(in_dob_str,'FXYYYY-MM-DD')
       where employee_id = in_employee_id;
   end set_dob;
end employee_api;
/