CODEWITHSUNDEEP
vb.net
Frater
Frater

Reputation: 47

Pass Textbox string to SQL Query

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!

Upvotes: 0

Views: 2949

Answers (2)

Arin
Arin

Reputation: 1393

(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

Upvotes: 0

Brent Keller
Brent Keller

Reputation: 1435

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.

Upvotes: 1

Related Questions