People often ask how to execute parametric query to database engines, like SQL Server or Oracle. Consider the following sql.
select * from emp where ename=’SMITH’
They want to convert the hardcode SMITH in the query to the variable having some value in runtime. Many people use the following approach.
string sql="select * from emp where ename='"+val+"'";
This approach has SQL injection flaw. To give you a short example: I am not sure about the Oracle, but in SQL Server, we can execute two queries as a batch separating them by semicolon. If, val variable has the value ‘; delete from emp the sql will become
select * from emp where ename=’’; delete from emp
I guess, you have got the idea, how dangerous it can become. Therefore, it is recommended that one should not concatenate Sql. There are number of options available, for example using SqlParameter with SQL Server. SqlParameter is the implementation of IDataParameter interface that is there to provide the parameters. Similarly, we have OleDbParameter in the System.Data.OleDb namespace that can be used with any OleDb compliant database and OracleParameter in System.Data.OracleClient namespace that should be used in such scenarios. Here is the example of using this:
cmd.CommandText="select * from emp where ename=?";
cmd.Parameters.Add(new System.Data.OracleClient.OracleParameter("emp", val));
//Execute command and close connection
Let me clear few things in the end, I have not used Oracle since long so I am not sure about the above code working. I am still unsure why we need to give the name of the OracleParameter. Working with SQL Server, the above query will become as given below and we naturally need the SqlParameter having same name i-e @eName.
select * from emp where ename=@eName
Microsoft released .NET application blocks having the code (C# and VB.NET) showing the best approaches for doing different things, like accessing databases. I have successfully used their Data Access Application Block (DAAB) in many projects. Unfortunately, it only supports SQL Server. I have come to know that they setup the workspace on GotDotNet and that workspace team has released couple of versions and their recent versions (v2, v3) now has polymorphic approach, meaning you can use the same block with different databases, including Oracle. Here is one article explaining DAAB v2.