A better way to clean up after an attack

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.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

A better way to clean up after an attack

Author

Topic

Savaries
Starting Member

8 Posts

Posted - 2008-08-06 : 15:00:39

My site recently got hit with a sql injection, i have fixed the page but cant get it to let me fix all of the database fields that were messed up. Most of them i was able to write a code to fix but one database has a huge amount of ntext and the code i came up with wont work on it. all the data was added to the end of the fields.

Here is the code im using.

<code>
UPDATE genericTable
SET column1 = LEFT(column1, CHARINDEX('<script', column1) - 1)
WHERE column1 LIKE '%<script%'; </code>

Could anyone help me figure out an automated way to do this doing it by hand is taking me hrs.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-06 : 15:10:11

I think you'll need to use UPDATETEXT since the data type is ntext.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Savaries
Starting Member

8 Posts

Posted - 2008-08-06 : 15:28:29

sorry im not seaming to understand you.

are you saying i need to use updatetext genericTable?

if now could you show me a brief example that would really help me.
I have been searching the web and found this.

DECLARE @t VARCHAR(255);

SET @t = '<script ...></script>';

UPDATE table_name
SET column_name = REPLACE(column_name, @t, '')
WHERE column_name LIKE '%' + @t + '%';

Would that work and if so where do i put that in. Im just learning how to work with a server.

Savaries
Starting Member

8 Posts

Posted - 2008-08-06 : 16:30:14

im being a dork apparently i looked up updatetext and have switched my code to

UPDATEtext genericTable
SET column1 = LEFT(column1, CHARINDEX('<script', column1) - 1)
WHERE column1 LIKE '%<script%'

It tells me UPDATETEXT is not supported when i try it in
microsoft sql server management studio. Also the @symbol will not work on mssms ether.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-06 : 16:34:40

UPDATETEXT works a bit different than what you have posted. I don't have too much experience with it though, otherwise I'd show you what to do.

Do you have SQL Server Books Online installed? If so, check out the example there for UPDATETEXT. You might also be interested in WRITETEXT.

Since you are using SQL Server 2005, could you just switch to nvarchar(max) instead of ntext?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Savaries
Starting Member

8 Posts

Posted - 2008-08-06 : 17:08:56

would that truncate the field? and i dont know what version i have i got it from the teacher. Ill have to have a look.

-edit-

nvarcher(max) dosnt work on my server. it says invalid type.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-06 : 17:11:45

Run SELECT @@VERSION to see what version you have.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Savaries
Starting Member

8 Posts

Posted - 2008-08-06 : 17:13:46

database property - options- computability says its sql 2000

it returned 2000.
would it be worth it to upgrade?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-08-06 : 22:33:24

Did you restore the db from sql2k's backup? If so, the db is upgraded and you can set compatibility level to 90.

Savaries
Starting Member

8 Posts

Posted - 2008-08-06 : 22:59:56

its the database the that the teacher gave out with the trial software.
Unfortunately classes don't start for about three weeks.

Im confused as to why the software is 2005 but it cant make a 2005 database. I just tried to make a new one and just to transefer the data but i am unable to get it to make a 2005 database. It will only do 2000 and sql server 7.0. Could i have possible installed it wrong?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-08-06 : 23:51:05

What do you mean 'It will only do 2000 and sql server 7.0'? You can change compatibility level if it's in sql2k5 instance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-07 : 03:47:24

If SELECT @@VERSION says SQL Server 2000, you are runnning a MICROSOFT SQL Server 2000 instance, no matter what compatibility level is set.

E 12°55'05.25"
N 56°04'39.16"

Savaries
Starting Member

8 Posts

Posted - 2008-08-07 : 12:16:27