locked
Need Help Parameterizing The Following Query RRS feed

  • 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 + '%')"



    Thursday, June 3, 2010 1:52 PM

Answers

  • 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! 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, June 4, 2010 1:01 PM

All replies

  • User-999963490 posted

    Anybody? 

    Friday, June 4, 2010 8:20 AM
  • 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? 

    Friday, June 4, 2010 12:45 PM
  • 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! 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, June 4, 2010 1:01 PM