Cleaning User Input...

[joesin]

I recently found a vulnerability on my website that allowed sql
injection. I have been trying to write some code that would clean user
data but have been running into problems. The validation still works,
however so does the injection methods I have used....
These are the examples of code I have tried to use to fix the problem.

1. FrmUserName=rep lace (FrmUserName, " ' ", "")

2. function stripQuotes(Frm UserName)
stripQuotes = replace(FrmUser Name, "'", "''")
end function

3. Function InputFilter(use rInput)
Dim newString, regEx
Set regEx = New RegExp
regEx.Pattern = " ' "
regEx.IgnoreCas e = True
regEx.Global = True
newString = regEx.Replace(u serInput, "")
Set regEx = nothing
InputFilter = newString
End Function

here is the validation I am currently using...
+++++++++++++++ +++++++++++++++ +++++++++++++++ +

DIM oDBConn, oDBRS, oDBCommand
DIM oDBString, oDBSQL
DIM frmUserName,frm Password

Set oDBConn = Server.CreateOb ject("ADODB.Con nection")
Set oDBRS = Server.CreateOb ject("ADODB.Rec ordset")
Set oSYSDB = Server.CreateOb ject("ADODB.REc ordset")
Set oDBCommand = Server.CreateOb ject("ADODB.Com mand")

' On Error Resume Next

oDBString = Application("Da tabase1_Connect ionString")
oDBConn.Open oDBString

If Session("valida ted") = 0 OR IsNull(Session( "validated"))=T rue Then

frmUserName = Request.Form("U serName")
frmPassword = Request.Form("P assword")

oDBSQL = "SELECT * FROM WEBUSERS WHERE UPPER(USERNAME) ='"&
UCase(frmUserNa me) &"' AND PWD='"& frmPassword &"'"
oDBRS.Open oDBSQL,oDBConn, adOpenDynamic

If oDBRS.EOF = False Then
If oDBRS("STATUS") = 1 Then
Session("UserCl ass") = oDBRS("CLASS")
Session("UserID ") = oDBRS("SYSID")
Session("UserNa me") = oDBRS("FIRSTNAM E")&" "& oDBRS("LASTNAME ")
Session("Valida ted") = 1
Session("Market er") = oDBRS("MARKETER ")
If IsNull(oDBRS("T ELEPHONE1"))=Tr ue or
IsNull(oDBRS("M ARKETER"))=True Then

Session("Update Profile") = 1
Else
Session("Update Profile") = 2
End If
Else
AccessDenied
End If

Else
displaybadlogin
End If
End If
+++++++++++++++ +++++++++++++++ +++++++++++++++ ++++++++++++

any suggestions would be greatly appreciated.
Thanks Very Much
Joe Sin

[David Jessee]

If you get this fixed not, it will eventually become an issue again at some
point.
You really need to think about using parameters in your SQL statements
instead of creating a SQL string based on user input. That way, the ADO API
will insulate you from that kind of attack. It also keeps you from having to
do strange stuff like turning date values into strings, etc

Check out:
http://www.itjungle.com/mpo/mpo052203-story02.html

Look at the section titled "How to Execute a Parameterized Statement "

"joesin" wrote:

I recently found a vulnerability on my website that allowed sql
injection. I have been trying to write some code that would clean user
data but have been running into problems. The validation still works,
however so does the injection methods I have used....
These are the examples of code I have tried to use to fix the problem.

1. FrmUserName=rep lace (FrmUserName, " ' ", "")

2. function stripQuotes(Frm UserName)
stripQuotes = replace(FrmUser Name, "'", "''")
end function

3. Function InputFilter(use rInput)
Dim newString, regEx
Set regEx = New RegExp
regEx.Pattern = " ' "
regEx.IgnoreCas e = True
regEx.Global = True
newString = regEx.Replace(u serInput, "")
Set regEx = nothing
InputFilter = newString
End Function

here is the validation I am currently using...
+++++++++++++++ +++++++++++++++ +++++++++++++++ +

DIM oDBConn, oDBRS, oDBCommand
DIM oDBString, oDBSQL
DIM frmUserName,frm Password

