Hi, I want to write a custom search form using <asp:textbox>'s to query a database that does not use full-text indexing. Basically i want to dynamically append search criteria for each of the 15 or so possible textboxes, to a where clause and search on that. Users can choose which field to search on and can search on multiple fields at a time. (e.g. if they enter a client_reference in the client reference box, then it seraches for a client reference that matches it, if the search for a first name then it returns all records with that first name, if they enter both then it look for a client with that client reference and that first name) What i want to know is how i can achieve this without having to do a call to each request.form object. I would rather either on textchanged call a function that adds the contents of the textbox to the SQL, or at the end do one call to a function that loops through each field in the form. (Something like a foreach field in form fields...) Any thoughts ?

Dynamic sql =[

First here is why you should never use Dynamic SQL:
http://xkcd.com/327/

Here is the problem with your methodology:
Say, for example, I wanted to search for a client reference so i typed x string into the text box the sql would be: SELECT <columns> form <table> WHERE client LIKE '%" + textbox.text + "%'";

So then I say that I want to add another criteria, you run into a logic problem like this:

SELECT <columns> form <table> WHERE client LIKE '%" + textbox.text + "%' AND secondCritera like '%" + textbox2.text +"'";

But what if I want the sql to execute like this:

SELECT <columns> form <table> WHERE client LIKE '%" + textbox.text + "%' OR secondCritera like '%" + textbox2.text +"'";

You have a large list of possiblities where the user might want to have the logic to be AND or OR so i think you need to consider that.

Also, what if I were to put this string into one of my text boxes:
foo'; DROP TABLE users; --

Your select statement would now look literally evaluate to this:
SELECT <columns> form <table> WHERE client LIKE '%foo'; DROP TABLE Users; --' OR secondCritera like '%value'";

If the user gets away with this they could execute something like SELECT QutoeName(TABLE_NAME) FROM Information_schema.tables it would return a listing of all tables in your database and the aforementioned DROP TABLE command becomes much more scary.

I would really reconsider your approach.



================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for:
Beginning Visual C# 2008
Professional Search Engine Optimization with ASP.NET
Professional IIS 7 and ASP.NET Integrated Programming
Wrox Blox: Introduction to Google Gears
Wrox Blox: Create Amazing Custom User Interfaces with WPF and .NET 3.0
================================================== =========

I never said I would be using Dynamic SQL, perhaps saying that i wanted to dynamically build the query string was a poor choice of words. I know about SQL injection but am not worried about it as the search page will require the users to already be logged in to the site on an SSL server. Furthermore, the search box is for the support site for an administration system. The users of the support site are all approved to be using it and will not try and drop tables or anything like that. The search criteria is always an AND, I am not giving them the option to search on OR at this stage, but I may implement it later. Now do you have anything to suggest ?