Preventing SQL Injection in Oracle

There are three kinds of SQL literal: text, datetime, and numeric. Each deserves separate attention.

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.

Notice that the mandate in the third bullet is the crucial one. It is this one that guarantees immunity to injection; the first two and the fourth mandates prevent annoying run-time errors.

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.

-- Code

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) :=
Sys.DBMS_Assert.Enquote_Literal(q||To_Char(d, Fmt)||q);

Fmt_Literal constant varchar2(32767) := q||Fmt||q;
Safe_Stmt constant varchar2(32767) :=
' insert into t(d) values(To_Date('
|| Safe_Date_Literal
|| ', '
|| Fmt_Literal
|| '))';
begin
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()

Notice that the mandate in the third bullet is the crucial one. It is this one that guarantees immunity to injection; the first mandate prevents annoying run-time errors.


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().