The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Just a question/opinion - parametrizing SQL params

In general, it's recommended that we parametrize SQL parameters using the SqlParameter wrapper classes, in order to prevent SQL injecttion attacks. This makes perfect sense when we're using user-entered values while constructing the query.

However, do you really want to use this if the  query is constructed purely internally in your program - let's say you want to get the all books belonging to category '6', and I have a business API as

Books.GetAllByCategory(int categoryID){

string query = "select * from books where categoryID="+categoryID;



Why do I even need to create a parametrized object here? or is there some other reason that I don't know about?
anon for this!
Sunday, January 29, 2006
Other databases, I can't say. In the Oracle world, you can save parsing overhead, both in time and in shared pool space.
George Jansen Send private email
Sunday, January 29, 2006
In our internal encapsulated classes that depend on constants that are already type-set by the system, such as index numbers, I just assemble the SQL string inside the class and query directly. No point in validating if the only inputs all must fall in the valid set of characters.
Josh McFarlane Send private email
Sunday, January 29, 2006
I tend to parameterize even in that case because it helps me tell at a glance that the query is safe. Otherwise, I need to check and make sure that the value being concatenated comes from an internal source.

It's worth making right code look right.

Also, in the example you gave, what if somebody later calls Books.GetAllByCategory with a user-supplied and unvalidated category ID?
Sunday, January 29, 2006
I don't see what the drawback is to just paramterizing everything. It makes the code more consistent and sends a clear message to all programmers. The newbie that you hire next week doesn't even know what an SQL injection attack is let alone why your scenario may have been an instance where SQL parms wasn't needed. The other problem is cut and paste coding. People will ultimately end up using your SQL statement by copying it and will not bother to alter it to use parameters.
Turtle Rustler
Sunday, January 29, 2006
> t's worth making right code look right.

You convinced me. I have to stop being lazy now.
son of parnas
Sunday, January 29, 2006

Can't argue with your logic! Well, I own and write the code for it, but someday - someone else may extend it. Guess I have to stop being lazy :) Guess I was just trying to find an excuse for being lazy.
anon for this!
Sunday, January 29, 2006
Turtle - +1 for you too.

clcr - sending a user-input/invalid ID is not a problem, the method will return null.
anon for this!
Sunday, January 29, 2006
From the Oracle point of view, parameterizing avoids a hard-parse of the statement each time it is submitted. The major scenariuo in which you would want to avoid parameterizing is where you want the cost-based optimizer to be value sensitive in defining the optimum access path.

As an example, if you had a table of 100,000 invoices with a column to indicate that the invoice had or hadn't been printed ("Y" and "N" respectively), then 99,950 of the rows might be "Y" and 50 would be "N". If your query had the predicate ...

printed = 'N'

... then using an index on the printed column would be a Good Thing. However if the predicate said ...

printed = 'Y'

... then you would almost certainly not want to use the index (unless the query could be satisfied by accessing only the index, and not the table).

So in that case you would not parameterise the predicate on the "printed" column, although you would still parameterize other predicates.
David Aldridge Send private email
Monday, January 30, 2006
I think using parametrized queries improves code readability and reliability, so it should be preferred over string concatenation approach. Also I used to think that performance should be better because parsing is done only once. But then I came accross Paul Wilson's commment to this blog post ( and now I am not so sure:

"This used to be a valid difference, but it hasn't been in at least  MS Sql or Oracle (and probably other DBMSs) for quite some time.  Instead, these databases will actually parameterize dynamic sql  and then automatically reuse existing and already compiled query  plans just as if it had been parameterized to begin with. To take  advantage of this you simply have to make sure your dynamic sql  "looks" the same each time, except for the actual values of course  -- and if you're creating your sql using a common method (or an  O/R Mapper) then that is no big deal. Now my goal is not to argue  against using parameterized sql since it does help to protect  against sql injection, but even then its not very hard to protect  yourself even without using parameters -- so I'm simply trying to  clarify your point. Note that my O/R Mapper provides both options  (parameterized queries using OPath and concatenated dynamic sql  queries otherwise), so I'm not trying to defend either option."
End of quoation.

It sounds a bit dusturbing, because personally I have never heard of this before and did not have a chance to test it yet. It would be great if somebody could confirm it.
Monday, February 06, 2006
"The major scenariuo in which you would want to avoid parameterizing is where you want the cost-based optimizer to be value sensitive in defining the optimum access path."

Beginning with Oracle 9i, the optimizer will consider bind variables when choosing execution paths ("Oracle SQL Tuning", Mark Gurry)

Tuesday, February 07, 2006

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz