DB Search Command

[AirlineSim]

I've got a searh form with about 8 different boxes where a user can enter data to search from. To save writing many different search commands I want to be able to write one and if the field is empty then search all records

for example

SELECT * FROM MyTable WHERE CarType = CarType.Text And Color = Color.Text

If a user enters a Color then search it otherwise don't narrow down the results so like this

SELECT * FROM MyTable WHERE CarType = ""

How can I do this? would it be like the % sine if Color.Text is = String.Empty??

[cicatrix]

Let's suppose you have 3 fields in your DB (Field1, Field2 and Field3)
And you have 3 textboxes on your form and a Submit button.

Then we can design something like this.

Dim sql, condition1, condition2, condition3 As String

Private Sub TextBox1_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
     If TextBox1.Text = "" Then 
          condition1 = "(TRUE)" 
     Else
          condition1 = String.Format("Field1='{0}')", TextBox1.Text)    
     End If
End Sub

Private Sub TextBox2_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
     If TextBox1.Text = "" Then 
          condition2 = "(TRUE)" 
     Else
          condition2 = String.Format("Field2='{0}')", TextBox2.Text)    
     End If
End Sub

Private Sub TextBox3_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
     If TextBox1.Text = "" Then 
          condition3 = "(TRUE)" 
     Else
          condition3 = String.Format("Field3='{0}')", TextBox3.Text)    
     End If
End Sub

Private Sub btnSumbit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
    sql = "SELECT * FROM MyTable WHERE " _
                & condition1 & " AND " _
                & condition2 & " AND " _
                & condition3 & ";"
  
     ' Submit your sql

End Sub

If all textboxes are empty then your sql will look like:

SELECT * FROM MyTable WHERE (TRUE) AND (TRUE) AND (TRUE);

[si_the_geek]

A better idea is to not check anything at all, by not putting it in to the SQL statement in the first place.

You can see a VB6 example (so wrong syntax, but the right idea) in the "Further Steps" link in my signature.

[AirlineSim]

that link does not work

[si_the_geek]

What do you mean by "does not work"? Are you getting an error message of some sort when you click on it?

I just tested in two browsers (even when logged out) and it worked fine in both.

[AirlineSim]

It directs me here http://www.microsoft.com/visualstudi...al-studio-2010

can you post the full URL for me

[si_the_geek]

Did you click the adword link in my post, rather than the "Further Steps" link in my signature I told you about?

The URL for it is: http://www.vbforums.com/showthread.php?t=551154

[AirlineSim]

I know how to add more Fild = 'blaa blaa' but when there is more than one WHERE how can I add the AND inbetween?

For example

Dim strSQL As String, strWhere As String = ""
strSQL = "SELECT * FROM MyTable"

If MeMe.Text <> "" Then strWhere = strWhere & " WHERE MeMe= '" & MeMe.Text & "'"
If Options.Text And strWhere <> "" Then strWhere = strWhere & " Type = '" & Options.Text & "'" Else strWhere = strWhere & "AND Type = '" & Options.Text & "'"

Dim NewSQL As String = ""
If strWhere <> "" Then
NewSQL = strSQL & " WHERE " & strWhere
End If

[cicatrix]

USE the example I showed you.

[jmcilhinney]

USE the example I showed you.

Never use string concatenation to build SQL statements if it can possibly be avoided. E.g.

Dim query As String = "SELECT * FROM Table1 WHERE @Column1 = '' OR Column1 = @Column1 AND @Column2 = '' OR Column2 = @Column2"
Dim command As New SqlCommand(query, connection)
 
command.Parameters.AddWithValue("@Column1", Me.TextBox1.Text.Trim())
command.Parameters.AddWithValue("@Column2", Me.TextBox2.Text.Trim())

This is one option so that an empty TextBox will match every row. Alternatively you could write some conditional code that won't add the parameters at all for empty controls. No point handling the TextChanged events though. Just get all the values once when you execute the query.

[cicatrix]

Never use string concatenation to build SQL statements if it can possibly be avoided.

OK. What can possibly go wrong with the code I provided?

Well, I admit that a user can use characters ' or " in the textbox and exploit some SQL injection but this should be done by filtering user input. What's wrong with the concatenation itself?

[jmcilhinney]

OK. What can possibly go wrong with the code I provided?

Well, I admit that a user can use characters ' or " in the textbox and exploit some SQL injection but this should be done by filtering user input. What's wrong with the concatenation itself?

You just described the downside. What's the upside? There isn't one, so why not just do it the right way? That way you're safe from the problems you anticipate and those you don't.

[cicatrix]

I still can't see how your example in the post #10 gets around the SQL injection risk.

[jmcilhinney]

