使用ASP.NET C＃高级搜索过滤器 [英] Advance search filter using ASP.NET C#

问题描述

嗨朋友们，



我正在尝试创建高级搜索查询，允许用户输入任意数量的过滤器。这个过滤器包含不同的Asp控件，如下拉列表和复选框。

这是我使用的查询，问题是它需要输入所有过滤器才能工作，我需要它接受任何过滤器集合，



（请在修改我的代码后发布您的答案以便更好地理解）。





检查是我的查询对吗？

检查也是我的寄生代码对吧？





感谢所有人。



我有什么试过：

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:

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());
        }

    }

推荐答案

引用：

我稍后会对paramitrized进行查询。

I will paramitrized query later.

不，不，你不会。因为它将正常工作，你将转向其他事情。因此，您的代码将始终容易受到SQL注入攻击。首先修复它，然后继续做你想要做的事情。

并且......如果你在没有参数化查询的情况下修复过滤器问题，你甚至不太可能改变你的想法它！



事实上，你可以同时修复这两个问题。

你需要做的是：创建一个StringBuilder来在你组装时保存查询，以及一个List< SqlParameter>保持参数值。添加一个整数，并将其预设为1.

编写一个方法，返回一个整数，并接受一个int，一个StringBuilder，你的列表和两个字符串：一个名为Condition，一个名为Parameter

在方法中，检查参数 - 如果它是空的或空格，则返回不变的整数。

否则，将条件字符串添加到StringBuilder，加上'@'和整数值。递增整数。创建一个SQLParameter，给它与刚刚添加到StringBuilder中的名称相同，并将其值设置为Parameter。将它添加到列表中，并返回新的整数值。

现在您只需为每个可能的搜索项调用它，完成后，设置SQL命令及其参数。



编码比用英文写的快得多！

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!

这篇关于使用ASP.NET C＃高级搜索过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！