Click here to Skip to main content
15,300,338 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi friends,

I am trying to create the advanced search query , that allow the users to enter as many filters as they want. this filter contain different Asp controls like dropdownlist and checkbox.
This is the query that i use , the problem is that it require the enter all filter to work ,i need it to accept any collection of filters ,

(please please post your answers with modifying my code for better understanding).


Check is my query right ?
Check also is my paramitrized code right ?


THANKS TO ALL.

What I have tried:

C#
public partial class Searchpage : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection();
    string price1;
    string price2;
    string osx;
    string checktwog;
    string checkthreeg;
    string checkfourg;
    string phonetype;
    string cam;
    string ram;
    string q;
    protected void Page_Load(object sender, EventArgs e)
    {
        con.ConnectionString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
        con.Open();
        try
        {
           price1 = Convert.ToString(Session["price1"]);
           price2 = Convert.ToString(Session["price2"]);
           osx = Convert.ToString(Session["osx"]);
           checktwog = Convert.ToString(Session["checktwog"]);
           checkthreeg = Convert.ToString(Session["checkthreeg"]);
           checkfourg = Convert.ToString(Session["checkfourg"]);
           phonetype = Convert.ToString(Session["phonetype"]);
           cam = Convert.ToString(Session["cam"]);
           ram = Convert.ToString(Session["ram"]);
           
           q = "SELECT * FROM legacy WHERE [price] >= @prc1 AND [price] <= @prc2 AND [os] = @systm AND [gprsedge] = @gtwog AND [threeg] = @gthreeg AND [fourg] = @gfourg AND [touchscreen] = @touchtype AND [camera] = @ccamera AND [ram] = @rram";
           SqlDataSource1.SelectCommand = q;
           SqlDataSource1.DataBind();
           lbl_item_page.Text = Convert.ToString(checktwog);
           SqlCommand comm = new SqlCommand(q, con);
           comm.Parameters.AddWithValue("@prc1", price1);
           comm.Parameters.AddWithValue("@prc2", price2);
           comm.Parameters.AddWithValue("@systm", osx);
           comm.Parameters.AddWithValue("@gtwog", checktwog);
           comm.Parameters.AddWithValue("@gthreeg", checkthreeg);
           comm.Parameters.AddWithValue("@gfourg", checkfourg);
           comm.Parameters.AddWithValue("@touchtype", phonetype);
           comm.Parameters.AddWithValue("@ccamera", cam);
           comm.Parameters.AddWithValue("@rram", ram);
           
        }
        catch (Exception ex)
        {
            
            Response.Write(ex.ToString());
        }

    }
Posted
Updated 19-Sep-18 7:29am
v2
Comments
F-ES Sitecore 23-Nov-16 9:52am
   
Check if the variables have any valid data before adding them to the where clause

q = "select * from legacy where "
if (!string.IsNullOrWhitespace(price1))
{
q = q + "price >= @price and "
}

and so on, then strip any "and " text at the end of the string and you'll have a query that only contains the things you are interested in. You'll need to cater for when there are no valid params etc too.
}

1 solution

Quote:
I will paramitrized query later.

No. No, you won't. Because it will be "working" and you will move on to other things. And as a result, your code will always be vulnerable to SQL Injection. Fix it first, then move on to doing what you are trying to do.
And ... if you fix your filter problem without parameterizing the query, it's even less likely that you will get round to changing it!

In fact, you can fix both problems at the same time.
What you need to do is: create a StringBuilder to hold the query as you assemble it, and a List<SqlParameter> to hold the parameter values. Add an integer, and preset it to 1.
Write a method, that returns an integer, and accepts an int, a StringBuilder, your list, and two strings: one called Condition, and one called Parameter
In the method, check Parameter - if it's empty or whitespace, return the integer unchanged.
Otherwise, add the condition string to the StringBuilder, plus a '@' and the integer value. Increment the integer. Create an SQLParameter, give it the same name as you just added to the StringBuilder, and set it's value to the Parameter. Add it to the list, and return the new integer value.
Now you just call that for each possible search term, and when you are done, set the SQL command and its parameters.

It's a lot quicker to code than to write out in English!
   
Comments
navi G 23-Nov-16 9:44am
   
ok ok thanks but with this query what is wrong ? i mean if i want to do with this query ?
OriginalGriff 23-Nov-16 9:52am
   
You don't want to do it with that query, trust me.
If you leave it like that, anyone, anywhere in the world, can delete your database just by typing in boxes.
If you leave it vulnerable, you had better get used to taking regular, comprehensive backups, and like restoring. Because you are going to spend a good amount of time doing just that...
navi G 23-Nov-16 9:51am
   
and help me to create this code how to do it paramitrized ? Please edit my code with paramitrized. please because i don't know how to do it.
OriginalGriff 23-Nov-16 10:25am
   
No: it's your job, or your homework - not mine.
This isn't complex: it's all spelled out for you.
Give it a try - how do you expect to learn anything if you don't?
navi G 23-Nov-16 10:13am
   
Will you help me OriginalGriff ?
navi G 23-Nov-16 10:29am
   
ok thank you very much friend.............i appreciate your help.
navi G 23-Nov-16 10:34am
   
anyways i will ask this question in my next post.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900