An example of advanced database search

Question

im looking for an example script. I saw one yesterday but for the life of me I can't find it again today.

The task I have is to allow the user to search 1 database table via input controls on an aspx page where they can select and , or , equals to combine fields, generating the sql on the fly with concat/stringbuilder or similar. (it runs behind the corp firewall)

Please can someone point me in the right direction of an example or tutorial

I've been working on the page, but have run into problems. Here is the Page_load;

 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    Dim sql As String = ("Select * From Table Where ")

    'variables to hold the and or values between fields
    Dim andor1v As String = AndOr1.SelectedValue.ToString()
    Dim andor2v As String = AndOr2.SelectedValue.ToString()
    Dim andor3v As String = AndOr3.SelectedValue.ToString()
    Dim andor4v As String = AndOr4.SelectedValue.ToString()
    Dim andor5v As String = AndOr5.SelectedValue.ToString()
    Dim andor6v As String = AndOr6.SelectedValue.ToString()

    'variables to stop web control inputs going direct to sql 
    Dim name As String = NameSearch.Text.ToString()
    Dim email As String = EmailSearch.Text.ToString()
    Dim city As String = CitySearchBox.Text.ToString()
    Dim province As String = ProvinceSelect.SelectedValue.ToString()
    Dim qualifications As String = QualificationsObtained.Text.ToString()
    Dim competencies As String = CompetenciesDD.SelectedValue.ToString()
    Dim expertise As String = Expertiselist.SelectedValue.ToString()

    If NameSearch.Text IsNot String.Empty Then
        sql += "Surname LIKE '%" & name & "%' "
    End If

    If EmailSearch.Text IsNot String.Empty Then
        sql += andor1v & " Email LIKE '%" & email & "%' "
    End If

    If CitySearchBox.Text IsNot String.Empty Then
        sql += andor2v & " City LIKE '%" & city & "%' "
    End If

    If QualificationsObtained.Text IsNot String.Empty Then
        sql += andor3v & " (institutionquals1 LIKE '%" & qualifications & "%') OR " & _
        "(institutionquals2 LIKE '%" & qualifications & "%') OR " & _
        "(institutionquals3 LIKE '%" & qualifications & "%') OR " & _
        "(institutionquals4 LIKE '%" & qualifications & "%') "
    End If

    Dim selectedrow As String = CompetenciesDD.SelectedValue.ToString
    Dim selectedquals As String = NQFlevel.SelectedValue.ToString
    If CompetenciesDD.SelectedValue.ToString IsNot "0" And selectedquals = 0 Then
        sql += (selectedrow & " = 1 ")

    ElseIf selectedrow = "assessortrue" And selectedquals IsNot "0" Then
        sql += andor4v & (" assessortrue=1  and assessorlvl=" & selectedquals)
    ElseIf selectedrow = "coordinatortrue" And selectedquals IsNot "0" Then
        sql += andor4v & ("coordinatortrue=1 and coordinatorlvl=" & selectedquals)
    ElseIf selectedrow = "facilitatortrue" And selectedquals IsNot "0" Then
        sql += andor4v & ("facilitatortrue=1 and facilitatorlvl=" & selectedquals)
    ElseIf selectedrow = "moderatortrue" And selectedquals IsNot "0" Then
        sql += andor4v & ("moderatortrue=1 and moderatorlvl=" & selectedquals)
    ElseIf selectedrow = "productdevelopertrue" And selectedquals IsNot "0" Then
        sql += andor4v & ("productdevelopertrue=1 and productdeveloperlvl=" & selectedquals)
    ElseIf selectedrow = "projectmanagertrue" And selectedquals IsNot "0" Then
        sql += andor4v & ("projectmanagertrue=1 and projectmanagerlvl=" & selectedquals)
    End If

    Response.Write(sql)

End Sub

After an hours tinkering the code is now looking as it does above ^

Now the problem im faced with is if a user does not enter a value for surname (the first field) but does enter a value for email (or any subsequent fields), the sql produced has an extra and like this;

Select * From Table Where And Email LIKE '%test%' 

I'm also looking for a way to take the OR option into account. Do you think this should be done as Martin says where the whole query is either an and or an or and not a mix of the 2? Then I should be able to take out all the and/or drop downs?

Thanks.

NB: I'm not really looking for comments on how I should parameterise or about sql injection.

Answer 1

To avoid sql injection and allow a dynamic search I would probably write a stored procedure something like this. If nothing is selected send DBNull.Value in the ado.net parameters collection as the parameter value. With this approach you can check any columns you want and if they are not selected by the user they will be ignored.

EDIT: I just saw that you are not allowed to use stored procedures. I changed my answer below to show a parameterized sql statement

SELECT * FROM TABLE
WHERE ([name] = @name OR @name IS NULL)
AND (email = @email OR @email IS NULL)
AND (city = @city OR @city IS NULL)
AND (province = @province OR @province IS NULL)
AND (qualifications = @qualifications OR @qualifications IS NULL)
AND (competencies = @competencies OR @competencies IS NULL)
AND (expertise = @expertise OR @expertise IS NULL)

Answer 2

Regarding your issue with users not selecting an option you could just remove the "please select" and have it default to "and"

Also what is the desired behaviour if they select a mix of ANDs and ORs?

By default the ANDs will be evaluated first in the absence of any brackets

http://msdn.microsoft.com/en-us/library/ms186992.aspx

So if they enter

name="Fred" or email="blah" and city="london" and province="xyz" or qualifications="Degree"

I'm not really sure what the desired semantics would be?

Is it

(name="Fred" or email="blah") and city="london" and (province="xyz" or qualifications="Degree")

or

(name="Fred" or (email="blah" and city="london") and province="xyz") or qualifications="Degree"

Or something different? Maybe you should restrict them to AND or OR for the whole query or allow them to disambiguate either by typing in advanced search syntax with brackets or by providing a query builder UI.

Answer 3

 Dim sql As String = ("Select * From Table Where **1=1**")

    'variables to hold the and or values between fields
    Dim andor1v As String = AndOr1.SelectedValue.ToString()
    Dim andor2v As String = AndOr2.SelectedValue.ToString()
    Dim andor3v As String = AndOr3.SelectedValue.ToString()
    Dim andor4v As String = AndOr4.SelectedValue.ToString()
    Dim andor5v As String = AndOr5.SelectedValue.ToString()
    Dim andor6v As String = AndOr6.SelectedValue.ToString()

    'variables to stop web control inputs going direct to sql 
    Dim name As String = NameSearch.Text.ToString()
    Dim email As String = EmailSearch.Text.ToString()
    Dim city As String = CitySearchBox.Text.ToString()
    Dim province As String = ProvinceSelect.SelectedValue.ToString()
    Dim qualifications As String = QualificationsObtained.Text.ToString()
    Dim competencies As String = CompetenciesDD.SelectedValue.ToString()
    Dim expertise As String = Expertiselist.SelectedValue.ToString()

    If NameSearch.Text IsNot String.Empty And andor1v IsNot "0" Then
        sql += "**and** Surname LIKE '%" & name & "%' " 
    ElseIf NameSearch.Text IsNot String.Empty And andor1v Is "0" Then
        sql += "**or** Surname LIKE '%" & name & "%' "
    End If

  ....additional logic here.....
    Response.Write(sql)

End Sub

note the ** parts. 1=1 evaluates to true on most DBMS. This allows you to just start concatenating your or / ands on to it without worrying about ()'s

Answer 4

I have done this "dynamic" type query interface on classic asp.

The advice that I give to you is that you are trying to do the whole query in one page load so...

Look to "building" the query via a "wizard" type interface - either ajax for the newness or simple multiple pages for each part of the query building.

This is essence gives you "persitance" via what ever means you have (session, dbstore, cookie etc) for each part of the query and you have can sanity check each part of the query as you build.

Answer 5

Concat strings to build a query is never a good idea. You should use a stored procedure or parametrized queries