Generic Dynamic DML stored procedures
-
I have been given my first project to do the database design and administration for this new website, written in PHP with a SQL server backend. Today, the developers came to my boss and said they needed four generic stored procedures, each one would do a separate task, insert, update, delete and select. They delevelopers would do data validation on there end, and pass as "," and "|" delimited strings, table name, column names, and column values and in the case of update, delete and select, the where clause. The store procedure would then take this information and create a dynamic sql statement that would execute and return the id/'s. My exposure to dynamic sql is minimal, but yesterday, I did read the famous article, I think called, "Blessings and disadvantages of Dynamic SQL." I am not sure how to do this, or even if a one size fits all can be done. However, there is an Oracle group in our shop that has done something similar, at least with insert, but they had some crude checks in place (ie querying system tables to make sure the datatypes were correct for the parsed values, checking constraints, and counting columns and making sure that there were the same number of columns and parsed data). My boss is not hearning the same alarm bells are just going off in my head all over the place. Concerns I have are : 1) reliance on programmers to pass in the correct information to the sp. 2) reliance on programmers to do the necessary data validation or put in a whole slue of check constraints on the tables 3) the relying of perfect parsing of varing strings 4) when things go wrong, if will be found after the fact and it will be my responsibilty to clean up and fix, and at that point since it is dynamic, good luck finding it. I don't know that much about web design or PHP, but my gut says this is not the correct approach, but I need more than that to go my boss. I should tell you that all though I know these are not best practices, our shop usually grant the website user data reader and data writer permission, most things are done in inline SQL and some dynamic sql from web code that is checked by DBA's first for tuning and sent through QA. More frequest tasks are put into sp's. Any help or experience doing something similar to this would be great. Thanks.
-
My major concerns right up front would be: 1) Potential SQL injection issues and 2) Performance. Not to mention just potential problems in general.
Sounds like someone over there wants to combine best practices (stored procedures) with worst practices (dynamically building every DML statement). I guess the assumption is that if you combine the best practices with the worst practices you'll end up with "average practices"? This is why that's wrong: You won't get the benefits you would get if you were using parameterized sp_executesql statements (protection from SQL injection, precompilation) in your SP since you're dynamically building a different non-parameterized statement each time. Also you're opening yourself up for a ton of errors in general from the application layer and a heck of a time with maintenance, troubleshooting and debugging.
With all that in mind, they might as well just build the SQL statements dynamically on the Application layer and send the statement. What's the point of complicating it by introducing an SP that's not going to offer any benefits over a simple string-concatenation function they can do in the Application layer?
BTW if you want to split a string into an "array" there are several ways to do it. Here are two:
http://blogs.sqlservercentral.com/blogs/michael_coles/archive/2006/03/10/531.aspx
http://blogs.sqlservercentral.com/blogs/michael_coles/archive/2006/03/09/529.aspx
-
Thank you Mike.
I appreciate your feedback.
-
Just in case your boss needs more than one opinion, I'm going to agree with Mike. This seems like, honestly, the worst possible solution. Why can't the webmonkeys just ask you to create sp's to do the tasks they need? Do they really have that many tasks with that much fluidity that they can't predict what kind of queries they'll need to run?
If so, dynamic SQL on their end is going to be much better than trying to kludge together a dynamic SQL middleware tool.
-
Agreed with Marshall.
A good general guideline is the 80/20 rule for SP's; i.e., count on 20% of your code/queries to be used 80% of the time. Converting that 20% of your code to SP's will give you the biggest performance boost. If you have a certain query that's run against a certain table 10,000 times per day and another query that's run about once per week, converting the former to an SP would make much more sense than converting the latter. But you can easily kill any performance benefits if you add in a lot of dynamic SQL.
I'd ask the developers to sit down and think through which queries they are planning to send, and then prioritize those queries for you. Then convert the highest priority queries to their own SP's. If you use SP's and parameterized queries you get the benefits of cached execution plan re-use. If you dynamically build query strings, you lose that benefit.
Also I would suggest your developers look into parameterized queries, as they offer some of the same benefits as SP's, and the developers can create the queries themselves on the application layer or in the middle tier.
And if any of your developers or management have a problem with that, tell them to fire me an email and we can set up a conference call LOL
Do me a favor and let us know how it turns out -
Thank you Mike and Marshall. With your helpful insight, this idea was put to rest today (Thank Goodness!!!). We are going to use the old fashion way of putting the most common queries in stored procedures and writing the lesser used queries into webcode. There is just such a strong push here for re-usable - plug and play code. It's crazy!
Thank You Again.
Erin
-
No problem. Just remember, reusable doesn't necessarily equal stored procedures. If you're building a 3-tier or n-tier app, you can use parameterized queries to great effect in the middle tier(s).
Good deal, and glad to hear it came to a reasonable conclusion... too often it doesn't
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply