By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,490 Members | 1,398 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,490 IT Pros & Developers. It's quick & easy.

Cleaning User Input...

P: n/a
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=replace (FrmUserName, " ' ", "")

2. function stripQuotes(FrmUserName)
stripQuotes = replace(FrmUserName, "'", "''")
end function

3. Function InputFilter(userInput)
Dim newString, regEx
Set regEx = New RegExp
regEx.Pattern = " ' "
regEx.IgnoreCase = True
regEx.Global = True
newString = regEx.Replace(userInput, "")
Set regEx = nothing
InputFilter = newString
End Function

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

DIM oDBConn, oDBRS, oDBCommand
DIM oDBString, oDBSQL
DIM frmUserName,frmPassword

Set oDBConn = Server.CreateObject("ADODB.Connection")
Set oDBRS = Server.CreateObject("ADODB.Recordset")
Set oSYSDB = Server.CreateObject("ADODB.REcordset")
Set oDBCommand = Server.CreateObject("ADODB.Command")

' On Error Resume Next

oDBString = Application("Database1_ConnectionString")
oDBConn.Open oDBString

If Session("validated") = 0 OR IsNull(Session("validated"))=True Then

frmUserName = Request.Form("UserName")
frmPassword = Request.Form("Password")

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

If oDBRS.EOF = False Then
If oDBRS("STATUS") = 1 Then
Session("UserClass") = oDBRS("CLASS")
Session("UserID") = oDBRS("SYSID")
Session("UserName") = oDBRS("FIRSTNAME")&" "& oDBRS("LASTNAME")
Session("Validated") = 1
Session("Marketer") = oDBRS("MARKETER")
If IsNull(oDBRS("TELEPHONE1"))=True or
IsNull(oDBRS("MARKETER"))=True Then

Session("UpdateProfile") = 1
Else
Session("UpdateProfile") = 2
End If
Else
AccessDenied
End If

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

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

Aug 17 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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=replace (FrmUserName, " ' ", "")

2. function stripQuotes(FrmUserName)
stripQuotes = replace(FrmUserName, "'", "''")
end function

3. Function InputFilter(userInput)
Dim newString, regEx
Set regEx = New RegExp
regEx.Pattern = " ' "
regEx.IgnoreCase = True
regEx.Global = True
newString = regEx.Replace(userInput, "")
Set regEx = nothing
InputFilter = newString
End Function

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

DIM oDBConn, oDBRS, oDBCommand
DIM oDBString, oDBSQL
DIM frmUserName,frmPassword

Set oDBConn = Server.CreateObject("ADODB.Connection")
Set oDBRS = Server.CreateObject("ADODB.Recordset")
Set oSYSDB = Server.CreateObject("ADODB.REcordset")
Set oDBCommand = Server.CreateObject("ADODB.Command")

' On Error Resume Next

oDBString = Application("Database1_ConnectionString")
oDBConn.Open oDBString

If Session("validated") = 0 OR IsNull(Session("validated"))=True Then

frmUserName = Request.Form("UserName")
frmPassword = Request.Form("Password")

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

If oDBRS.EOF = False Then
If oDBRS("STATUS") = 1 Then
Session("UserClass") = oDBRS("CLASS")
Session("UserID") = oDBRS("SYSID")
Session("UserName") = oDBRS("FIRSTNAME")&" "& oDBRS("LASTNAME")
Session("Validated") = 1
Session("Marketer") = oDBRS("MARKETER")
If IsNull(oDBRS("TELEPHONE1"))=True or
IsNull(oDBRS("MARKETER"))=True Then

Session("UpdateProfile") = 1
Else
Session("UpdateProfile") = 2
End If
Else
AccessDenied
End If

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

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

Aug 17 '06 #2

P: n/a
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=replace (FrmUserName, " ' ", "")

2. function stripQuotes(FrmUserName)
stripQuotes = replace(FrmUserName, "'", "''")
end function

3. Function InputFilter(userInput)
Dim newString, regEx
Set regEx = New RegExp
regEx.Pattern = " ' "
regEx.IgnoreCase = True
regEx.Global = True
newString = regEx.Replace(userInput, "")
Set regEx = nothing
InputFilter = newString
End Function

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

DIM oDBConn, oDBRS, oDBCommand
DIM oDBString, oDBSQL
DIM frmUserName,frmPassword

Set oDBConn = Server.CreateObject("ADODB.Connection")
Set oDBRS = Server.CreateObject("ADODB.Recordset")
Set oSYSDB = Server.CreateObject("ADODB.REcordset")
Set oDBCommand = Server.CreateObject("ADODB.Command")

' On Error Resume Next

oDBString = Application("Database1_ConnectionString")
oDBConn.Open oDBString

If Session("validated") = 0 OR IsNull(Session("validated"))=True Then

frmUserName = Request.Form("UserName")
frmPassword = Request.Form("Password")

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

