1

A database that a client of mine has was hacked. I am in the process of trying to rebuild the data. The site is running classic ASP with a SQL Server database. I believe I have found where the weak point was for the hackers and removed that entry point for now.

Every text colummn in the database was appended with some html markup and inline script/js tags.

Here is an example of a field:

all</title><script>
    document.write("<style>.aq21{position:absolute;clip:rect(436px,auto,auto,436px);}</style>");
</script>
<div class=aq21>
    <a href=http://samedaypaydayloansonlineelqmt.com >same day payday loans online</a>
    <a href=http://samedaypaydayloan

This example was in the Users table in the UserRights column. The initial value was all, but then you can see the links that were appended.

I need to write a regex script that will search through all fields in each column of each table in the database and remove this extra markup.

Essentially, if I try to match </table>, then that string and everything that appends it can be replaced with a blank string.

All of these appended strings are the same for each field in the same column. However, there are multiple columns in each table.

This is what I have been doing so far, replacing the hacked part, but a nice regex would probably help me out, though my regex skills.... well suck.

UPDATE [databasename.[db].[databasetable]
set 
UserRights = replace(UserRights,'</title><script>document.write("<style>.aq21{position:absolute;clip:rect(436px,auto,auto,436px);}</style>");</script><div class=aq21><a href=http://samedaypaydayloansonlineelqmt.com >same day payday loans online</a><a href=http://samedaypaydayloan','');

Any regex help and/or tips are appreciated.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
symfo
  • 31
  • 2
  • 3
    Are you sure, you are not trying to hack by using regexp? ;-) first impression of regexp is to extract things from unknown/unpredictable....so anyway you want to remove some bad stuff here though... – bonCodigo Nov 30 '12 at 03:26
  • lol, no i'm not trying to hack it. This has been a pain in the arse for the 2nd time. first i fixed it using the above SQL, but this time, I noticed that all the spam that was added is random. – symfo Dec 04 '12 at 03:56
  • If you had a **back up** :) any plans for a back up this time around after the invador attack? :D – bonCodigo Dec 04 '12 at 04:37
  • 1
    I have backups, the problem is the data in the DB is always being updated. So having a backup before the hack would be great, however if I restored it, then I would be potentially loosing data like new users/customers new products, new orders, etc.... which would be bad. anyways, I did manage to fix the db. It was painful and slow. I'll post the answer below this comment. – symfo Dec 20 '12 at 14:36

2 Answers2

2

Since the bad stuff starts with a <, and that is an unusual character to typically find, I would use normal text functions, something like this:

update mytable set
mycol = substr(mycol, 1, charindex('<', mycol) - 1)
where mycol like '%<%';

And methodically do this with every column of every table.

Note that I'm only guessing at the right function to use, since I'm unfamiliar with SQL Server, but you get idea.

I welcome someone editing the SQL to improve it.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • I'll give this a try. Let you know how it goes. – symfo Dec 04 '12 at 03:57
  • @user1865092 I just noticed a mistake in the query and fixed it. Better check the where clause is `like '%<%'` – Bohemian Dec 04 '12 at 04:02
  • In my case the above query did not "compile" and left the `<` in-place. This modified solution worked for me: `update mytable set mycol = substring(mycol, 1, charindex('<', mycol)-1) where mycol like '%<%';`. – Uwe Keim Nov 04 '15 at 19:00
2

This is what I ended up doing (big thanks to @Bohemian):

I went through each table and checked which column was affected. Then I ran the following script on each column:

UPDATE [tablename]  
set columnname = substring(columnname, 1, charindex('/', columnname)-1)
where columnname like '%</%';

If the column had any markup in it, then I ended up manually updating those records manually. (lucky for me there was only a couple of records).

If anyone has any better solutions, please feel free to comment.

Thanks!

symfo
  • 31
  • 2