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

Blocker

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 or replace package body employee_api is
   procedure set_dob(
      in_employee_id in employees.employee_id%type
     ,in_dob_str     in varchar2
   ) is
      co_employee_id constant employees.employee_id%type := in_employee_id;
      co_dob_str     constant type_up.date_string        := in_dob_str;
   begin
      update employees
         set date_of_birth = to_date(co_dob_str default null on conversion error)
       where employee_id = co_employee_id;
   end set_dob;
end employee_api;
/

Example (good)

create or replace package body employee_api is
   procedure set_dob(
      in_employee_id in employees.employee_id%type
     ,in_dob_str     in varchar2
   ) is
      co_employee_id constant employees.employee_id%type := in_employee_id;
      co_dob_str     constant type_up.date_string        := in_dob_str;
   begin
      update employees
         set date_of_birth = to_date(
                co_dob_str default null on conversion error
               ,'FXYYYY-MM-DD'
             )
       where employee_id = co_employee_id;
   end set_dob;
end employee_api;
/