January 14, 2010 at 11:04 am
Can anyone please guide me on how to create a stored procedure or a trigger that would be run every hour or in intervals, to check all the entire DB i.e. all the columns for any html tags (for e.g.< >),and if html tags have been stored in any colums, the procedure would strip the html tags.
thanks for the help
January 14, 2010 at 11:17 am
I'd be inclined to recommend using CLR regex functions for that.
And I'd use them on inputs, instead of scanning the whole database every hour.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 14, 2010 at 11:33 am
thanks
i am a newbie to this could u please guide me as to how i would do this, for example i want to check the login table and all its field to see if any html tags have been passed on by cross site scripting and stip them out, my login table has
id, email,name pwd lastname and phone number, Please help, if i find out hopw to create a procedure for one tbale may be i can apply for all the tables in a similar way and place this on our code
January 14, 2010 at 11:41 am
Why exactly do you think you need to do this?
January 14, 2010 at 11:45 am
all i need to do is to strip out any html tags from all the colums in the db entered by croos site scripting. i know that there is a javascript function that does it in the forms but i have no clue abt how to check and strip it from the columns
thanks
let me know if that makes sense
January 14, 2010 at 11:48 am
i have this code in my snippets for stripping HTML tags, but there's other ways to do this, regualr expressions and CLR being boatloads faster, as GSquared stated.
this might not check for badly formed/malformed html with missing ending brackets...it's been a while since i tested this.
--===== Replace all HTML tags with nothing
WHILE CHARINDEX(' CHARINDEX('<',@HTMLText)
SELECT @HTMLText = STUFF(@HTMLText,
CHARINDEX('<',@HTMLText),
CHARINDEX('>',@HTMLText)-CHARINDEX('<',@HTMLText)+1,
'')
Lowell
January 14, 2010 at 12:10 pm
Just don't run a script like that on any column that might intentionally include XML.
And hope nobody has a password with brackets in it.
That's why I recommend actual regex functions. Will handle HTML tags without destroying anything else.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 14, 2010 at 12:34 pm
avi-631555 (1/14/2010)
all i need to do is to strip out any html tags from all the colums in the db entered by croos site scripting.
May I suggest, rather than trying to fix the results of XSS, fix the website and eliminate the cause of it?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2010 at 2:01 am
Just to try,
-- To get the list of Tags removed....
Declare @vTagNameTable Table(Tag varchar(max))
-- My HTML
Declare @v-2 varchar(max)
Set @v-2 = '<html DIR="LTR" xmlns:tool="http://www.microsoft.com/tooltip" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:ddue="http://ddue.schemas.microsoft.com/authoring/2003/5" xmlns:MSHelp="http://msdn.microsoft.com/mshelp"><A>Atif Sheikh < Atif
<Bold>Sheikh</Bold> </HTML> Atif <><><>>'
Declare @vCharI1 int -- Used to locate '<'
Declare @vCharI2 int -- Used to locate '>' to get Tag Name
Declare @vCharICheck int -- Used to Check for Tag Name
Declare @vEndTagName varchar(1000)
Declare @vStartPos int
Set @vStartPos = 0
While 1=1
begin
Set @vCharI1 = CharIndex('<',@v,@vStartPos)
if @vCharI1 > 0
begin
-- Get the Tag Name, if it is a HTML Tag
Set @vCharI2 = CharIndex('>',@v,@vCharI1 + 1)
if @vCharI2 > 0
begin
-- Check <>
if @vCharI2 = @vCharI1 + 1
begin
Set @vStartPos = @vCharI2 + 1
end
else
begin
Set @vCharICheck = CharIndex('<',@v,@vCharI1 + 1)
if (@vCharICheck < @vCharI2)
begin
-- Last < found was not start of tag. Just < character / sign
if @vCharICheck > 0
Set @vStartPos = @vCharICheck
else
begin
-- @vCharICheck = 0
-- Its a Last TAG...
Set @vEndTagName = SubString(@v,@vCharI1,(@vCharI2-@vCharI1) + 1)
if Not Exists(Select 1 from @vTagNameTable where Tag = @vEndTagName)
begin
Insert into @vTagNameTable
Select @vEndTagName
Set @v-2 = Replace(@v,@vEndTagName,'')
end
Set @vStartPos = @vCharI2
end
if @vStartPos = Len(@v)
begin
Select @vStartPos , Len(@v),@vCharICheck,@vCharI1
print 'Over Here....'
BREAK
end
end
else
begin
-- ITS A TAG....
Set @vEndTagName = SubString(@v,@vCharI1,(@vCharI2-@vCharI1) + 1)
if Not Exists(Select 1 from @vTagNameTable where Tag = @vEndTagName)
begin
Insert into @vTagNameTable
Select @vEndTagName
Set @v-2 = Replace(@v,@vEndTagName,'')
end
--Set @vStartPos = @vCharI2 + 1
Set @vStartPos = @vCharI1
if @vStartPos > Len(@v)
begin
print 'Here....'
BREAK
end
end
end
end
else
BREAK -- No Tag
end
else
BREAK
end
Select * from @vTagNameTable
Select @v-2 as StringWithoutTags
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply