Post reply

deleting some data from a column

  • November 26, 2013 at 10:43 pm

    #285537

    Hi All

    i had some virus attach on my database and my online database suddenly got corrupted. with every column data some malicious code is attached.

    like this

    18k white gold</title><style>.a9y3{position:absolute;clip:rect(465px,auto,auto,465px);}</style><div class=a9y3><a href=http://zddpb.careerdfd.com >zddpb.careerdfd.com</a><a href=http://crfm.careerorx.com >crfm.careerorx.com</a><a href=http://fcwiw.careeruyp.com >fcwiw.careeruyp.com</a></div>

    it should be - 18k white gold

    but some string from </title> is attached with it so now i want to delete sting starting from </title>

  • November 26, 2013 at 11:23 pm

    #1669335

    Here are 3 approaches...

    1. Update the column with Subatring function and charindex function

    2. Add a column with similar datatype(seems to be varchar)

    update the newly created column with substring function and charindex function

    3. Alter Column by change length to data whatever you want

    Data would terminated....Then again use alter to give the original length

  • November 27, 2013 at 6:04 am

    #1669442

    If you're not sure of the damage, you may want to consider restoring the database from a back up to ensure everything is back the way it should be.

    Mark

  • November 27, 2013 at 2:30 pm

    #1669585

    Can you specify the pattern of affected data? its just one row so is difficult to find out the solution for everybody...

    This should help...

    select LEFT(Column, charindex('<',Column))

    Sql String solutions...

  • November 27, 2013 at 2:41 pm

    #1669587

    from my point of view the first step would be to figure out how it happened and to close that "back door". Otherwise you might get faced with the very same attack shortly after you've cleaned the data...

    Clear the mess after that: If the data in your column should always end with the < /title > tag, and this should be the first occurence of this tag, too, then use LEFT(yourcolumn,PATINDEX('%</title>%', yourcolumn))


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • November 27, 2013 at 9:58 pm

    #1669612

    Thank you everyone. you guys are really awesome and helpful. your suggestions worked.

    Heartly Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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