If oDBRS.EOF = False Then
If oDBRS("STATUS") = 1 Then
Session("UserClass") = oDBRS("CLASS")
Session("UserID") = oDBRS("SYSID")
Session("UserName") = oDBRS("FIRSTNAME")&" "& oDBRS("LASTNAME")
Session("Validated") = 1
Session("Marketer") = oDBRS("MARKETER")
If IsNull(oDBRS("TELEPHONE1"))=True or
IsNull(oDBRS("MARKETER"))=True Then

Session("UpdateProfile") = 1
Else
Session("UpdateProfile") = 2
End If
Else
AccessDenied
End If

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

any suggestions would be greatly appreciated.
Thanks Very Much
Joe Sin
Aug 17 '06 #3

P: n/a
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=replace (FrmUserName, " ' ", "")

2. function stripQuotes(FrmUserName)
stripQuotes = replace(FrmUserName, "'", "''")
end function

3. Function InputFilter(userInput)
Dim newString, regEx
Set regEx = New RegExp
regEx.Pattern = " ' "
regEx.IgnoreCase = True
regEx.Global = True
newString = regEx.Replace(userInput, "")
Set regEx = nothing
InputFilter = newString
End Function

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

DIM oDBConn, oDBRS, oDBCommand
DIM oDBString, oDBSQL
DIM frmUserName,frmPassword

Set oDBConn = Server.CreateObject("ADODB.Connection")
Set oDBRS = Server.CreateObject("ADODB.Recordset")
Set oSYSDB = Server.CreateObject("ADODB.REcordset")
Set oDBCommand = Server.CreateObject("ADODB.Command")

' On Error Resume Next

oDBString = Application("Database1_ConnectionString")
oDBConn.Open oDBString

If Session("validated") = 0 OR IsNull(Session("validated"))=True Then

frmUserName = Request.Form("UserName")
frmPassword = Request.Form("Password")

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

If oDBRS.EOF = False Then
If oDBRS("STATUS") = 1 Then
Session("UserClass") = oDBRS("CLASS")
Session("UserID") = oDBRS("SYSID")
Session("UserName") = oDBRS("FIRSTNAME")&" "& oDBRS("LASTNAME")
Session("Validated") = 1
Session("Marketer") = oDBRS("MARKETER")
If IsNull(oDBRS("TELEPHONE1"))=True or
IsNull(oDBRS("MARKETER"))=True Then

Session("UpdateProfile") = 1
Else
Session("UpdateProfile") = 2
End If
Else
AccessDenied
End If

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

any suggestions would be greatly appreciated.
Thanks Very Much
Joe Sin
Aug 17 '06 #4

P: n/a
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';Drop 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=replace (FrmUserName, " ' ", "")
>
2. function stripQuotes(FrmUserName)
stripQuotes = replace(FrmUserName, "'", "''")
end function
>
3. Function InputFilter(userInput)
Dim newString, regEx
Set regEx = New RegExp
regEx.Pattern = " ' "
regEx.IgnoreCase = True
regEx.Global = True
newString = regEx.Replace(userInput, "")
Set regEx = nothing
InputFilter = newString
End Function
>
here is the validation I am currently using...
++++++++++++++++++++++++++++++++++++++++++++++
>
DIM oDBConn, oDBRS, oDBCommand
DIM oDBString, oDBSQL
DIM frmUserName,frmPassword
>
Set oDBConn = Server.CreateObject("ADODB.Connection")
Set oDBRS = Server.CreateObject("ADODB.Recordset")
Set oSYSDB = Server.CreateObject("ADODB.REcordset")
Set oDBCommand = Server.CreateObject("ADODB.Command")
>
' On Error Resume Next
>
oDBString = Application("Database1_ConnectionString")
oDBConn.Open oDBString
>
If Session("validated") = 0 OR IsNull(Session("validated"))=True Then
>
frmUserName = Request.Form("UserName")
frmPassword = Request.Form("Password")
>
oDBSQL = "SELECT * FROM WEBUSERS WHERE UPPER(USERNAME)='"&
UCase(frmUserName) &"' AND PWD='"& frmPassword &"'"
>
>
oDBRS.Open oDBSQL,oDBConn,adOpenDynamic
>
If oDBRS.EOF = False Then
If oDBRS("STATUS") = 1 Then
Session("UserClass") = oDBRS("CLASS")
Session("UserID") = oDBRS("SYSID")
Session("UserName") = oDBRS("FIRSTNAME")&" "& oDBRS("LASTNAME")
Session("Validated") = 1
Session("Marketer") = oDBRS("MARKETER")
If IsNull(oDBRS("TELEPHONE1"))=True or
IsNull(oDBRS("MARKETER"))=True Then
>
Session("UpdateProfile") = 1
Else
Session("UpdateProfile") = 2
End If
Else
AccessDenied
End If
>
Else
displaybadlogin
End If
End If
>
>
++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++
>
any suggestions would be greatly appreciated.
Thanks Very Much
Joe Sin
>
>

Aug 17 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.