Importance of DMBS_Assert Package for Security

The DBMS_ASSERT package was introduced in Oracle 10g Release 2 and backported to Release 1 in the Oracle October 2005 Critical Patch Update. There are currently no references to this package in the 10g Release 2 documentation or on Metalink. The package contains a number of functions that can be used to sanitize user input and help to guard against SQL injection in applications that don't use bind variables.

ENQUOTE_LITERAL Function
Enquotes a string literal
ENQUOTE_NAME Function
Encloses a name in double quotes
NOOP Functions
Returns the value without any checking
QUALIFIED_SQL_NAME Function
Verifies that the input string is a qualified SQL name
SCHEMA_NAME Function
Verifies that the input string is an existing schema name
SIMPLE_SQL_NAME Function
Verifies that the input string is a simple SQL name
SQL_OBJECT_NAME Function
Verifies that the input parameter string is a qualified SQL identifier of an existing SQL object


It is this DBMS_Assert Package that that guarantees immunity to SQL Injection.

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

SQL Injection in Stored Procedure : 2nd Case Study

Stored procedure with dynamic SQL and embedded parameters

The Stored Procedure

Create proc authenticate (@uid nvarchar(25),@pwd nvarchar(25))
as
DECLARE @uid VARCHAR(64)
DECLARE @pwd VARCHAR(64)
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)

/* Build the SQL string once.*/

SET @SQLString =
N'SELECT * FROM users WHERE userid = @uid AND password = @pwd'

SET @ParmDefinition = N'@login VARCHAR(64), @password VARCHAR(64)'


Server side code:

cmd.CommandText = "authenticate";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add( "@uid", strUserName);
cmd.Paramerters.Add( “@pwd, strPassword);
con.Open();

string result = (string)cmd.ExecuteScalar();
oCon.Close();

In this case,bSQL Injection would NOT be possible. Hence what I would like to summarize is if at all we have to use dynamic SQL in stored procedure, always use embedded parameters in dynamic SQL

SQL Injection in Stored Procedure

Let us examine SQL Injection in Stored Procedure. This would be 1 of the vulnerable cases.

The Server Side Code would be something like:
oCmd.CommandText = "sp_login";
oCmd.CommandType = CommandType.StoredProcedure;
oCmd.Parameters.Add( "@loginId", strUserName);
oCmd.Paramerters.Add( “@password”, strPassword);
oCon.Open();
string result = (string)oCmd.ExecuteScalar();
oCon.Close();
====================================================================
The Stored Procedure would be:
CREATE PROC sp_login (@loginid nvarchar(25),@password)
AS
DECLARE @SQLString NVARCHAR(500)
DECLARE @loginid VARCHAR(64)
DECLARE @password VARCHAR(64)

/* Build the SQL string once.*/

SET @SQLString = 'SELECT * from cust_users WHERE login_id = '+ ''''+@loginid+'''' + 'AND password = '+ ''''+@password+''''

EXECUTE sp_executesql @SQLString

====================================================================

If the user input is as follows:
loginId = ' OR 1=1 --
password = junk

The above stored procedure will have an injection attack. The procedure executing will return all the rows because of the injected SQL.

Cheers.