search replace sql string doesn't work

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

search replace sql string doesn't work

Author

Topic

cindy0904
Starting Member

4 Posts

Posted - 2012-10-16 : 16:31:43

Hi, someone wrote a sql string for me to erase a bunch of injected spam urls from our mssql db. it works but erases too much. Can anyone help out?

it's 1 table called "items" and 2 fields that have the code that needs to be searched and erased, but leaving the original descriptions and photos.

table: "items"
field: "descr"
this is an example of the current field content:
1982 Jeep, red, automatic, clean. </title><div style=position:absolute;top:-9999px;><a href=http://paydayloansdenverqbqr.com >payday loans denver</a></div>

we need to erase everything including and after "</title>"

second field: "photo"
example of current content:
<a href="javascript:;" onClick="JustSoPicWindow('/adminn/images/towdolly1_f.jpg','500','310','* * Click window to close * * ','#FFFFFF','hug image','0')"><img src="/adminn/images/towdolly1_t.jpg" border="0"><</title><div style=position:absolute;top:-9999px;><a href=http://paydayloansdenverqbqr.com >payday loans denver</a></div>

we need to erase everything including and after "<</title>"

any help? I'd be happy to Paypal a little for the effort.

thanks!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-10-16 : 17:05:29

There are a bunch of ways to do this. Here is one way using the PATINDEX and SUBSTRING functions:

DECLARE @SearchString NVARCHAR(4000) = '</title>'
DECLARE @Descr NVARCHAR(4000) = 
'1982 Jeep, red, automatic, clean. </title><div style=position:absolute;top:-9999px;><a href=http://paydayloansdenverqbqr.com >payday loans denver</a></div>'


SELECT 
    PATINDEX('%' + @SearchString + '%', @Descr) AS FistOccurrence,
    SUBSTRING(@Descr, 0, PATINDEX('%' + @SearchString + '%', @Descr) + LEN(@SearchString))  AS TruncatedString

cindy0904
Starting Member

4 Posts

Posted - 2012-10-16 : 17:17:28

great! i'll try it!

cindy0904
Starting Member

4 Posts

Posted - 2012-10-16 : 19:43:26

hmm, it's not working. Each of these line items have different descriptions and photos. I know very little about sql, someone wrote the original one for me. Does this just replace the descr field and then do the same for the photo field? I tried to run it and these are the errors i got. thx.

SUBSTRING(@Descr, 0, PATINDEX('%' + @SearchString + '%', @Descr) + LEN(@SearchString)) AS TruncatedString
Msg 139, Level 15, State 1, Line number 0
Cannot assign a default value to a local variable.

Msg 139, Level 15, State 1, Line number 0
Cannot assign a default value to a local variable.

Msg 137, Level 15, State 2, Line number 7
Must declare the scalar variable "@SearchString".

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-16 : 23:19:51

the expression should be assigned to local variable and not assigned as a DEFAULT value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cindy0904
Starting Member

4 Posts

Posted - 2012-10-19 : 17:53:19

I'm beyond new to sql. really only need this code. Sorry, don't even know what visakh16's comment means.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-19 : 22:21:23

quote:
Originally posted by cindy0904

I'm beyond new to sql. really only need this code. Sorry, don't even know what visakh16's comment means.

can you show your currently used code?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources