Using wildcard in replace

[cdck]

I have been asked to clean up a database that was infected through injection SQL with script references throughout the tables. There are (so far) about 10 different script references that have been added to around 50 fields in 20 tables.

I want to write a stored procedure that can be used to clear these script references, and could be used again if re-infection with a new script occurred. So far, the only way that I have been able to do this is to add an update statement for every affected table for every found script.

I was hoping that I could use a wildcard to get them all at once, but this isn't working.

This is an example of an UPDATE that works:

[tt]UPDATE dbo.merchants
SET merchantName = REPLACE(merchantName, '<script src=

http://www.ausbnr.com/ngg.js></script>',

''), webSite = REPLACE(webSite, '<script src=

http://www.ausbnr.com/ngg.js></script>',

''), contactEmail = REPLACE(contactEmail, '<script src=

http://www.ausbnr.com/ngg.js></script>',

'')[/tt]

This is what I would like to do, but it isn't working:
[tt]UPDATE dbo.merchants
SET merchantName = REPLACE(merchantName, '<script%></script>', ''), webSite = REPLACE(webSite, '<script%></script>', ''), contactEmail = REPLACE(contactEmail, '<script%></script>', '')[/tt]

Nor does it work when I use a * instead of the %.

I found another thread that seemed similar (thread183-1398648), and the user there was directed to a microsoft article on RegEx (

http://msdn.microsoft.com/en-us/magazine/cc163473.aspx),

which I have looked over. I feel rather dense, as it seems beyond me to even determine which part of the article is supposed to apply to this issue, and how I would be able to make use of it.

Can anyone offer any clarity? Or even another source that's easier to understand?

Cheryl dc Kern

 

[SQLSister]

Of course you can't do it the way you tried, you are specifically telling the sqlserver what value merchantname should have. It would never let you use a wildcard on the right side of an update. To find the records you want to replace use a where clause.

"NOTHING is more important in a database than integrity." ESquared

 

[SQLSister]

Oh and you are getting rid of all that dynamic sql that let a sql injection attack work aren;t you?

"NOTHING is more important in a database than integrity." ESquared

 

[cdck]

Wow, you seem unhappy that I asked. Thank you for not answering.

Cheryl dc Kern

 

[Crowley16]

you can try for something like:

merchantName =
left(merchantName, patindex('<script')) +
right(merchantName, len(merchantName) - patindex('</script>')

this is not tested, and will probably need a bit of configuration but the method will work...

--------------------
Procrastinate Now!

 

[cdck]

Crowley16:

Thanks for the idea, I'll experiment with it and see what happens!

Cheryl dc Kern

 

[stocktondesigns]

Cheryl,
I find myself in the same predicament, were you able to find an easy way to cleanup sql injection damage in your database?

 

[cdck]

Actually, I just got back to trying to do a quick fix tonight, and found that it works using CASE and CHARINDEX. I have to do it for every field and table affected, but only once for each.

I didn't get to really try it out, but it seemed as if the above option would actually have me save the script references, and dispose of the rest of the data. But it did give me ideas where to start looking for a solution.

Here's what I did:

[tt]
ALTER PROCEDURE ScriptCleanup
--
AS


--CLIENTADDRESS
UPDATE dbo.clientAddress
SET streetTwo =
CASE
WHEN CHARINDEX('<', streetTwo) > 1 THEN Left( streetTwo, CHARINDEX('<', streetTwo)-1)
WHEN (CHARINDEX('<', streetTwo)-1) = 0 THEN ''
ELSE streetTwo
END,
streetOne =
CASE
WHEN CHARINDEX('<', streetOne) > 1 THEN Left(streetOne, CHARINDEX('<', streetOne)-1)
WHEN (CHARINDEX('<', streetOne)-1) = 0 THEN ''
ELSE streetOne
END[/tt]
Watch out that the < character is not actually needed in any of the fields. It appears that you could actually use <script in the CARINDEX, in order to avoid issues, but I didn't have to go that far.


I hope this helps!

Cheryl dc Kern