Ensuring safety of Datetime literal
- Use the two-parameter overload, for an input of datatype date, To_Char(d, Fmt), to compose a SQL datetime literal
- Concatenate one single quote character before the start of this value and one single quote character after its end.
- Assert that the result is safe with DBMS_Assert.Enquote_Literal().
- Compose the date predicate in the SQL statement using the two-parameter overload for To_Date(t, Fmt) and using the identical value for Fmt as was used to compose t.
The procedure p_Safe(), whose first few lines are shown in code below implements this approach. Of course, date is not the only datetime datatype. The same reasoning applies for, for example, a timestamp literal.
procedure p_Safe(d in date) is
q constant varchar2(1) := '''';
-- Choose precision according to purpose.
Fmt constant varchar2(32767) := 'J hh24:mi:ss';
Safe_Date_Literal constant varchar2(32767) :=
Fmt_Literal constant varchar2(32767) := q||Fmt||q;
Safe_Stmt constant varchar2(32767) :=
' insert into t(d) values(To_Date('
|| ', '
execute immediate Safe_Stmt;
Ensuring the safety of a SQL text literal
The rules for composing a safe SQL text literal from a PL/SQL text value:
- Replace each singleton occurrence, within the PL/SQL text value, of the single quote character with two consecutive single quote characters.
- Concatenate one single quote character before the start of the value and one single quote character after the end of the value.
- Assert that the result is safe with DBMS_Assert.Enquote_Literal()
Ensuring the safety of a SQL numeric literal or simple SQL name
The rules for composing a safe SQL numeric literal from a PL/SQL numeric value:
- Use explicit conversion with the To_Char() overload with three formal parameters. This overload requires that a value be supplied for Fmt. Explicitly provide the value that supplies the default when the overload with one formal parameter is used. This is 'TM'. 'TM' is the so-called text minimum number format model. It returns the smallest number of characters possible in fixed notation unless the output exceeds 64 characters.
- Explicitly provide the value that supplies the default for the NLS_Numeric_Characters parameter when the one of the overloads with one or two formal parameters is used. This is '.,'.
- Ensure the safety of the name with DBMS_Assert.Simple_Sql_Name().