Set oDBConn = Server.CreateOb ject("ADODB.Con nection")
Set oDBRS = Server.CreateOb ject("ADODB.Rec ordset")
Set oSYSDB = Server.CreateOb ject("ADODB.REc ordset")
Set oDBCommand = Server.CreateOb ject("ADODB.Com mand")

' On Error Resume Next

oDBString = Application("Da tabase1_Connect ionString")
oDBConn.Open oDBString

If Session("valida ted") = 0 OR IsNull(Session( "validated"))=T rue Then

frmUserName = Request.Form("U serName")
frmPassword = Request.Form("P assword")

oDBSQL = "SELECT * FROM WEBUSERS WHERE UPPER(USERNAME) ='"&
UCase(frmUserNa me) &"' AND PWD='"& frmPassword &"'"
oDBRS.Open oDBSQL,oDBConn, adOpenDynamic

If oDBRS.EOF = False Then
If oDBRS("STATUS") = 1 Then
Session("UserCl ass") = oDBRS("CLASS")
Session("UserID ") = oDBRS("SYSID")
Session("UserNa me") = oDBRS("FIRSTNAM E")&" "& oDBRS("LASTNAME ")
Session("Valida ted") = 1
Session("Market er") = oDBRS("MARKETER ")
If IsNull(oDBRS("T ELEPHONE1"))=Tr ue or
IsNull(oDBRS("M ARKETER"))=True Then

Session("Update Profile") = 1
Else
Session("Update Profile") = 2
End If
Else
AccessDenied
End If

Else
displaybadlogin
End If
End If
+++++++++++++++ +++++++++++++++ +++++++++++++++ ++++++++++++

any suggestions would be greatly appreciated.
Thanks Very Much
Joe Sin

[joesin]

Thanks very much David...
That article will help with the rebuild I plan on doing for the page(i
am still torn between that and stored procedures), however, I need to
plug the hole in the mean time. I still cant get special characters
removed. My attempts (the examples) do not hinder the page pulling the
names from the database, but they do not stop entry by other means.
any ideas?
David Jessee wrote:

If you get this fixed not, it will eventually become an issue again at some
point.
You really need to think about using parameters in your SQL statements
instead of creating a SQL string based on user input. That way, the ADO API
will insulate you from that kind of attack. It also keeps you from having to
do strange stuff like turning date values into strings, etc

Check out:
http://www.itjungle.com/mpo/mpo052203-story02.html

Look at the section titled "How to Execute a Parameterized Statement "

"joesin" wrote:

I recently found a vulnerability on my website that allowed sql
injection. I have been trying to write some code that would clean user
data but have been running into problems. The validation still works,
however so does the injection methods I have used....
These are the examples of code I have tried to use to fix the problem.

1. FrmUserName=rep lace (FrmUserName, " ' ", "")

2. function stripQuotes(Frm UserName)
stripQuotes = replace(FrmUser Name, "'", "''")
end function

3. Function InputFilter(use rInput)
Dim newString, regEx
Set regEx = New RegExp
regEx.Pattern = " ' "
regEx.IgnoreCas e = True
regEx.Global = True
newString = regEx.Replace(u serInput, "")
Set regEx = nothing
InputFilter = newString
End Function

here is the validation I am currently using...
+++++++++++++++ +++++++++++++++ +++++++++++++++ +

DIM oDBConn, oDBRS, oDBCommand
DIM oDBString, oDBSQL
DIM frmUserName,frm Password

Set oDBConn = Server.CreateOb ject("ADODB.Con nection")
Set oDBRS = Server.CreateOb ject("ADODB.Rec ordset")
Set oSYSDB = Server.CreateOb ject("ADODB.REc ordset")
Set oDBCommand = Server.CreateOb ject("ADODB.Com mand")

' On Error Resume Next

oDBString = Application("Da tabase1_Connect ionString")
oDBConn.Open oDBString

If Session("valida ted") = 0 OR IsNull(Session( "validated"))=T rue Then

frmUserName = Request.Form("U serName")
frmPassword = Request.Form("P assword")

oDBSQL = "SELECT * FROM WEBUSERS WHERE UPPER(USERNAME) ='"&
UCase(frmUserNa me) &"' AND PWD='"& frmPassword &"'"
oDBRS.Open oDBSQL,oDBConn, adOpenDynamic

If oDBRS.EOF = False Then
If oDBRS("STATUS") = 1 Then
Session("UserCl ass") = oDBRS("CLASS")
Session("UserID ") = oDBRS("SYSID")
Session("UserNa me") = oDBRS("FIRSTNAM E")&" "& oDBRS("LASTNAME ")
Session("Valida ted") = 1
Session("Market er") = oDBRS("MARKETER ")
If IsNull(oDBRS("T ELEPHONE1"))=Tr ue or
IsNull(oDBRS("M ARKETER"))=True Then

Session("Update Profile") = 1
Else
Session("Update Profile") = 2
End If
Else
AccessDenied
End If

Else
displaybadlogin
End If
End If
+++++++++++++++ +++++++++++++++ +++++++++++++++ ++++++++++++

any suggestions would be greatly appreciated.
Thanks Very Much
Joe Sin

[joesin]

Thanks very much David...
That article will help with the rebuild I plan on doing for the page(i
am still torn between that and stored procedures), however, I need to
plug the hole in the mean time. I still cant get special characters
removed. My attempts (the examples) do not hinder the page pulling the
names from the database, but they do not stop entry by other means.
any ideas?
David Jessee wrote:

If you get this fixed not, it will eventually become an issue again at some
point.
You really need to think about using parameters in your SQL statements
instead of creating a SQL string based on user input. That way, the ADO API
will insulate you from that kind of attack. It also keeps you from having to
do strange stuff like turning date values into strings, etc

Check out:
http://www.itjungle.com/mpo/mpo052203-story02.html

Look at the section titled "How to Execute a Parameterized Statement "

"joesin" wrote:

I recently found a vulnerability on my website that allowed sql
injection. I have been trying to write some code that would clean user
data but have been running into problems. The validation still works,
however so does the injection methods I have used....
These are the examples of code I have tried to use to fix the problem.

1. FrmUserName=rep lace (FrmUserName, " ' ", "")

2. function stripQuotes(Frm UserName)
stripQuotes = replace(FrmUser Name, "'", "''")
end function

3. Function InputFilter(use rInput)
Dim newString, regEx
Set regEx = New RegExp
regEx.Pattern = " ' "
regEx.IgnoreCas e = True
regEx.Global = True
newString = regEx.Replace(u serInput, "")
Set regEx = nothing
InputFilter = newString
End Function

here is the validation I am currently using...
+++++++++++++++ +++++++++++++++ +++++++++++++++ +

DIM oDBConn, oDBRS, oDBCommand
DIM oDBString, oDBSQL
DIM frmUserName,frm Password

Set oDBConn = Server.CreateOb ject("ADODB.Con nection")
Set oDBRS = Server.CreateOb ject("ADODB.Rec ordset")
Set oSYSDB = Server.CreateOb ject("ADODB.REc ordset")
Set oDBCommand = Server.CreateOb ject("ADODB.Com mand")

' On Error Resume Next

oDBString = Application("Da tabase1_Connect ionString")
oDBConn.Open oDBString

If Session("valida ted") = 0 OR IsNull(Session( "validated"))=T rue Then

frmUserName = Request.Form("U serName")
frmPassword = Request.Form("P assword")

oDBSQL = "SELECT * FROM WEBUSERS WHERE UPPER(USERNAME) ='"&
UCase(frmUserNa me) &"' AND PWD='"& frmPassword &"'"
oDBRS.Open oDBSQL,oDBConn, adOpenDynamic

If oDBRS.EOF = False Then
If oDBRS("STATUS") = 1 Then
Session("UserCl ass") = oDBRS("CLASS")
Session("UserID ") = oDBRS("SYSID")
Session("UserNa me") = oDBRS("FIRSTNAM E")&" "& oDBRS("LASTNAME ")
Session("Valida ted") = 1
Session("Market er") = oDBRS("MARKETER ")
If IsNull(oDBRS("T ELEPHONE1"))=Tr ue or
IsNull(oDBRS("M ARKETER"))=True Then

Session("Update Profile") = 1
Else
Session("Update Profile") = 2
End If
Else
AccessDenied
End If

Else
displaybadlogin
End If
End If
+++++++++++++++ +++++++++++++++ +++++++++++++++ ++++++++++++

any suggestions would be greatly appreciated.
Thanks Very Much
Joe Sin

[David Jessee]

