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

[Casey Crookston]

-----Original Message-----

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.

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

------------------------


Ok Ken,

Thanks for your help.  Here's the stored procedure I'm using now:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE Procedure getSearchResults2
(
@search nvarchar(4000)
)
as
SELECT listing_id, RANK, name, address, city, zip, heading, phone 
FROM listings a, 
FREETEXTTABLE(listings, *, @Search)
WHERE [KEY] = a.listing_id 
ORDER BY RANK DESC, name

GO
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Now, I need to modify this to allow for the user having defined a city
to search within.  This would be the obvious addition (assuming @city is
declared) :

WHERE [KEY] = a.listing_id AND city LIKE @city

But, this does not allow for the possibility the user might have NOT
defined a city, in which case the "AND city LIKE @city" clause needs to
not exist.

So, I'm back to my original question: How do I dynamically include "AND
city LIKE @city" in my stored proc?

Thanks,

Casey