[thelist] To Store or Not to Store - Building dynamic queries

[Ken Schaefer]

I would just add to this post, that the better, safer, way may be to build
your query at your app tier and use ADO.NET parameters. That avoids the SQL
Injection security issue.

Cheers
Ken

: -----Original Message-----
: From: thelist-bounces at lists.evolt.org [mailto:thelist-
: bounces at lists.evolt.org] On Behalf Of Ken Schaefer
: Sent: Tuesday, 15 August 2006 11:20 AM
: To: thelist at lists.evolt.org
: Subject: Re: [thelist] To Store or Not to Store - Building dynamic queries
: 
: Just be aware that this type of sproc ***reeks*** of SQL Injection
: vulnerabilities. You need to be ***very*** careful in your app tier that
: you
: aren't compromising your application.
: 
: Cheers
: Ken
: 
: --
: My IIS Blog: www.adOpenStatic.com/cs/blogs/ken
: Tech.Ed Sydney: learn all about IIS 7.0 - See you there!
: 
: 
: : -----Original Message-----
: : From: thelist-bounces at lists.evolt.org [mailto:thelist-
: : bounces at lists.evolt.org] On Behalf Of Brian Cummiskey
: : Sent: Tuesday, 15 August 2006 7:10 AM
: : To: thelist at lists.evolt.org
: : Subject: Re: [thelist] To Store or Not to Store - Building dynamic
: queries
: :
: : Casey Crookston wrote:
: : > Brian - thanks for your help.  I wasn't too clear on what I was really
: : > asking:  Will this kind of syntax work in a stored proc?
: : >
: : > if @param1 > ''
: : >
: : > set @queryString = 'SELECT * FROM table WHERE ' + @whereString
: : >
: : > Thanks!
: : >
: : > Casey
: : >
: : >
: : No, you can't do math on a string like that.  (greater than space?)
: :
: :
: : If you have something like:
: :
: : search: <select name="srchtype"><option
: : value="books">Books</option><option value="cd">CDs</option></select>
: : for: <select name="srcw"><option value="title">Title</option><option
: : value="author">Author</option></select>
: : <input type="text" name="textfield" />
: :
: : You could do something like this:
: :
: : pass srchtype and srcw in as a parameter to the SP (this will depend on
: : your scripting language)
: :
: : then in the SP,
: :
: : set @queryString = 'SELECT * FROM '
: :
: :
: : if @srchtype = 'books'
: :     begin
: :         set @queryString = @queryString + 'BOOKSTABLE'
: :              if @srcq = 'title'
: :                 begin
: :                    set @queryString = @queryString + ' WHERE title = '
: : @textfield
: :                   else
: :                      set @queryString = @queryString + ' WHERE author =
: : ' @textfield
: :                 end
: :           end
: :        end
: : end
: :
: :
: : That's just quick and probably missing some quotes in the syntax, but i
: : think you get the idea.
: 
: --
: 
: * * Please support the community that supports you.  * *
: http://evolt.org/help_support_evolt/
: 
: For unsubscribe and other options, including the Tip Harvester
: and archives of thelist go to: http://lists.evolt.org
: Workers of the Web, evolt !