Wrox Programmer Forums
Go Back   Wrox Programmer Forums > .NET > Other .NET > General .NET
General .NET For general discussion of MICROSOFT .NET topics that don't fall within any of the other .NET forum subcategories or .NET language forums.  If your question is specific to a language (C# or Visual Basic) or type of application (Windows Forms or ASP.Net) try an applicable forum category. ** PLEASE BE SPECIFIC WITH YOUR QUESTION ** When posting here, provide details regarding the Microsoft .NET language you are using and/or what type of application (Windows/Web Forms, etc) you are working in, if applicable to the question. This will help others answer the question without having to ask.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the General .NET section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old October 16th, 2007, 10:58 AM
Registered User
Join Date: Oct 2007
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Multiple Form Fields

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 ?
Old October 16th, 2007, 11:17 AM
Wrox Author
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons

Dynamic sql =[

First here is why you should never use Dynamic SQL:

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:
================================================== =========
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
================================================== =========
Old October 17th, 2007, 04:22 AM
Registered User
Join Date: Oct 2007
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts

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 ?

Similar Threads
Thread Thread Starter Forum Replies Last Post
copy fields from Form to new form - openargs justabeginner Access VBA 1 February 4th, 2007 01:28 PM
Group on multiple fields leep Crystal Reports 2 May 21st, 2004 01:12 AM
Updating multiple Rows from multiple fields in ASP vdm_nana SQL Server ASP 0 April 1st, 2004 04:26 AM
UPDATE multiple fields in 1 form a_pathak SQL Server ASP 1 March 3rd, 2004 04:19 AM
Multiple Fields Search Helmut Classic ASP Databases 4 July 15th, 2003 02:47 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.