Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
parrot
Posting Yak Master
132 Posts |
 Posted - 2011-12-12 : 12:19:26
|
| I trapped an sql injection hacker on my log file and prevented the malicious code from being entered into my database. The hacker's ip address is 141.136.17.150 which is somewhere in Romania. What is the recommended solution for identifying a hacker? Should I just transfer control back to my homepage, display an error message, tell him to go to hell, etc. Also, is there a forum where I can expose the hacker's ip address so other developers can prevent his intrusion. |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-12-13 : 06:23:11
|
| We get loads of SQL Injection attack attempts in our web logs. We ignore them.We used to tell the ISP, and found some website (where we could report them) which provided a general resource for malware authors to be on the look out etc. but we never felt reporting them actually achieved anything so we gave up.Almost certainly they are BOTs so giving them a 404 page or NOT AUTHORISED or somesuch will achieve nothing.You might give then a SLEEP 30 seconds (something that won't cause them to timeout though) so that they don't swamp your site.Stick a denied folder + file in your ROBOTS.TXT and anything that requests THAT file (FROM that folder) is suspect (as the only place that the reference to it will be is in your ROBOTS.TXT) - and anything referencing that may be more troublesome than just SQL Injection attacks!!I don't think that banning IP addresses works - they may be legitimate ISPs from which you would get real, worthwhile, traffic - just a few kiddy-scripters happen to be using that ISP.I assume your application has ZERO vulnerabilities to SQL Injection ... if not fix them before you attempt to ban anyone! otherwise the attacks will have succeeded before you have worked out the best way to ban them! |
 |
|
|
parrot
Posting Yak Master
132 Posts |
Posted - 2011-12-13 : 16:03:12
|
| Thanks for your reply, Kristen. I was able to track the injection IP as being from Romania so I added the IP address to my server's denial list as I assume somebody from Romania would not be interested in a local community website. I had all my sql commands for input parameterized but I realized in the problem program that I was passing command code in my ip address field of the browser. So someone was adding malicious code after my ?key= parameter which is not a problem if I am using parameterized updates. However, I was using the key data to do a read command such as 'SELECT * FROM TABLE X WHERE KEYFIELD = ' + key. Then I executed the command using daName = new System.Data.OleDb.OleDbDataAdapter(strSQL, urlconn); dataSet1 = new DataSet(); int ret = daName.Fill(dataSet1, "Stats");So now I check all command input for proper length and check for special characters such as ";' or '--' or 'varchar', etc. in everyone one of my more than 50 programs. If any of these rules are violated, I trap the input command, the ip address, date and time and write it to a logerror file and send myself an email to notify me of a potential hacker. So far I have had only one hacker. He also tried to hack on Nov. 11 and then again on Dec. 11 which makes me think he had a schedule for mass updating his malicious code to various web sites. Anyway, he tried 300 times on Dec. 11 to hack into my website.The other thing I would note is that the hacker attempted to add script to any table field having the title of 'Description' or 'Text' or 'Textdata'. So the next time you design a table, I would not use those field names because the hacker can then get lucky if you're not careful as I wasn't. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-12-13 : 17:07:23
|
"'SELECT * FROM TABLE X WHERE KEYFIELD = ' + key"Where we need code like that we do something like:'SELECT * FROM TABLE X WHERE KEYFIELD = ' + MyFunction(key) (this is in application language, rather than SQL, but could be a "dbo.MyFunction(key)" in SQL)"MyFunction" replaces ' with double-single-quotes '', and wraps the value with ' tooSo the stringxx'yy would become'xx''yy' which will be safe to use in your'SELECT * FROM TABLE X WHERE KEYFIELD = ' + 'xx''yy' statement.No need to then have special tests for invalid characters in IP address etc. (although it obviously makes sense to have data-validation tests because a) it helps the user when they make a simple typing mistake and b) it stops getting runtime error from SQL when you give it a Goofy Date / number / IP address / etc.)For "somewhat adhoc SQL" we use sp_ExecuteSQL to parametrise the query; if you are not familiar with it you might like to look it up the DOCs. |
|
|
|
parrot
Posting Yak Master
132 Posts |
Posted - 2011-12-13 : 20:08:47
|
| Thanks for the feedback. What does the double quote do? I program with C#. Does that work in that type of language? In regards to error feedback. If I find bad characters in a textbox on a web page, I report back an error. If I find that the parameters on my command line contains bad characters, I assume someone is playing around and I just redirect the viewer back to my homepage and let them scratch their head for awhile. I have used sp_ExecuteSQL in my programming as well. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-12-14 : 08:54:22
|
quote:
Originally posted by parrot
What does the double quote do?
It ESCAPEs it. You can't do this:SELECT * FROM TABLE X WHERE KEYFIELD = 'xx'yy' because it will cause a syntax error, instead you have to do this:SELECT * FROM TABLE X WHERE KEYFIELD = 'xx''yy' hence you have to double up any embedded quotes. Failure to do this is the primary cause of SQL Injection vulnerabilities; the other major cause would be allowing non-numeric characters in numeric values, such that a user could enter "123;xxx" and thereby inject some SQL:SELECT * FROM TABLE X WHERE MyNumber = 123;DROP DATABASE MyDatabase !!As a quick-and-dirty solution you can put single quotes around numeric constants, thus the above would become:SELECT * FROM TABLE X WHERE MyNumber = '123;DROP DATABASE MyDatabase' which would just result in a runtime error, rather than the database being dropped!Parametrised queries would be better though, because the data typing is assured, and no possibility of SQL Injection (unless you manipulate the values and use dynamic-SQL within your actual SQL/Procedures."If I find bad characters in a textbox on a web page, I report back an error. If I find that the parameters on my command line contains bad characters, I assume someone is playing around and I just redirect the viewer back to my homepage and let them scratch their head for awhile."Well, if a user does that you'll probably just annoy them. Its probably only BOTs that are hacking your system, so they will not see those actions anyway; they will try all of the hack-attack items in their array, and then go away ...So on the grounds it might be a user you might as well tell them what they did wrong and let them try again properly.Goofy stuff in the Query String is either a hacker, I agree, or perhaps an error in your program. Goofy stuff in a field is either a hacker, or a user making a data entry mistake (e.g. Cur & Paste the wrong stuff by mistake ...) |
|
|
|
parrot
Posting Yak Master
132 Posts |
Posted - 2011-12-15 : 14:28:53
|
| Thanks for your valuable suggestions. I will implement these in my programs. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
How to expose SQL Injection hacker