Best practices for dynamic user searches

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi group, I'm building a high traffic web portal that will present a sizeable
range of search options to the user. My plan is to use dynamic parameterized
queries (as opposed to stored procedures) since you never know how many
search arguments a user will enter. I could create a stored procedure to
accept variable arguments and then assemble the queries, but this defeats one
of the prime benefits of a stored procedure - plan caching. Portal searches
are a very common feature, is using dynamic SQL with parameters the
recommended approach for a site that must perform?

Thanks for any thoughts!
 
We've built a really flexible searching mechanism into our application that
uses dynamic SQL. It automatically generates the WHERE and GROUP BY clauses
from information the client submits. The security issue you end up having to
deal with is protecting yourself from SQL injection attacks. First off make
sure that the database user the app runs under can only execute stored procs
(to update data) and SELECT statements on the database. Then you'll need to
be very careful about cleaning up any of the submitted information before
you generate the query strings. Here's a good article to get you started:
http://www.sitepoint.com/article/sql-injection-attacks-safe

HTH,
-B
 
Doesn't have to worry about injection attacks because Parameterized queries
take parameters, and those parameters aren't executed as SQL Statements
themselves...

Unless I'm wrong...

Mythran
 
No you are correct. If you are just filling in values and creating command
parameters then ADO.NET takes care of the issue. However if you are creating
dynamic SQL statements (creating the field lists, or entire where and order
by clauses) then you will need to be careful.
 
If the parameters are known but optional, you can still use a stored
procedure and benefit from the plan caching. For example, if a user can
search by first name, last name, address, phone, or email where each one is
optional you can definitely use a stored procedure by checking if the
optional parameters are null. But I suspect J already knows about that.

If some of the parameters can have multiple values, I would package the
parameters in an xml string and pass it in as a text parameter to a stored
procedure. The stored procedure would use OpenXml to place those values into
a table and then do the search. More info can be found here:
http://www.sql-server-performance.com/jb_openxml.asp

Not sure if I'm addressing understood your problem correctly. Hope this helps.


Cheng Yuan Yap Ye
http://kuantanzai.blogspot.com
 
Back
Top