I still can't see how your example in the post #10 gets around the SQL injection risk.

The difference is that your code is processed on the client, while mine is processed on the server. You are inserting literal values into the SQL code first, then sending that SQL code to the server. Let's say that you have this code:

Dim query = "SELECT * FROM SomeTable WHERE SomeColumn = '" & someTextBox.Text & "'"

If the user then types the following into that TextBox:

'; DELETE FROM SomeTable; SELECT * FROM SomeTable WHERE SomeColumn = '

That literal string will be inserted into your SQL code before sending it to the server, so the actual SQL code you execute will be:

SELECT * FROM SomeTable WHERE SomeColumn = ''; DELETE FROM SomeTable; SELECT * FROM SomeTable WHERE SomeColumn = ''

Congratulations! You just deleted every record in your table courtesy of SQL injection.

Now, if you had used a parameter:

Dim query = "SELECT * FROM SomeTable WHERE SomeColumn = @SomeColumn"

then that is the SQL that would be sent to the server. The server would then compare the value of the parameter, i.e. the value the user entered, to the values in the SomeColumn column. That value is treated as text rather than executed as SQL code, so no harm can be done.

[cicatrix]

Oh, I see it now, thanks.

I just thought that the SQL statement is formed on the client side in both cases.

[si_the_geek]

The important thing is that the items inside Parameters can only ever be treated as data - which not only means that it is safer, but you also don't need to have code to validate that the input (such as dealing with the ' character, etc).

You also don't need to worry about formatting values within the SQL statement (such as forcing dates to US or ISO formats, and numbers to use . as the decimal point), because that is all dealt with in other ways by the Parameters.

While Parameters are certainly a very good idea in any situation (including this one), they don't need to exclude string building in general - arguably the best way to deal with this situation is to build the conditions in the string (with placeholders for the parameters), and then add the actual values with Parameters.

I know how to add more Fild = 'blaa blaa' but when there is more than one WHERE how can I add the AND inbetween?

There should not be more than one Where, you clearly didn't pay enough attention to the examples in the link I gave (one of which contains multiple conditions).

[AirlineSim]

Could I not do this

Dim Search as String = "%"
If Textbox1.Text =String.Empty Then
Search = "%"
Else
Search = Textbox1.Text
End If

Wont the % sign find anything??

[GaryMazzone]

Of course not if there is a % you need to use a LIKE search not an equal serach

Where FileName LIKE '%'

[si_the_geek]

..and it also won't work unless the field is a text based data type.

[AirlineSim]

Well I have this and it does not work when nothing is entered into textbox1.text

Dim BDLocation As String = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=C:\Users.mdb"
            Dim connection As New OleDb.OleDbConnection(BDLocation)
            connection.Open()
            Dim command As New OleDb.OleDbCommand("SELECT * FROM Users WHERE Username = '' Or Username = @data", connection)
            command.Parameters.AddWithValue("@data", TextBox1.Text.Trim())

            Dim reader As OleDb.OleDbDataReader = command.ExecuteReader
            While reader.Read
                ListBox1.Items.Add(reader("Username"))
            End While
            connection.Close()

[GaryMazzone]

Can UserName ever have an empty string in it. And remeber that NULL data is not the same as ''

[AirlineSim]

Can UserName ever have an empty string in it. And remeber that NULL data is not the same as ''

Yes it can.

[GaryMazzone]

So UserName is not NULL but can have an empty string...

If there are empty strings stored there then this (run right in an Access Query) will bring back records right?

SELECT * FROM Users WHERE Username = ''

Does it?

[AirlineSim]

I'm wanting it to return all records if left blank. The username field will always be filled in.

[GaryMazzone]

Is the textfield is Blank the the statement needs to be:

SELECT * FROM Users WHERE Username LIKE '%'

Or

SELECT * FROM USERS

[nbrege]

If the user then types the following into that TextBox:

'; DELETE FROM SomeTable; SELECT * FROM SomeTable WHERE SomeColumn = '

I think the odds of a user knowing or guessing the table name to type in would be pretty low, though I guess it's possible...

[GaryMazzone]

First query:

'; Select Table_Name from Information_Schema.Tables

New query
'; Drop Table One of the names you just got back from the first.

[nbrege]

First query:

'; Select Table_Name from Information_Schema.Tables

New query
'; Drop Table One of the names you just got back from the first.

Are these universal names or database specific?

[si_the_geek]

They are universal... but only for the database systems that support it. Other database systems have their own equivalents, but they are very east to find.

You might be thinking of ways to stop it without Parameters (such as blocking the ' character), but that is far from safe (there are "secret" character combinations that work too).


If you don't want to allow injection attacks, use Parameters.

If you want to make your code simpler (eg: no checking for special characters, no formatting of values within SQL statements), use Parameters.


There is more info about the benefits in the article Why should I use Parameters instead of putting values into my SQL string? from our Database Development FAQs/Tutorials (at the top of the Database Development forum), and via the link in jmcilhinney's signature.

[AirlineSim]

Is the textfield is Blank the the statement needs to be:

SELECT * FROM Users WHERE Username LIKE '%'

Or

SELECT * FROM USERS

but as it's been said it only works for string, what about numbers?

[nbrege]

I'm not disputing the benefits of using parameters ... I'm just wondering how common sql injection attacts are in the real world. I'm guessing that web apps would be more vulnerable than internal apps, unless you had some disgruntled employees...

[GaryMazzone]

There is not wildcard seaching on anything but text fields. That means no number fields, date fields, time fields or DateTime fields

[cicatrix]

"SELECT * FROM Users WHERE Username = '' Or Username = @data"

This SQL will return only records where the username field is empty or where the username field contains the text from the textbox.

I think that I can combine string building and parameters into something safe:

Here's my previous example with modification:
Let's suppose you have 3 fields in your DB (Field1, Field2 and Field3)
And you have 3 textboxes on your form and a Submit button.

Dim SQLcmd As New OleDb.OleDbCommand
Dim sql, condition1, condition2, condition3 As String
 
Private Sub TextBox1_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
     If TextBox1.Text = "" Then 
          condition1 = "(TRUE)" 
     Else
          condition1 = "(Field1=@arg1)"
          SQLcmd.Parameters.AddWithValue("arg1", Textbox1.Text)
     End If
End Sub
 
Private Sub TextBox2_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
     If TextBox1.Text = "" Then 
          condition2 = "(TRUE)" 
     Else
          condition2 = "(Field2=@arg2)"   
          SQLcmd.Parameters.AddWithValue("arg2", Textbox2.Text)
     End If
End Sub
 
Private Sub TextBox3_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
     If TextBox1.Text = "" Then 
          condition3 = "(TRUE)" 
     Else
          condition3 = "(Field3=@arg3)"    
          SQLcmd.Parameters.AddWithValue("arg3", Textbox3.Text)
     End If
End Sub
 
Private Sub btnSumbit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
     SQLcmd.CommandText = "SELECT * FROM MyTable WHERE " _
                & condition1 & " AND " _
                & condition2 & " AND " _
                & condition3 & ";"
  
     ' Submit your sql
     ' SAFE from injections!
     
End Sub

In your case "SELECT * From Users WHERE (TRUE) AND (TRUE) AND (TRUE);" will be an equivalent of "SELECT * FROM Users;"

[si_the_geek]

Rather than setting the parameters etc in the _TextChanged events (which could run multiple times and therefore add multiple parameters for each, or add them in the wrong order), I would recommend doing it all when the SQL statement is about to run (inside btnSumbit_Click).

As an added bonus, all of the code for this can be in one place - which makes it much easier to debug etc.

[cicatrix]

Yes, I didn't consider that. Of course you shouldn't add the parameters in the TextChanged event. Everything can (and should) be done in a single procedure:

Private Sub btnSumbit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
     Dim SQLcmd As New OleDb.OleDbCommand
     Dim condition1, condition2, condition3 As String
 
     If TextBox1.Text = "" Then 
          condition1 = "(TRUE)" 
     Else
          condition1 = "(Field1=@arg1)"
          SQLcmd.Parameters.AddWithValue("arg1", Textbox1.Text)
     End If
 
     If TextBox1.Text = "" Then 
          condition2 = "(TRUE)" 
     Else
          condition2 = "(Field2=@arg2)"   
          SQLcmd.Parameters.AddWithValue("arg2", Textbox2.Text)
     End If
 
     If TextBox1.Text = "" Then 
          condition3 = "(TRUE)" 
     Else
          condition3 = "(Field3=@arg3)"    
          SQLcmd.Parameters.AddWithValue("arg3", Textbox3.Text)
     End If
 
 
     SQLcmd.CommandText = "SELECT * FROM MyTable WHERE " _
                & condition1 & " AND " _
                & condition2 & " AND " _
                & condition3 & ";"
  
     ' Submit your sql
     ' SAFE from injections!
     
End Sub

[jmcilhinney]

While Parameters are certainly a very good idea in any situation (including this one), they don't need to exclude string building in general - arguably the best way to deal with this situation is to build the conditions in the string (with placeholders for the parameters), and then add the actual values with Parameters.

That's a very good point. I said:

Never use string concatenation to build SQL statements

when what I really meant was never use string concatenation to insert literal column values. I've posted some code in the CodeBank that builds up a SQL statement using concatenation but still uses parameters to create an IN clause.