Share via

Using data parameters with Oracle Data Provider for .NET

  • Article

This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at https://www.microsoft.com/info/cpyright.htm

By now everybody must have heard about SQL injection attacks and that to best way to proof your applications against them is by using data parameters in building you SQL queries. For a good discussion about SQL injection attack see this article Securing a .NET Application on the Oracle Database. In addition to security benefits there are some tangible performance gains in using data parameters, since they allow for more efficient server side cashing of SQL statements (for more details on Oracle statement caching see this article Improve ODP.NET Performance, specifically the section on statement caching).

Here is a sample function that takes advantage of using a data parameter

public static bool doesUserExist(string userName,

                                 OracleConnection dbConnection)

{

  bool exists = false;

  OracleCommand cmd = dbConnection.CreateCommand();

  OracleParameter prm = new OracleParameter();

  prm.OracleDbType = OracleDbType.Varchar2;

  prm.Direction = ParameterDirection.Input;

  prm.Value = userName.ToUpper();

  cmd.Parameters.Add(prm);

  cmd.CommandText = "select USERNAME from dba_users where USERNAME = :1";

  OracleDataReader rd;

  try

  {

    rd = cmd.ExecuteReader();

    if (rd.HasRows)

    {

      exists = true;

    }

   }

   catch (Exception ex)

   {

    throw new Exception(ex.Message);

   }

          

   cmd.Dispose();

   rd.Close();

   rd.Dispose();

   return exists;

 }

For details on OracleParameter class see Oracle ODP.NET documentation

Given the benefits of the data parameters I always try to use them whenever possible, and this is really the subject of this blog – in which situations you could not use data parameters and why.

Intuitevly you would try to put parameters within a SQL statement to substitute parts of it which depend on the user input, but as I found out through trial an error this is not always the case. Before I jump to an example here is an excerpt from a book “PRO .NET Oracle Programming” that outlines the limitations of data parameters.

You should be familiar with an important aspect of using bind variables. They may appear anywhere a text literal may appear in a SQL statement. A side effect of this is that you may not use bind variables for items such as table or column names. An easy way to think of this is to think of bind variables as placeholders for user input.

So after reading this I was quite confident that something like this should work.

public static bool setDefatultTableSpace(string userName,

     string tableSpace,

     OracleConnection dbConnection)

{

   OracleCommand cmd = dbConnection.CreateCommand();

   cmd.CommandText = "alter user :1 default tablespace :2";

   OracleParameter[] prm = new OracleParameter[2];

   prm[0] = cmd.Parameters.Add("paramUserName",

                               OracleDbType.Varchar2,

                               userName.ToUpper(),

                               ParameterDirection.Input);

   prm[1] = cmd.Parameters.Add("paramTableSpace",

                               OracleDbType.Varchar2,

      tableSpace, ParameterDirection.Input);

           

   try

   {

     cmd.ExecuteNonQuery();

   }

   catch (Exception ex)

   {

     throw new Exception(ex.Message);

   }

   finally

   {

      cmd.Dispose();

   }

      return true;

   }

To my surprise after running this sample I got the following error:

ORA-01036: illegal variable name/number

After experimenting with this further and throwing a question to Oracle ODP.NET forum it became apparent that in data definition SQL statements (ex. ALTER USER) Oracle treats variable parts (in my case the name of the user and the name of the tablespace) as part of the SQL statement and not as part of data, hence the error.

At any rate I still can’t quite come up with a general rule on how to best determine whether a data parameter would work or not, in my trials they don’t seem to work with data definition statements. I suppose to save yourself some time you could first try to execute your query via SQL PLUS to see if it would work with parameters. Here is an example:

SQL> var paramUserName varchar2(30);

SQL> var paramTableSpace varchar2(30);

SQL> exec :paramUserName := 'USER1';

 

PL/SQL procedure successfully completed.

 

SQL> exec :paramTableSpace :='USERS';

 

PL/SQL procedure successfully completed.

 

SQL> ALTER USER :paramUserName DEFAULT TABLESPACE :paramTableSpace;

ALTER USER :paramUserName DEFAULT TABLESPACE :paramTableSpace

           *

ERROR at line 1:

ORA-01935: missing user or role name

As per my function (setDefaultTableSpace), I had to rewrite it by hard coding the SQL statement.

Comments