 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
.NET Forum / .NET Framework / ADO.NET / May 2005Best practices for dynamic user searches
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 > Hi group, I'm building a high traffic web portal that will present a > sizeable [quoted text clipped - 10 lines] > > Thanks for any thoughts! 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 > We've built a really flexible searching mechanism into our application > that uses dynamic SQL. It automatically generates the WHERE and GROUP BY [quoted text clipped - 24 lines] >> >> Thanks for any thoughts! 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. > Doesn't have to worry about injection attacks because Parameterized > queries take parameters, and those parameters aren't executed as SQL [quoted text clipped - 33 lines] >>> >>> Thanks for any thoughts! 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 > 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 [quoted text clipped - 38 lines] > >>> > >>> Thanks for any thoughts! Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.