Fog Creek Software
Discussion Board




Welcome! and rules

Joel on Software

Where to store SQL query text

Every time I write some database components that query the database or persist information in the database, I wonder where to store the query text that is later used by a ADO.NET OracleCommand or OleDbCommand etc.

What do you people take as 'best practice'? Do you simply put the SQL text as static string in the class? Do you put them together in a resource file? Or somewhere else?

Ad

Ad Timmering
Thursday, October 14, 2004

Always use stored procedures, never raw SQL.

You can then write a database accessor class where the methods are the stored procedure names.

Nemesis
Thursday, October 14, 2004

Would you that for both SELECT statements (ie stored procs returning rowsets) and UPDATE/INSERT statements?

So far I had only been using stored procedures for changes to the database.

Thanks for the advice,
Ad

Ad Timmering
Thursday, October 14, 2004

Wherever possible, use Stored Procedures. They are superior in terms of performance, security and code maintenance.

Avoid SQL strings, especially those containing parameters inserted through concatenation. They leave your code vulnerable to injection attacks.

+=
Thursday, October 14, 2004

ditto ;-)

Nemesis
Thursday, October 14, 2004

To answer your question directly, yes, I mean use stored procedures for select statements too, not just updates/deletes.

If you can use output parameters, I think it is better than returning "resultsets" with only one row/column. For example, if you want to count something, or look up a simple value based upon a code.

Nemesis
Thursday, October 14, 2004

"If you can use output parameters, I think it is better than returning "resultsets" with only one row/column. For example, if you want to count something, or look up a simple value based upon a code."

Note that you can also do SqlCommand.ExecuteScalar to get a singular value from the db.

smallbiz
Thursday, October 14, 2004

Where possible, we use stored procs, and store the source for these in Subversion, right along with the source code for the app.

In our typical apps, 75% to 80% of the data access is done by canned/generated stored procs. The remaining 20% to 25% are of an RawSQL nature. We currently just sprinkle the raw SQL wherever it's needed in the source code. We have, over the years, come to realize this is a BadThing for us in many ways.

We're working on a new internal component we call an RawSQLCommand object that integrates into our data access objects. Basically, the raw SQL for each command is stored in an XML config file. At application initilization, our data engine reads each of the RawSQLCommands from the XML file and exposes the commands to the app through a nice, tight API. This allows us to tweak the raw SQL and not have to recompile. Simply redistribute the XML file.

It's still in development, and not yet used in production at any client sites, but we have high hopes for it!

Sgt. Sausage
Thursday, October 14, 2004

While I agree that using parameters is usually better than concatenation, it's not quite correct to say concatenation => risk of injection attacks.

The risk of injection attacks is a result of failing to quote parameters properly when concatenating.  A well designed data access layer, which for example uses helper methods for proper quoting of concatenated parameters is not any more at risk of injection attacks than one using parameters.

Joe
Thursday, October 14, 2004

Joe is perfectly correct. However, not everyone is aware of the best-practices and using SPs seems to get around this issue.

Let's face it, even the most hardened professionals are prone to these errors now and again. For example, Joel's new forum software (based on FogBUGZ) is easily manipulated to mess about with the application.

It isn't prone to injection attacks (afaik), but other attacks are possible and have been demonstrated recently.

Nemesis
Thursday, October 14, 2004

>  using SPs seems to get around this issue.

Another common misconception.  The choice of SPs vs raw SQL is orthogonal to the problem of protecting against injection attacks.

"EXEC MyProc'" + variable + "'"
is just as vulnerable to injection attacks as
"SELECT A FROM B WHERE C='" + variable + "'"

Joe
Thursday, October 14, 2004

"orthogonal, injection, nothing wrong with using strings..."

You are insane if you execute stored procedures that way. End of story.

so there!
Thursday, October 14, 2004

Yeah, come on Joe, that is not how I meant anyone should use SPs, although I suppose we shouldn't discount the potential for inappropriate use of technology in naïve developers.

Nemesis
Friday, October 15, 2004

Thanks everyone for the input. I will immediately start writing my SPs! :)  It still does feel a bit strange however to have a stored procedure that, for example, just checks for the presence of one row in a certain table, but perhaps a solution like the 'RawSQL' described by one of the posters might be good for those cases.

Cheers,

Ad

Ad Timmering
Friday, October 15, 2004

> that is not how I meant anyone should use SPs

I agree, but you could also argue that that's not how anyone should use raw SQL: in both cases it's a better practice to use parameters.

Joe
Friday, October 15, 2004

Check out Andrew Norrick's articles on Implementing CRUD Operations Using Stored Procedures.  Pretty good articels and explains why you should be using SP's

Part 1:
http://www.databasejournal.com/features/mssql/article.php/3082201

Part 2:
http://databasejournal.com/features/mssql/article.php/3099551

Steve-O
Friday, October 15, 2004

To add a response...

http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx

Clearly, you're going to get some benefit if you encapsulate a lot of data-specific code within a stored procedure, but I question if a blanket "Always use stored procedures" statement is valid.

Bryan Jonker
Tuesday, October 19, 2004

*  Recent Topics

*  Fog Creek Home