G-9010
🆓Always use a format model in string to date/time conversion functions.
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
Non-Compliant Example
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;
/Issues
| Line | Column | Message |
|---|---|---|
| 10 | 30 | Missing format model in to_date function call. |
Compliant Solution - ★★★★★
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;
/References
- same as plsql:S4575
- similar to PMD TO_TIMESTAMPWithoutDateFormat
The scope of TO_TIMESTAMPWithoutDateFormat is TO_TIMESTAMP only.
- same as Trivadis G-9010