Invisible/unprintable characters after SQL Inject

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 2008 Forums

Other SQL Server 2008 Topics

Invisible/unprintable characters after SQL Inject

Author

Topic

hog7
Starting Member

5 Posts

Posted - 2010-04-09 : 09:10:06

Cleaning up after a SQL injection attack I have found some of my data columns include hidden or missing characters.

When I do

SELECT len(COL) from TABLE WHERE id='x'

I get

110

but when I do

SELECT COL from TABLE WHERE id='x'

I get

'' (blank)

I have tried the usual replace chr(13), chr(10)... and even ASCII(COL) returns '0'

I realise I could just update the column with new data, but I can't help thinking my old data is still there, just re-encoded and/or hidden.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-04-09 : 09:16:08

What is the collation of the column?

Harsh Athalye
http://www.letsgeek.net/

hog7
Starting Member

5 Posts

Posted - 2010-04-09 : 09:29:54

SQL_Latin1_General_CP1_CI_AS

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-04-09 : 09:37:38

My guess is that your column data is replace with series of nul (ascii 0) values, so there is nothing much you can do to recover the old data.

Harsh Athalye
http://www.letsgeek.net/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-09 : 09:45:44

[code]declare @t varchar(10)
set @t='

'
select @t,LEN(@t)
[/code]

Madhivanan

Failing to plan is Planning to fail

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-09 : 09:50:10

If it were me, it'd restore the latest backup and use that to update the current data, depending on the circumstances of course.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

hog7
Starting Member

5 Posts

Posted - 2010-04-09 : 09:53:21

quote:
Originally posted by DBA in the making

If it were me, it'd restore the latest backup and use that to update the current data, depending on the circumstances of course.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Could do, but I was hoping to do a selective restore of only the data in the effected columns...

hog7
Starting Member

5 Posts

Posted - 2010-04-09 : 09:58:48

Thanks @madhivanan and @harsh_athalye - I think you're correct.... subsequent tests show all effected records and columns have len(COL)=110 so it must just be junk...

Question is how do I SELECT * FROM TABLE WHERE COL='

'

?

hog7
Starting Member

5 Posts

Posted - 2010-04-09 : 10:11:47

So, I did

SELECT CONVERT(BINARY,COL) FROM TABLE

which gave me

0x000000000000000000000000000000000000000000000000000000000000

and then I can do

SELECT * FROM TABLE WHERE CONVERT(BINARY,COL)=0x000000000000000000000000000000000000000000000000000000000000

to return all the corrupt rows...

I'll write a function to replace the corrupt cols with data from back up without disturbing any good data. Nice.

Thanks for help, guys.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-09 : 10:12:53

quote:
Originally posted by hog7

Could do, but I was hoping to do a selective restore of only the data in the effected columns...
[/quote]

That's what I meant. If you restore a backup to a different db name (Which I thought I'd said the first time, but didn't), then you can copy the data into the current db selectively. Sorry, should have made it clearer.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources