July 24, 2011 at 3:59 am
Puzzle: how is the insert, update, delete as Stored procedures with input parameters as a table name and a string value that we're info to implement it?
July 24, 2011 at 4:08 am
Solution: dynamic SQL wrapped by a SQL injection prevention algorithm.
But the picture of the completed puzzle may look ugly...;-)
I, personally, vote against such a concept: it's hard to maintain and the overhead to protect against SQL injection usually is much larger than the "benefit".
July 24, 2011 at 6:24 am
ramsin1340 (7/24/2011)
Puzzle: how is the insert, update, delete as Stored procedures with input parameters as a table name and a string value that we're info to implement it?
Really, really bad idea. SQL injection, difficulty reading, harder to debug, little gain, lots of downsides.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 24, 2011 at 11:49 am
This would be on my list of things to avoid.
The nearest I've seen to a "safe" solution was where the tables for which these actions were allowed were stored in an explicit table.
the proc accepted an ID so the table from which the deletes took place had to be in the safe list.
The WHERE clause of the delete was fixed at WHERE DateCreated<@DataParam
All SQL was executed via sp_executeSQL. Given that this was a nightly purge job the only benefit was wrapping up the statements in a simple call rather than writing them out long-hand in the DB. caching the execution plan achieved precisely zero as by the time the next run happened the plan had aged out of the cache.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply