Need Help Parameterizing The Following Query

Question

User-999963490 posted

Need help parameterizing the following query

Please note that the query works as intended WITHOUT the parameters so I don't believe there is a syntax error

I want to parameterize in order to protect against Injection attacks

 

        Dim search1 As String = search1Txt.Text
        Dim search2 As String = search2Txt.Text
        search1 = Replace(search1, "'", "''")
        search2 = Replace(search2, "'", "''")

        If search2 <> "" And search1 <> "" Then
            If search1DDL.Text = "Contact/Company" Then
                addybookADS1.SelectCommand = "SELECT * FROM [addressbook] WHERE " + search2DDL.Text + " LIKE '%" + search2 + "%' " + andorDDL.Text + " (COMPANY + ' ' + CONTACT + ' ' + LASTNAME + ' ' + EMAIL LIKE '%" + search1 + "%')"
            ElseIf search1DDL.Text = "SLS" Then
                addybookADS1.SelectCommand = "SELECT * FROM [addressbook] WHERE SLS LIKE '%" + search1 + "%' " + andorDDL.Text + " " + search2DDL.Text + " LIKE '%" + search2 + "%'"
            ElseIf search1DDL.Text = "Type" Then
                addybookADS1.SelectCommand = "SELECT * FROM [addressbook] WHERE TYPE LIKE '%" + search1 + "%' " + andorDDL.Text + " " + search2DDL.Text + " LIKE '%" + search2 + "%'"
            ElseIf search1DDL.Text = "Phone Number" Then
                addybookADS1.SelectCommand = "SELECT * FROM [addressbook] WHERE " + search2DDL.Text + " LIKE '%" + search2 + "%' " + andorDDL.Text + " (PHONE1 LIKE '%" + search1 + "%' OR PHONE2 LIKE '%" + search1 + "%' OR PHONE3 LIKE '%" + search1 + "%' OR FAX LIKE '%" + search1 + "%')"
            End If
        ElseIf search2 = "" And search1 <> "" Then
            If search1DDL.Text = "Contact/Company" Then
                addybookADS1.SelectCommand = "SELECT * FROM [addressbook] WHERE (COMPANY + ' ' + CONTACT + ' ' + LASTNAME + ' ' + EMAIL LIKE '%" + search1 + "%')"
            ElseIf search1DDL.Text = "SLS" Then
                addybookADS1.SelectCommand = "SELECT * FROM [addressbook] WHERE SLS LIKE '%" + search1 + "%'"
            ElseIf search1DDL.Text = "Type" Then
                addybookADS1.SelectCommand = "SELECT * FROM [addressbook] WHERE TYPE LIKE '%" + search1 + "%'"
            ElseIf search1DDL.Text = "Phone Number" Then
                addybookADS1.SelectCommand = "SELECT * FROM [addressbook] WHERE (PHONE1 LIKE '%" + search1 + "%' OR PHONE2 LIKE '%" + search1 + "%' OR PHONE3 LIKE '%" + search1 + "%' OR FAX LIKE '%" + search1 + "%')"
            End If
        ElseIf search2 <> "" And search1 = "" Then
            addybookADS1.SelectCommand = "SELECT * FROM [addressbook] WHERE " + search2DDL.Text + " LIKE '%" + search2 + "%'"
        ElseIf search2 = "" And search1 = "" Then
            addybookADS1.SelectCommand = ""
            addybookGV.DataBind()
            addybookGV.Visible = False
            nosearchPanel.Visible = True
            nosearchLbl.Text = "Please enter some search criteria"
        End If

 Here is the relevant HTML

    <asp:DropDownList ID="search1DDL" runat="server">
        <asp:ListItem Text="Contact/Company" />
        <asp:ListItem Text="Phone Number" />
        <asp:ListItem Text="SLS" />
        <asp:ListItem Text="Type" />
    </asp:DropDownList>
    <asp:TextBox ID="search1Txt" runat="server" />
    <asp:DropDownList ID="andorDDL" runat="server" SkinID="AndOrDDL">
        <asp:ListItem Text="And" Selected="True" />
        <asp:ListItem Text="Or" />
    </asp:DropDownList>
    <asp:DropDownList ID="search2DDL" runat="server">
        <asp:ListItem Text="City" />
        <asp:ListItem Text="Company" />
        <asp:ListItem Text="Contact" />
        <asp:ListItem Text="Country" />
        <asp:ListItem Text="Focus" />
        <asp:ListItem Text="SLS" />
        <asp:ListItem Text="State" />
        <asp:ListItem Text="Title" />
        <asp:ListItem Text="Type" />
        <asp:ListItem Text="Zip" />
    </asp:DropDownList>
    <asp:TextBox ID="search2Txt" runat="server" />
    <asp:Button ID="searchBtn" runat="server" Text="Search" CausesValidation="false" />

    <asp:AccessDataSource ID="addybookADS1" runat="server" DataFile="~/App_Data/pelas.mdb"
        DeleteCommand="DELETE FROM [addressbook] WHERE [ID]=@ID" CancelSelectOnNullParameter="false">
        <DeleteParameters>
            <asp:Parameter Name="ID" />
        </DeleteParameters>
        <SelectParameters>
            <asp:ControlParameter ControlID="search1Txt" PropertyName="Text" Name="Search1"  />
            <asp:ControlParameter ControlID="search2Txt" PropertyName="Text" Name="Search2" />
        </SelectParameters>
    </asp:AccessDataSource>

  When I try changing my query to the following, for example, the search returns nothing 

addybookADS1.SelectCommand = "SELECT * FROM [addressbook] WHERE " + search2DDL.Text + " LIKE '%' + @Search2 + '%' " + andorDDL.Text + " (COMPANY LIKE '%' + @Search1 + '%' OR CONTACT LIKE '%' + @Search1 + '%' OR LASTNAME LIKE '%' + @Search1 + '%' OR EMAIL LIKE '%' + @Search1 + '%')"

Answer 1

User-999963490 posted

Anybody? 

Answer 2

User-999963490 posted

Also, the code works if I use the @Search1 parameter and then search2 (the textbox value) in my SelectCommands 

When I try to use @Search1 and @Search2 parameters at the same time in my SelectCommands, there are no search results (but no errors)

Calling @Search2 seems to be the problem

What am I doing wrong? 

Answer 3

User-999963490 posted

Was able to reach my goal by adding the following:

Dim s2CP As ControlParameter = addybookADS1.SelectParameters(1)
s2CP.DefaultValue = search2
Dim s2 As String = s2CP.DefaultValue

And then calling s2 in the SelectCommand... I feel there like should be a cleaner way to do it but this will do for now.

I'm still open to suggestions though so feel free to plug away!