I have been hacked; how to strip values

Question

Post reply

I have been hacked; how to strip values

middletree

Hall of Fame

Points: 3316
More actions
July 22, 2009 at 9:31 am

#132114

Someone managed to get some SQL injection going, and our database has one field which has this:
Sample value before attack:
101 main street
Same field now:
101 main street
I need to know how to remove the from the thousands of affected rows without losing the good data.
Is a REPLACE function the best way to go?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

middletree Hall of Fame Points: 3316 More actions · Answer 1

I tried this and it didn't work:

update SHIP_BOL_HDR

SET 3rdPartyAddress = REPLACE ( 3rdPartyAddress ,'' ,'' )

The error just said nothing helpful.

_UB SSCrazy Points: 2987 More actions · Answer 2

I do not understand the question clearly....

Are there any non-displayable (or special) characters added to the address column

_Ub

middletree (7/22/2009)
Someone managed to get some SQL injection going, and our database has one field which has this:
Sample value before attack:
101 main street
Same field now:
101 main street
I need to know how to remove the from the thousands of affected rows without losing the good data.
Is a REPLACE function the best way to go?

SqlTimes Blog on Sql Server

middletree Hall of Fame Points: 3316 More actions · Answer 3

When I posted the message, it included the bad stuff. But this forum stripped it out, which is understandable. It was an HTML script tag, followed by a link to a javascript file, then a closing script tag. It was appended onto the existing value.

So why does my replace function not work?

steveb. SSC-Forever Points: 46733 More actions · Answer 4

middletree (7/22/2009)
When I posted the message, it included the bad stuff. But this forum stripped it out, which is understandable. It was an HTML script tag, followed by a link to a javascript file, then a closing script tag. It was appended onto the existing value.
So why does my replace function not work?

Your replace function does not work because you are searching for nothing ('') and replacing it with nothing ('')

you need to state what string you want to find and replace

SET 3rdPartyAddress = REPLACE ( 3rdPartyAddress ,'String to Replace' ,'' )

John Rowan SSC Guru Points: 56440 More actions · Answer 5

Depending on how big your table is, you may want to consider re-building it by moving the 'good' data over into a new table instead of stripping out the bad data. Once your new table has all of the good data, drop your original and rename the new one. If your table has a bunch of constraints, this may add a bunch of work for you so you may consider building a new table w/ good data, truncating the original table, and copying the new table's data back to the original.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Lowell SSC Guru Points: 323518 More actions · Answer 6

Sample value before attack:

101 main street

Same field now:

101 main street

I need to know how to remove the from the thousands of affected rows without losing the good data.

i think i would use a LEFT function to search for the first left bracket:

i had to replace the less than with a curly to fix errors in the forum:

SELECT ADDRESS,LEFT(ADDRESS,CHARINDEX(' {a href=',ADDRESS) -1)

FROM MYTABLE

WHERE CHARINDEX(' {a href=',ADDRESS) > 0

--similar update if the select produces results

UPDATE FROM MYTABLE

SET ADDRESS = LEFT(ADDRESS,CHARINDEX(' {a href=',ADDRESS) -1

WHERE CHARINDEX(' {a href=',ADDRESS) > 0

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

middletree Hall of Fame Points: 3316 More actions · Answer 7

steveb (7/22/2009)
middletree (7/22/2009)
When I posted the message, it included the bad stuff. But this forum stripped it out, which is understandable. It was an HTML script tag, followed by a link to a javascript file, then a closing script tag. It was appended onto the existing value.
So why does my replace function not work?
Your replace function does not work because you are searching for nothing ('') and replacing it with nothing ('')
you need to state what string you want to find and replace
SET 3rdPartyAddress = REPLACE ( 3rdPartyAddress ,'String to Replace' ,'' )

Not really. This forum stripped out the value that I was trying to replace.

steveb. SSC-Forever Points: 46733 More actions · Answer 8

middletree (7/22/2009)
steveb (7/22/2009)
middletree (7/22/2009)
When I posted the message, it included the bad stuff. But this forum stripped it out, which is understandable. It was an HTML script tag, followed by a link to a javascript file, then a closing script tag. It was appended onto the existing value.
So why does my replace function not work?
Your replace function does not work because you are searching for nothing ('') and replacing it with nothing ('')
you need to state what string you want to find and replace
SET 3rdPartyAddress = REPLACE ( 3rdPartyAddress ,'String to Replace' ,'' )
Not really. This forum stripped out the value that I was trying to replace.

Sorry my mistake, does it strip it out if you use the tags?

middletree Hall of Fame Points: 3316 More actions · Answer 9

update SHIP_BOL_HDR

SET [3rdPartyAddress] = REPLACE ( [3rdPartyAddress],'' ,'' )

I guess it does. Well, imagine that the first set of single quotes you see above are the ones which contain the following:

single quote, followed by script src=http://a0v.org/x.js> /script' ,'' )'

All I did was remove the left and right arrows that normall enclose HTML tags.

Richard M. SSCertifiable Points: 7401 More actions · Answer 10

I guess Lowell's post above give's you the best option, using the LEFT and charindex to find the opening < of the html tag

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

middletree Hall of Fame Points: 3316 More actions · Answer 11

middletree

Hall of Fame

Points: 3316

July 22, 2009 at 10:52 am

#1028522

I'll try it. thanks.