Are you still referring to Sql Injection?

If so, then using parameters totally prees you from having to check your
user's input. If you have:

Select * From Users WHERE UserName=? and Password=?

and, the user enters:
UserName: John
Password: Smith';Drop Table Users;

the the parameter's underlying implementation will automatically "clean" the
input, so to speak, and the net result will be zero records because there is
not a record with the Paddword of "Smith';Dro p Table Users;"

Ultimately, the only way to prevent SQL Injection is to deal only with
stored procedures and only give the account that's accessing the database
permissions to work with those procedures. However, using Parameters instead
of SQL strings that you build based on user input will add enough protection
to your code that you won't have to worry about injection at the UI level any
more.
"joesin" wrote:

Thanks very much David...
That article will help with the rebuild I plan on doing for the page(i
am still torn between that and stored procedures), however, I need to
plug the hole in the mean time. I still cant get special characters
removed. My attempts (the examples) do not hinder the page pulling the
names from the database, but they do not stop entry by other means.
any ideas?
David Jessee wrote:

If you get this fixed not, it will eventually become an issue again at some
point.
You really need to think about using parameters in your SQL statements
instead of creating a SQL string based on user input. That way, the ADO API
will insulate you from that kind of attack. It also keeps you from having to
do strange stuff like turning date values into strings, etc

Check out:
http://www.itjungle.com/mpo/mpo052203-story02.html

Look at the section titled "How to Execute a Parameterized Statement "

"joesin" wrote:

I recently found a vulnerability on my website that allowed sql
injection. I have been trying to write some code that would clean user
data but have been running into problems. The validation still works,
however so does the injection methods I have used....
These are the examples of code I have tried to use to fix the problem.
>
1. FrmUserName=rep lace (FrmUserName, " ' ", "")
>
2. function stripQuotes(Frm UserName)
stripQuotes = replace(FrmUser Name, "'", "''")
end function
>
3. Function InputFilter(use rInput)
Dim newString, regEx
Set regEx = New RegExp
regEx.Pattern = " ' "
regEx.IgnoreCas e = True
regEx.Global = True
newString = regEx.Replace(u serInput, "")
Set regEx = nothing
InputFilter = newString
End Function
>
here is the validation I am currently using...
+++++++++++++++ +++++++++++++++ +++++++++++++++ +
>
DIM oDBConn, oDBRS, oDBCommand
DIM oDBString, oDBSQL
DIM frmUserName,frm Password
>
Set oDBConn = Server.CreateOb ject("ADODB.Con nection")
Set oDBRS = Server.CreateOb ject("ADODB.Rec ordset")
Set oSYSDB = Server.CreateOb ject("ADODB.REc ordset")
Set oDBCommand = Server.CreateOb ject("ADODB.Com mand")
>
' On Error Resume Next
>
oDBString = Application("Da tabase1_Connect ionString")
oDBConn.Open oDBString
>
If Session("valida ted") = 0 OR IsNull(Session( "validated"))=T rue Then
>
frmUserName = Request.Form("U serName")
frmPassword = Request.Form("P assword")
>
oDBSQL = "SELECT * FROM WEBUSERS WHERE UPPER(USERNAME) ='"&
UCase(frmUserNa me) &"' AND PWD='"& frmPassword &"'"
>
>
oDBRS.Open oDBSQL,oDBConn, adOpenDynamic
>
If oDBRS.EOF = False Then
If oDBRS("STATUS") = 1 Then
Session("UserCl ass") = oDBRS("CLASS")
Session("UserID ") = oDBRS("SYSID")
Session("UserNa me") = oDBRS("FIRSTNAM E")&" "& oDBRS("LASTNAME ")
Session("Valida ted") = 1
Session("Market er") = oDBRS("MARKETER ")
If IsNull(oDBRS("T ELEPHONE1"))=Tr ue or
IsNull(oDBRS("M ARKETER"))=True Then
>
Session("Update Profile") = 1
Else
Session("Update Profile") = 2
End If
Else
AccessDenied
End If
>
Else
displaybadlogin
End If
End If
>
>
+++++++++++++++ +++++++++++++++ +++++++++++++++ ++++++++++++
>
any suggestions would be greatly appreciated.
Thanks Very Much
Joe Sin
>
>