Pass Textbox string to SQL Query

Question

I am trying to take my typed in text from a textbox in my aspx page and pass it to my database for a query, but I dont have any errors. And when I put a break point my ElseIf sections dont evaluate. Where did I go wrong?

Protected Sub SearchButton_Click(ByVal sender As Object, ByVal e As EventArgs) Handles SearchButton.Click
    Dim DS As New DataSet

    If SearchTextBox.Text = "" Or NameSearchTextBox.Text = "" Then
        DS = RunQuery("SELECT * FROM tblFiles WHERE Status = '" & radiolist1.SelectedValue & "'")
        GridView1.DataSource = DS
        GridView1.DataBind()
        If radiolist1.SelectedValue = "*" Then
            BindGrid()
        End If
    ElseIf SearchTextBox.Text.Length >= 1 Then
        DS = RunQuery("SELECT * FROM tblFiles WHERE Number like '%" & SearchTextBox.Text & "%'")
        GridView1.DataSource = DS
        GridView1.DataBind()
    ElseIf NameSearchTextBox.Text.Length >= 1 Then
        DS = RunQuery("SELECT * FROM tblFiles WHERE Member like '%" & NameSearchTextBox.Text & "%'")
        GridView1.DataSource = DS
        GridView1.DataBind()
    End If

End Sub

Thanks!

Brent Keller · Accepted Answer · 2014-02-27 19:08:59Z

1

You may want to move your first if condition down below the other two. The way it is now if either NameSearchTextbox or SearchTextbox is blank you use the radiolist for the query. This means you'd have to have a value in both SearchTextBox and NameSearchTextbox to get past the radiolist condition. In which case it would use the SearchTextbox query and never use the NameSearchTextbox query.

Like this:

If SearchTextBox.Text.Length >= 1 Then
    DS = RunQuery("SELECT * FROM tblFiles WHERE Number like '%" & SearchTextBox.Text & "%'")
    GridView1.DataSource = DS
    GridView1.DataBind()
ElseIf NameSearchTextBox.Text.Length >= 1 Then
    DS = RunQuery("SELECT * FROM tblFiles WHERE Member like '%" & NameSearchTextBox.Text & "%'")
    GridView1.DataSource = DS
    GridView1.DataBind()
Else
    DS = RunQuery("SELECT * FROM tblFiles WHERE Status = '" & radiolist1.SelectedValue & "'")
    GridView1.DataSource = DS
    GridView1.DataBind()
    If radiolist1.SelectedValue = "*" Then
        BindGrid()
    End If
End If

Alternatively, just change the OR to an AND and it should work.

answered Feb 27, 2014 at 19:08

Brent Keller

1,4351 gold badge14 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Frater Over a year ago

Thanks! I knew it had to be something simple but couldn't put my finger on it. Guess I should walk around for a bit. :-)

Arin · Accepted Answer · 2014-02-27 21:58:21Z

0

(I apologize profusely for not including this as a comment instead of an answer. I'm still building reputation so I'm not allowed to comment yet, but felt this point was important enough to be mentioned anyway. Feel free to delete this if it's still out of line!)

Just wanted to chime in with some friendly advice! Anytime you're putting text directly from a webpage into a SQL statement, it's critical to defend against SQL injection attacks. If someone were to type '; DROP TABLE tblFiles -- into your NameSearchTextBox, it could get rid of your table!

There's an old but still relevant article on the Microsoft Patterns and Practices website that discusses some solutions. The simplest solution is just to wrap input text in a function call like this

DS = RunQuery("SELECT * FROM tblFiles WHERE Member like '%" & StringToSQL(NameSearchTextBox.Text) & "%'")

where StringToSQL() looks like

'Sanitize SQL input to defend against injection attacks
Public Shared Function StringToSQL(rawString As String) As String
    If rawString Is Nothing Then Return Nothing 'ignore null strings
    Return rawString.Replace("'", "''")  'Make SQL treat a single quote as a literal
End Function

answered Feb 27, 2014 at 21:58

Arin

1,3932 gold badges10 silver badges24 bronze badges

1 Comment

Frater Over a year ago

Thanks Franklin, I have a sanitize method that I apply after I get all my functionality up and running the way I like it. I then go back and apply javascript to the front as part of that as well. I know most like to do everything at once, I prefer to move a step at a time with scope of responsibility.

Collectives™ on Stack Overflow

Pass Textbox string to SQL Query

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related