Building a Dynamic Where Clause

[Brian Shannon]

I have 3 combo boxes and two date text boxes on a .aspx page. The user can
fill in any of the 5 controls or none to filter a datagrid. I was hoping
someone could explain how to efficiently build the where clause of a sql
string to send to SQL 2000 for a data set.

Currenly I check each control with an IF statement to determine if something
is filled in. If there is I begin building the where clause. Below is what
I have done (and it works just fine) but am curious if there is a better
way.

Thanks

Controls are:
txtOrdNum
ctlThru.DateField 'User Control with a calandar control. References a
text box
ctlFrom.DateField 'User Control with a calandar control. References a
text box
cboTask
cboEmployee
'Combo boxes display a list item with a blank text and -1 as the value
Currenly if the control is null then I skip it

Private Function WhereFunction() As String
Dim OrdNum As String
Dim Employee As String
Dim DateFrom As String
Dim DateThru As String
Dim OrderNumber As String
Dim x As StringBuilder
Dim y As Int16
Dim lastIndexof As Int16

DateThru = Me.ctlThru.DateField
DateFrom = Me.ctlFrom.DateField

If Not Me.txtOrdNum.Text = String.Empty Then
WhereFunction = WhereFunction & "j_recref = " &
Me.txtOrdNum.Text & " AND "
End If

If Me.cboEmployee.SelectedItem.Value <> -1 Then
WhereFunction = WhereFunction & "j_UserID = " &
cboEmployee.SelectedItem.Value & " AND "
End If

If Me.cboTask.SelectedItem.Value <> -1 Then
WhereFunction = WhereFunction & "j_taskid = " &
cboTask.SelectedItem.Value & " AND "
End If

If DateFrom = String.Empty Then
'Nothing
ElseIf DateThru = String.Empty Then
WhereFunction = WhereFunction & "j_date = " & gloQuote(DateFrom)
& " AND "
Else
WhereFunction = WhereFunction & "j_date BETWEEN " &
gloQuote(DateFrom) _
& " AND " & gloQuote(DateThru) & " AND "
End If

If WhereFunction <> String.Empty Then
WhereFunction = " WHERE j_timein <> '' AND j_timeout <> '' AND "
& WhereFunction
x = New StringBuilder(WhereFunction)
y = x.Length
lastIndexof = x.ToString.LastIndexOf(" ")
If y - 1 = lastIndexof Then
WhereFunction = WhereFunction.Remove(y - 5, 5)
End If
End If
Return WhereFunction
End Function

 

[Brian Shannon]

Thanks for the input on SQL injection attacks. Although I believe my
strategy will prevent that for the drop downs are restricted to the values
binded to the control and the two date text boxes require a valid date. So,
I hope I haven't missed anything to allow such unwanted attacks.

Although you are correct that I should use SQLParameters being that is the
more efficient way of doing Data Access events. I think I will change my
strategy to your suggested method.

By placing a '%' sign for null values will that hit performance since it's
doing a search all?

Thanks

 

[Scott Allen]

Hi Brian:

Just to follow up on your post:

I could write a program or a script to POST a value to the server
which is not in the drop down list. You really can't trust anything
that comes in over the network - there are ways to circumvent the UI.

Wildcard matches will generally take longer than finding an exact
match, but you'll have to measure with your application to see if the
performance hit is noticeable or acceptable.

 

[Brian Shannon]

Thanks for the followup. Would wildcard matches take care of the sql
injection?

How does someone script something to be posted to the server that is not
entered in the dropdown box? My sql statements are based off the values
from the control?

Just curious? I guess I am not all that familiar with sql injection
although I have heard a lot about it. Does using sql paramaters take care
of it?

Thanks

 

[Scott Allen]

Hi Brian:

1) Using parameters will certainly help prevent sql injection
problems.

There is an example of writing a program to programattically fill out
a web form here:
http://msdn.microsoft.com/msdnmag/issues/04/08/TestRun/default.aspx


2) When the browser sends the value of the drop down the user selected
to the server, it just sends along a simple text string over the
network, like:
dropDownListID=SomeValue

I could write a program, which just like the browser opens an HTTP
connection to the server and sends a value for the selected drop down
item, but my program will send whatever I tell it to send, not
nessecarily a value from the drop down list.

dropDownListID=SomeSQLCommand


What I would do with SomeSQLCommand is enter escape characters to stop
your SQL command and then execute my own. There is an example in the
SQL Injection section here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/SecNetch12.asp

--s