Parameterized Queries (Oracle, SQLServer, OleDb)

In my opinion, the parameterized queries approach is always better than the traditional approach of joining string to build a dynamic SQL string.

This last one is susceptible to SQL injections attacks, and usually leads to data format problems, you have to worry about how to encode the parameter according to its type. (For instance, the string parameters must be enclosed with a single quote).

Unfortunately, each database vendor implements parameterized queries in different ways, and it took me sometime today to figure out how to use them in Oracle.

SQL server uses a named parameter approach, it does not matter the parameters order, but each parameter name requires a "@" prefix.

string sql = "SELECT * FROM Customers WHERE CustomerId = @CustomerId";

SqlCommand command = new SqlCommand(sql);

command.Parameters.Add(new SqlParameter("@CustomerId", System.Data.SqlDbType.Int));

command.Parameters["@CustomerId"].Value = 1;

Oracle uses a similar approach, but a different prefix, since it expects a ":" prefix.

string sql = "SELECT * FROM Customers WHERE CustomerId = :CustomerId";

OracleCommand command = new OracleCommand(sql);

command.Parameters.Add(new OracleParameter(":CustomerId", OracleType.Int32));

command.Parameters[":CustomerId"].Value = 1;

OleDB uses a sequential approach, in this case, the parameters order really matters. A parameter is identified by a character "?" in the SQL query.

string sql = "SELECT * FROM Customers WHERE CustomerId = ?";

OleDbCommand command = new OleDbCommand(sql);

command.Parameters.Add(new OleDbParameter("CustomerId", OleDbType.Integer));

command.Parameters["CustomerId"].Value = 1;

 

Any comment is welcome.

Pablo. 

Published 28 September 2006 01:13 PM by cibrax
Filed under:

Comments

# Wallym said on 28 September, 2006 12:23 PM
Dynamic Sql is only susceptible to Sql Inject if you don't know how to guard against it. I have a set of libraries that I have developed over the years as a consultant that protect against this issue as well as provide additional features. Though, that is the fault of most programmers, not dynamic sql. I find it easier to develop applications with dynamic sql as oppossed to stored procedures or parameterized queries. Most inhouse developers don't know what sprocs or parameterized sql is, so I find that including them in a solution creates difficulties for the inhouse developers. Granted, this is a skillset that can(and should) be learned. So there is a learning curve to what these items. One last thought is the area of performance. There are valid situations where parameterized queries and sprocs should be used. One just has to know and learn what these are, when to use the features and when not to. Finally, I think that parameterized queries (and sprocs) are a tool. They work really well in the right circumstances. Knowing when to use parameterized queries, sprocs and dynamic sql is important. But jus saying that one should always use any tool, I don't agree with that.
# Yong said on 28 September, 2006 01:40 PM
We often use dynamically generated parameterized SQL when stored procs are not flexible enough to handle the possible permutations. However, sometimes parameterized SQL may have performance problem compared to Dynamic SQL.
# Richard Ponton said on 28 September, 2006 11:22 PM
There is zero excuse for *not* using parameterized queries. If you have to generate dynamic SQL, generate dynamic *parameterized* SQL. Parameters can be added dynamicly too, ya know. Never allow user input into the SQL directly. If you have to use a dynamic table name (since they can't be parameters), pass them through a switch statement. Likewise with ORDER BY clauses. "I have a set of libraries that I have developed over the years as a consultant that protect against this issue as well as provide additional features." Thank you for reinventing the wheel. I'm sure you made a nice, shiny, round wheel. But I prefer to trust the database driver implementers instead. "Most inhouse developers don't know what sprocs or parameterized sql is, so I find that including them in a solution creates difficulties for the inhouse developers." In .NET, parameterized queries are *easier* than dynamic SQL. If they can't learn parameterized queries in a couple of days, you don't want them as developers. "Knowing when to use parameterized queries, sprocs and dynamic sql is important." But it's not about parameterized queries vs. dynamic SQL at all. It's about parameterized queries, dynamic or not, vs. sticking raw string values in the SQL. StringBuilder dynSQL = new StringBuilder(); dynSQL.Append("SELECT * FROM tab WHERE foo IN ('dummyval'"); for (int i = 0; i < inVals.Count; i++) { // using Oracle syntax, add parameters dynamicly as we // build the dynamic SQL cmd.Parameters.AddWithValue("in" + i, inVals[i]); dynSQL.Append(", :in" + i); } dynSQL.Append(")"); cmd.CommandText = dynSQL.ToString();
# Matt said on 06 February, 2007 08:56 AM

I agree with Richard Ponton - there is absolutely no reason what-so-ever for using lame dynamic sqls like:

"SELECT * FROM emp WHERE name = '" + Textbox1.Text +"'"

This is never, ever necessary. Additionally, it is incredibly lame, because everything must become a string (like dates, numbers) and be parsed back into the type by the server.

Note that if you want to build sql dynamically, that's fine.. BUILD A PARAMETERIZED QUERY DYNAMICALLY (c# pseudocode):

strSQL = "SELECT * FROM TABLE WHERE 1=1 ";

List<OracleParameter> tempParametersList = new List<OracleParameter>;

if(nameCheckbox.Checked){

 strSQL = strSQL & " AND name = :NAME";

 tempParametersList.Add(new OracleParameter("NAME", nameTextBox.Text));

}

...

Yeah, youre allowed to build a parameterized dynamic sql - its immune from injection, flexible and can be cached for repeated use

# Jason said on 06 February, 2007 01:24 PM

Well said Richard.  There has never, nor will there ever be a case in an enterprise application where you can consider dynamic SQL, the non parameterized verity, to be secure enough.  With the huge problem of identity theft doing nothing but getting worse, there is no excuse for leaving your application vulnerable to attack.  The thing that so many developers don’t think about is that by using raw generated SQL in your application, you not only leave your application at risk, but your clients entire system at risk.

The fundamental problem to raw generated SQL is that the entire statement is executable code.  There is no way you can protect the values entered by the user from being executed.  You can filter out most of the so called risky characters but someone will find a way around your filter.  By passing these values as parameters to the statement, you separate the executable portion of the SQL statement from the values that are entered by the user.  There is no way that a value entered by the user can be executed.

# Praveen said on 05 March, 2007 09:30 PM

It is all good about using parameterized queries.

  But what about the case wherein I need to have a single code-base(i.e. common methods) which needs to access different databases ? That is quoting the line from the article:

"...each database vendor implements parameterized queries in different ways".

  Do we have any solution for such a scenario wherein I could see only dynamic SQL string creation.

# sam garcia said on 10 September, 2007 04:24 PM

thanks you so saved me at least ten-twenty lines of code

# Mike Edenfield said on 13 September, 2007 12:10 PM

"There is zero excuse for *not* using parameterized queries.".  You should know better than to make blanket statements like this.  How would you suggest to implement this as a parameterized query:

bool BackupDb(string dbName)

{

...

cmd.CommandText = "ALTER DATABASE [" + dbName + "] SET READ_ONLY WITH ROLLBACK IMMEDIATE";

...

cmd.CommandText = "BACKUP LOG ([" + dbName + "]) WITH TRUNCATE_ONLY";

...

}

# Pablo Alarcón García said on 30 January, 2008 05:58 AM

Those are not "queries",  Mike.

Rule of Thumb: Select-Insert-Delete-Update statements are called queries, even though "querying would mean just Selecting.

These statements are those to be parametrized, and realize this is just for the WHERE clause.

In general  SqlStatements are categorized as DDL( Data Definition Language ) ones or DML(Data Modification Language ) ones. Search for those for further info.

# Mr. Mags said on 02 June, 2008 10:51 AM

Your article helped me and worked without error.  Kinda surprising for something found on the InterWebs.  Thanks and Nice jorb!

# SMM said on 02 June, 2008 03:52 PM

So maybe someone can help me with this:  Can you parameterize the ORDER BY clause of a SELECT query using the command objects?  From what I have researched, I'd guess you can't.  For example, the following query:

SELECT * FROM [Shippers] WHERE [CompanyName] LIKE ([@WhereCompanyName]) ORDER BY [@OrderPhone]

With these parameters:

Parameter(0) = 'Blah'

Parameter(1) = 'Phone' (this is the name of the column)

This returns w/o error, but the data is not ordered.  However this:

SELECT * FROM [Shippers] WHERE [CompanyName] LIKE ([@WhereCompanyName]) ORDER BY [Phone]

With one parameter:

Parameters(0) = 'Blah'

Works.  So I am thinking that you can't parameterize the ORDER BY.  Can anyone verify this please?

Leave a Comment

(required) 
(required) 
(optional)
(required)