Find and Replace Injection String in Server 2005

Question

Find and Replace Injection String in Server 2005

allaspects

SSC Enthusiast

Points: 164
More actions
April 7, 2014 at 7:27 am

#151337

A website I manage has recently fallen victim to an SQL injection, so I've been trying to find a quick find and replace script to remove the simple string of text that was injected. I'm running SQL server 2005 on windows 2003.
I've tried several different scripts from around the internet (including this site), only one of them has worked but unfortunately I have to enter it in table and column at a time.
UPDATE [database].[dbo].
SET ColumnName = CAST(REPLACE(CAST(ColumnName as NVarchar(MAX)), 'stringtofind', 'replacewith') AS NText)
WHERE ColumnName LIKE '%stringtofind%'
I was hoping to find it and replace it in all tables and columns in the database, how would I go about doing that? (and before you link to any other articles on here, know that I've tried every find and replace script here and none of them work so far)
The query above is the only one that has worked. How would I use that (nvarchar(max), ntext, etc) on all tables and columns?

Viewing 15 posts - 1 through 15 (of 25 total)

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

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 1

Have you looked into using the system tables (sys.objects and sys.columns) to generate the script for you? i.e. select from all tables that could have been updated (columns with varchar, nvarchar, text, etc.) and generate a script to run an update statement. Here is a link discussing what I am talking about http://www.jephens.com/2008/07/27/how-to-clean-up-after-a-sql-injection-attack/

I would consider something like.

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

allaspects SSC Enthusiast Points: 164 More actions · Answer 2

I was hoping for something a little clearer and more simplified. Unfortunately, most of my experience is in mysql and I'm not really a windows server person but we have to keep our old site alive another month or two until the new one is ready, which means cleaning out this injection in a timely manner.

I have seen a few articles related to that, but unfortunately every attempt I have taken to use that method has ended up in error.

Lowell SSC Guru Points: 323518 More actions · Answer 3

take a look at this thread, which in turn can point to other threads.

there are three different methods , by three different authors, that can search all tables and columns for specific strings.

any of those solutions can get you which tables and columns to specifically create update statements for:

http://www.sqlservercentral.com/Forums/Topic1515219-391-1.aspx

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!

allaspects SSC Enthusiast Points: 164 More actions · Answer 4

Thanks! Unfortunately, I have tried those methods without any success. I am bound and determined to find a solution to this.

I will post one if I find one before someone else.

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 5

allaspects (4/7/2014)
Thanks! Unfortunately, I have tried those methods without any success.

What do you mean without success? You are getting errors? it is not finding the correct columns in the right tables? Missing tables? Let us what isn't working and maybe we can help. I'm not sure you will find a quick and easy solution without a little bit of tweaking to get it right.

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 6

allaspects (4/7/2014)
Thanks! Unfortunately, I have tried those methods without any success. I am bound and determined to find a solution to this.
I will post one if I find one before someone else.

we'd need more details about what seemed to go wrong. those are three versions, all of which were created by some damn fine DBAs, as well as myself.

calling my proc, for example exec sp_uglysearch 'a string that matches the sql injection' will return subqueries for every matching critieria; from there it should be easy to build some update commands.

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!

allaspects SSC Enthusiast Points: 164 More actions · Answer 7

Well with the code I have been using it helps remove this particular string of spam but only one column and one table at a time. The injected spam string didn't fit in all of the columns because of the length of that field, so it cut some characters off of the end everywhere (ranging from 1 character missing to most of them missing) so I have go back and not only do it one table and one column at a time, but also delete one character off the end and run it again (so that it gets the ones stuck in limited fields)... that being said, with most of the scripts I've found on here (and other sites) I either get various differing error messages, it has problems with NText/NVarchar and just says "completed" without actually doing anything, or it just simply selects a couple of fields but then does nothing else.

Code I've been using

UPDATE [database].[dbo].[TableName]

SET ColumnName = CAST(REPLACE(CAST(ColumnName as NVarchar(MAX)), '</title><style>.avo7{position:absolute;clip:rect(471px,auto,auto,471px);}</style><div class=avo7><a href=http://spammerwebsite.address >spammerwebsite </a> more text</div>', '') AS NText)

WHERE ColumnName LIKE '%</title><style>.avo7{position:absolute;clip:rect(471px,auto,auto,471px);}</style><div class=avo7><a href=http://spammerwebsite.address >spammerwebsite </a> more text</div>%'

Mock of injected string

</title><style>.avo7{position:absolute;clip:rect(471px,auto,auto,471px);}</style><div class=avo7><a href=http://spammerwebsite.address >spammerwebsite </a> more text</div>

I was just hoping to find a way to have it perform the task I have above but while updating all tables/columns so I don't have to enter it in one at a time.

allaspects SSC Enthusiast Points: 164 More actions · Answer 8

For example, when I use sp_UGLYSEARCH I get

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'END'.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 9

allaspects (4/7/2014)
I was hoping for something a little clearer and more simplified. Unfortunately, most of my experience is in mysql and I'm not really a windows server person but we have to keep our old site alive another month or two until the new one is ready, which means cleaning out this injection in a timely manner.
I have seen a few articles related to that, but unfortunately every attempt I have taken to use that method has ended up in error.

Right after you get done finding the injection in SQL Server, you should probably do the same thing for MySQL. If the code allowed it in SQL Server, the code in MySQL will probably allow it, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

allaspects SSC Enthusiast Points: 164 More actions · Answer 10

Right after you get done finding the injection in SQL Server, you should probably do the same thing for MySQL. If the code allowed it in SQL Server, the code in MySQL will probably allow it, as well.

Well, the new website/system wasn't written anything like the old one and is unique from the old one. The old one was written by a previous employee in 2003 with this point and click programmer software called "Ironspeed". The flaws/injections are a result of using ironspeed, for the last two months or so we have to have it, I have fixed a handful of the exploits and setup cloudflare to help protect against future ones. Hopefully that helps at least temporarily, unfortunately though, I still have to find a way to remove this string from all of the tables and columns in the mean time since it is a live server. If I have to do it the hard way as I've been doing it, so be it, I was just hoping to find a faster and easier way to do it.

When I find a solution though, I will post it, because no one should ever have to go through this stressful and tedious nonsense.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

First, I have never used IronSpeed, but I take exception to you blaming the tool. It comes down to the person using the tool not knowing how to protect the database from SQL Injection.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 12

Lynn Pettis (4/7/2014)
First, I have never used IronSpeed, but I take exception to you blaming the tool. It comes down to the person using the tool not knowing how to protect the database from SQL Injection.

I would blame the tool for suggesting that it's a good idea to execute dynamic code without parametrisation or validation.

Look for Data access layer in this page: http://www.ironspeed.com/products/Application-Architecture.aspx

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

Luis Cazares (4/7/2014)
Lynn Pettis (4/7/2014)
First, I have never used IronSpeed, but I take exception to you blaming the tool. It comes down to the person using the tool not knowing how to protect the database from SQL Injection.
I would blame the tool for suggesting that it's a good idea to execute dynamic code without parametrisation or validation.
Look for Data access layer in this page: http://www.ironspeed.com/products/Application-Architecture.aspx

Still comes down to the developer using the tool. How the tool is used is up to the developer, not the tool.

allaspects SSC Enthusiast Points: 164 More actions · Answer 14

Google it. It's a point and click application builder, there are countless flaws and exploits in it... go on, google it, I'll wait. If it were a real programming development kit/sdk/whatever else, it would be the programmer's fault, but by your analogy/logic you're also saying that getting hacked/worm/etc because of your operating system is the fault of the user not the operating system.

However, I have found duct tape resolution for my issue since I last posted, so it is what it is. Either way LOL, I didn't write the application to begin with.