March 5, 2012 at 9:01 am
I'm considering using dynamic sql for a particular stored procedure. This procedure is called from a web page that allows users to specify a variety of search criteria, all of which are "and-ed" together.
The criteria are such that different choices may result in very different query plans. At this point, I have the query hint "optimize for unknown" in the procedure and the where clause is built to ignore unspecified parameters like so:
WHERE col_a = ISNULL(@col_a, col_a)
(Yes, I'm aware that kind of where clause only works if col_a is not null. In this case, all of the search columns are not null in the table definition.)
I'm considering dynamic SQL, but I'm not sure how to prevent SQL injection in this case.
Several of the possible parameters are well-defined. The user chooses from a set of possible options. The web page has event validation turned on, meaning the user can't substitute a value that wasn't presented to him. However, even these parameters aren't safe because certain users can enter any free text for some of these values on another screen. The insert/update won't have SQL injection issues, but if those values are stored in the database, and then used in a dynamic where clause, there could be trouble.
My biggest issue is the "where name starts with" condition. The name field is VARCHAR(50) and contains a full name, usually in the "last, first" format. Currently, the name field contains alphanumeric characters, spaces and the following punctuation: ,.'-()
I don't see how I can possibly ensure that the "name starts with" doesn't contain injection while allowing valid possibilities.
Does anyone know if dynamic SQL is safely possible in this particular situation?
March 5, 2012 at 9:08 am
Yes, it's absolutely possible
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
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
March 6, 2012 at 8:45 am
Thanks for the link. The site is blocked by my employer, so I'll take a look at it from home some time.
March 7, 2012 at 7:07 am
Or use a free proxy server somewhere on 'the net'. This article is an absolute 'must read' related to your work. You should not have to read it from home.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply