HTML Encode a varchar

Question

HTML Encode a varchar

Robert Lomax

Ten Centuries

Points: 1334
More actions
August 25, 2005 at 6:37 am

#67839

In Yukon, would writing a CLR managed extension be the best way to HTML encode (< > " & etc...) a varchar or is there some built in method of doing this?
Thanks.
August 29, 2005 at 8:00 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

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

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 1

There is no built in method for it. Whether or not a CLR method is the best way to go is difficult to answer, it depends on a lot of factors. However, IMHO SQL Server 2005 brings nothing new to change my opinion that these encodings shuold not be made in the database.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 2

Steve Jones - SSC Editor

SSC Guru

Points: 736455

August 29, 2005 at 9:10 am

#585883

Agree with Chris. Do it as a function in the app/.

Robert Lomax Ten Centuries Points: 1334 More actions · Answer 3

Thanks Chris. Astute as always.

Our design calls for the XML data to be produced by SQL Server 2005, which it is simply great for, but there are also elements that must be HTMLEncoded. We went for a C# Managed Extension rather than increasing the size and difficulty managing the data by HTMLEncoding it before we put it in the DB.

Creating it was easy, new DB Project, Add Item, C# Function, simple. The one wierd thing was that we were not allowed to add a Reference to System.Web, so we could access System.Web.HttpUtility.HTMLEncode(). So we created the HTMLEncode function ourselves. Still a lifesaver.

Robert Lomax Ten Centuries Points: 1334 More actions · Answer 4

WOW!

We just found out that if SQL Server 2005 is creating XML, and the column contains HTML data, it AUTOMATICALLY does the HTMLEncode() for you.

We don't need our Managed Extension, and the XML creation is handled by SQL Server 2005, which is what we wanted.

Live and learn, and sometimes be amazed.

Thanks all.

rmcmillian Valued Member Points: 53 More actions · Answer 5

Hello all,

How is this to be accomplished when using SQL Server 2000? Is this the creation of a custom function inside of SQL Server 2000 and calling it within the Select statement for the column/field you need to be HTMLencoded?

The source XML i'm working with is built within a stored procedure. The stored procedure uses a view as input. There are particular fields I'm concerned with; however if there is a function that can take the entire XML and encode appropriately, this would be the best approach.

Thanks in advance for your response.

Robert Lomax Ten Centuries Points: 1334 More actions · Answer 6

SQLXML in SQL Server 2000 isn't suited for this task, in my opinion. I agree with the posters above who say in SQL2000 you should do this outside of your database. I'm still amazed Yukon handles it all for you in the DB.

If you must, you could create a UDF to HtmlEncode a string, but remember, the result will grow in size, and it may be hard to predict. "" will become "$quot; so if you have alot of quotes, it can get very large.

Another possibility is to just use Yukon pointed at a SQL2000 database to do your task, assuming you will license it immediatly on availability.