Just a couple quick hits from this weekend's code camp.

Firstly, I'll have the materials from all three of my sessions posted on either Wed. or Thursday of this week.  I've got another presentation Tuesday night at the San Diego .NET User Group which I'm currently focusing on, but you are welcome to attend that meeting it's down in San Diego here's their website for more information: http://www.sandiegodotnet.com

Secondly after my session on Visual Studio 2005 and SQL Server 2005 I was asked a pair of questions.  The first question was "We are seeing tools put the T-SQL text of database statements into our source code.  Is this as good as using stored procedures?".  The answer to this question was easy.  Never use text based queries in your code as that leaves the possibility that you will be susceptible to SQL Injection.  There are several tools that will try to help ensure that this isn't possible by blocking special characters etc.  but the fact is the easiest solution which is also one of the most performance enhancing is to ALWAYS USE STORED PROCEDURES.

The second question they asked however caught me off-guard.  It was "We've seen that Microsoft tends to separate the Create, Update and Delete statements into separate stored procedures.  However, other tools have combined these with a simple flag to indicate which action should be taken.  Is there a specific reccomendation?".  Here my answer was that I couldn't think of anything specific and that although I always separated the items I couldn't think of a reason that mattered beyond style.  In this case the person asking was saying they liked to have a smaller number of stored procedures.... which has it's own disadvantage with multiple developers.   But anyway after the day was over and I was driving home the correct answer came to mind, which is that you should NOT COMBINE the CREATE, UPDATE, and DELETE statements in a SINGLE STORED PROCEDURE.

The reason is quite simple, security (again).  In this case it has to do with the fact that you of course are not using your 'SA' account for your website.  You should be using an account with limited permission (for a whole host of reasons).  So what's the big deal, well in most cases we design systems that don't allow a user to Delete an entry from one of our tables.  So if I have a stored procedure that creates my entries and I of course give anonymous or external customers access to that stored procedure then that's in the scope of what I expect a hacker might at somepoint compromise and I accept that risk.  However, if that stored procedure also contains the entry delete logic, then I have no way of limiting a user's permissions at the database level to prevent a hacker who might violate a small portion of my application's logic from doing significant damage to my application.  By separating out the Create, Update and Delete logic, I can create a security model which will prevent an anonymous hacker who get's unfettered access to my create logic from also being able to delete valid data from my database.

That's the short of what I consider to be a very good reason to not combine your Create, Update and Delete logic in a single stored procedure.