Remove damage caused by SQL injection

[stocktondesigns]

I have a table in our SQL database that has a bunch of hacker URL's appended to about 80,000 rows. I would like to go into each row and erase the offending characters, maintaining my originally entered data. My current approach is to do a search/replace with the table open, however, this is taking a really long time. Is there a faster way to execute this kind of cleanup? It's the same value in every record in about 3 or 4 fields.

I'd truly appreciate any help.

 

[fredericofonseca]

a simple SQL update will do the trick if the string is always same size or always at the end of your data.

example.

update my_tbl set f1 = SUBSTRING(f1,1,charindex('ABC',f1) - 1);

where ABC is the string you want to replace.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[bborissov]

If the string is the same and if you want to keep all information but that string you could use REPLACE Function:

UPDATE YourTable SET TheField = REPLACE(TheField,OffendedString,'')

Be sure you have a good backup first.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[stocktondesigns]

fredericofonseca,
your suggestion makes sense, however, when I try to run it, I get an error telling me 'invalid legth parameter passed to the substring function'

 

[philhege]

Append this WHERE clause:

WHERE (charindex('ABC',f1) - 1) > 0

HTH,

Phil H.
Some Bank
-----------
Time's fun when you're having flies.

 

[SQLSister]

And remember this will happen again until you fix the code that allowed the SQL injection attack to occur.

Here are a couple of links to help you with that:

http://www.unixwiz.net/techtips/sql-injection.html

http://www.sommarskog.se/dynamic_sql.html

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

 

[stocktondesigns]

Works great! thank you!