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
"John Sivilla" <Jo*********@discussions.microsoft.com> wrote in message
news:DE**********************************@microsof t.com...

Appending text directly from web controls and putting them into a sql query, especially without validating them is a huge security hole. You are leaving your site open to SQL injection attacks.

An alternative to what you have, you could build one query using
SQLParameters. Make a parameter for each of your fields that are dynamic;
give them default values of wildcards '%' so that if there are not used the they will return everything. And give the parameters that are used the values of what is entered.

This is much more secure, and it is even easier to develop and program.

hope this helps,
John

"Brian Shannon" wrote:

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

[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.

--
Scott
http://www.OdeToCode.com
On Thu, 5 Aug 2004 09:35:12 -0500, "Brian Shannon"
<bs******@lbrspec.com> wrote:

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
"John Sivilla" <Jo*********@discussions.microsoft.com> wrote in message
news:DE**********************************@microso ft.com...

Appending text directly from web controls and putting them into a sql

query,

especially without validating them is a huge security hole. You are

leaving

your site open to SQL injection attacks.

An alternative to what you have, you could build one query using
SQLParameters. Make a parameter for each of your fields that are dynamic;
give them default values of wildcards '%' so that if there are not used

the

they will return everything. And give the parameters that are used the

values

of what is entered.

This is much more secure, and it is even easier to develop and program.

hope this helps,
John

"Brian Shannon" wrote:

> I have 3 combo boxes and two date text boxes on a .aspx page. The usercan > fill in any of the 5 controls or none to filter a datagrid. I washoping > 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 ifsomething > is filled in. If there is I begin building the where clause. Below iswhat > 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 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" <bitmask@[nospam].fred.net> wrote in message
news:4d********************************@4ax.com...

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.

--
Scott
http://www.OdeToCode.com
On Thu, 5 Aug 2004 09:35:12 -0500, "Brian Shannon"
<bs******@lbrspec.com> wrote:

Thanks for the input on SQL injection attacks. Although I believe my
strategy will prevent that for the drop downs are restricted to the valuesbinded 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 themore 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'sdoing a search all?

Thanks
"John Sivilla" <Jo*********@discussions.microsoft.com> wrote in message
news:DE**********************************@microso ft.com...

Appending text directly from web controls and putting them into a sql

query,

especially without validating them is a huge security hole. You are

leaving

your site open to SQL injection attacks.

An alternative to what you have, you could build one query using
SQLParameters. Make a parameter for each of your fields that are dynamic; give them default values of wildcards '%' so that if there are not used

the

they will return everything. And give the parameters that are used the

values

of what is entered.

This is much more secure, and it is even easier to develop and program.

hope this helps,
John

"Brian Shannon" wrote:

> 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 iswhat

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

[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/is...n/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/de...SecNetch12.asp

--s

On Thu, 5 Aug 2004 11:53:54 -0500, "Brian Shannon"
<bs******@lbrspec.com> wrote:

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?

--
Scott
http://www.OdeToCode.com