Alex Rivera | Logout

HTML Encoding in T-SQL?

Asked 2009-03-12T16:18:07.937
22

Is there any function to encode HTML strings in T-SQL? I have a legacy database which contains dodgey characters such as '<', '>' etc. I can write a function to replace the characters but is there a better way?

I have an ASP.Net application and when it returns a string it contains characters which cause an error. The ASP.Net application is reading the data from a database table. It does not write to the table itself.

asp.net sql t-sql
Edit
Report

4 Answers

27

We have a legacy system that uses a trigger and dbmail to send HTML encoded email when a table is entered, so we require encoding within the email generation. I noticed that Leo's version has a slight bug that encodes the & in &lt; and &gt; I use this version:

CREATE FUNCTION HtmlEncode
(
    @UnEncoded as varchar(500)
)
RETURNS varchar(500)
AS
BEGIN
  DECLARE @Encoded as varchar(500)

  --order is important here. Replace the amp first, then the lt and gt. 
  --otherwise the &lt will become &amp;lt; 
  SELECT @Encoded = 
  Replace(
    Replace(
      Replace(@UnEncoded,'&','&amp;'),
    '<', '&lt;'),
  '>', '&gt;')

  RETURN @Encoded
END
GO
answered 2009-09-25T10:33:52.987
16

You shouldn't fix the string in SQL. A better way is to use a function in ASP.net called HtmlEncode, this will cook the special characters that cause the issues you're seeing see the example below. I hope this helps.

string htmlEncodedStr = System.Web.HttpUtility.HtmlEncode(yourRawStringVariableHere);
string decodedRawStr =  System.Web.HttpUtility.HtmlDecode(htmlEncodedStr);

Edit: Since you're data binding this from a datatable. Use an inline expression to call HTMLEncode in the markup of the GridView or whatever control your using and this will still satisfy your data binding requirement. See example below. Alternativly you can loop every record in the data table object and update each cell with the html encoded string prior to data binding.

<%# System.Web.HttpUtility.HtmlEncode(Eval("YourColumnNameHere")) %>
answered 2009-03-12T16:22:42.230
2

If you're displaying a string on the web, you can encode it with Server.HTMLEncode().

If you're storing a string in the database, make sure the database field is "nchar", instead of "char". That will allow it to store unicode strings.

If you can't control the database, you can "flatten" the string to ASCII with Encoding.ASCII.GetString.

answered 2009-03-12T16:33:31.363
-1

assign it to Text Property of label, it will be auto encoded by .NET

answered 2010-11-08T15:40:47.327

Your Answer