SQL Injection in stored procedure
-
Sorry, for jumping right in
1. Remove dynamic queries and write select, insert and update seperate procs for all the lookup tables (about 20).
Not that much at all, so go for it.
2. Keep dynamic query and validate input properly.
Forget the dynamic query and still validate input.
3. change structure to merge all lookup tables and add parent table keeping info of table or lookup table type. Whole app will be affected and don't want to do this.
This is at best a kludge and against the nature of tables. You can see such nightmare where you look at spt_values in master (Yes, even the guys at MS are sometimes far from being perfect
) In short, don't do this.--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Good advice, Frank provides.
The need for dynamic SQL grows strong, when database is poorly designed! Resist, for this leads to the dark side!
Joins, conditional they should not be. If lookup value is not required, do it anyway you shall ! Confuse not returning columns A,B,C in a resultset, with showing columns A,B and C on a report! Return them all you might, but show them all the client may not!
To confuse presentation with data, a fast path to the darkest regions of the force!
Should all lookups not match, LEFT OUTER JOINs you will produce! Should conditions determine from which other table to assign to a column, a CASE statement you shall use!
All are powerful tools of the jedi ...
Viewing 2 posts - 31 through 31 (of 31 total)
You must be logged in to reply to this topic. Login to reply