HTML Encoding in T-SQL?

asp.net sql tsql

57,923

Solution 1

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 < and > 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

Solution 2

It's a bit late, but anyway, here the proper ways:

HTML-Encode (HTML encoding = XML encoding):

DECLARE @s NVARCHAR(100)
SET @s = '<html>unsafe & safe Utf8CharsDon''tGetEncoded ÄöÜ - "Conex"<html>'
SELECT (SELECT @s FOR XML PATH(''))

HTML-encode in a query:

SELECT 
    FIELD_NAME  
    ,(SELECT FIELD_NAME AS [text()] FOR XML PATH('')) AS FIELD_NAME_HtmlENcoded 
FROM TABLE_NAME

HTML-Decode:

SELECT CAST('<root>' + '&lt;root&gt;Test&amp;123' + '</root>' AS XML).value(N'(root)[1]', N'varchar(max)');

If you want to do it properly, you can use a CLR-stored procedure.
However, it gets a bit complicated, because you can't use the System.Web-Assembly in CLR-stored-procedures (so you can't do System.Web.HttpUtility.HtmlDecode(htmlEncodedStr);). So you have to write your own HttpUtility class, which I wouldn't recommend, especially for decoding.

Fortunately, you can rip System.Web.HttpUtility out of the mono sourcecode (.NET for Linux). Then you can use HttpUtility without referencing system.web.

Then you write this CLR-Stored-Procedure:

using System;
using System.Collections.Generic;
using System.Text;

using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
//using Microsoft.SqlServer.Types;


namespace ClrFunctionsLibrary
{


    public class Test
    {


        [Microsoft.SqlServer.Server.SqlFunction]
        public static SqlString HtmlEncode(SqlString sqlstrTextThatNeedsEncoding)
        {
            string strHtmlEncoded = System.Web.HttpUtility.HtmlEncode(sqlstrTextThatNeedsEncoding.Value);
            SqlString sqlstrReturnValue = new SqlString(strHtmlEncoded);

            return sqlstrReturnValue;
        }


        [Microsoft.SqlServer.Server.SqlFunction]
        public static SqlString HtmlDecode(SqlString sqlstrHtmlEncodedText)
        {
            string strHtmlDecoded = System.Web.HttpUtility.HtmlDecode(sqlstrHtmlEncodedText.Value);
            SqlString sqlstrReturnValue = new SqlString(strHtmlDecoded);

            return sqlstrReturnValue;
        }


        // ClrFunctionsLibrary.Test.GetPassword
        //[Microsoft.SqlServer.Server.SqlFunction]
        //public static SqlString GetPassword(SqlString sqlstrEncryptedPassword)
        //{
        //    string strDecryptedPassword = libPortalSecurity.AperturePortal.DecryptPassword(sqlstrEncryptedPassword.Value);
        //    SqlString sqlstrReturnValue = new SqlString(sqlstrEncryptedPassword.Value + "hello");

        //    return sqlstrReturnValue;
        //}

        public const double SALES_TAX = .086;

        // http://msdn.microsoft.com/en-us/library/w2kae45k(v=vs.80).aspx
        [SqlFunction()]
        public static SqlDouble addTax(SqlDouble originalAmount)
        {
            SqlDouble taxAmount = originalAmount * SALES_TAX;

            return originalAmount + taxAmount;
        }


    } // End Class Test


} // End Namespace ClrFunctionsLibrary

And register it:

GO

/*
--http://stackoverflow.com/questions/72281/error-running-clr-stored-proc
-- For unsafe permission
EXEC sp_changedbowner 'sa'
ALTER DATABASE YOUR_DB_NAME SET TRUSTWORTHY ON 

GO
*/


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HtmlEncode]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[HtmlEncode]
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HtmlDecode]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[HtmlDecode]
GO




IF  EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'ClrFunctionsLibrary' and is_user_defined = 1)
DROP ASSEMBLY [ClrFunctionsLibrary]

GO


--http://msdn.microsoft.com/en-us/library/ms345101.aspx



CREATE ASSEMBLY [ClrFunctionsLibrary]
AUTHORIZATION [dbo]
FROM 'D:\username\documents\visual studio 2010\Projects\ClrFunctionsLibrary\ClrFunctionsLibrary\bin\Debug\ClrFunctionsLibrary.dll' 
WITH PERMISSION_SET = UNSAFE  --EXTERNAL_ACCESS  --SAFE
;

GO




CREATE FUNCTION [dbo].[HtmlDecode](@value [nvarchar](max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
-- [AssemblyName].[Namespace.Class].[FunctionName]
EXTERNAL NAME [ClrFunctionsLibrary].[ClrFunctionsLibrary.Test].[HtmlDecode]
GO





CREATE FUNCTION [dbo].[HtmlEncode](@value [nvarchar](max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
-- [AssemblyName].[Namespace.Class].[FunctionName]
EXTERNAL NAME [ClrFunctionsLibrary].[ClrFunctionsLibrary.Test].[HtmlEncode]
GO



/*
EXEC sp_CONFIGURE 'show advanced options' , '1';
 GO
 RECONFIGURE;
 GO
 EXEC sp_CONFIGURE 'clr enabled' , '1'
 GO
 RECONFIGURE;
 GO

EXEC sp_CONFIGURE 'show advanced options' , '0';
 GO
 RECONFIGURE;
*/

Afterwards, you can use it like normal functions:

SELECT
     dbo.HtmlEncode('helloäÖühello123') AS Encoded
    ,dbo.HtmlDecode('hello&auml;&Ouml;&uuml;hello123') AS Decoded

Anybody who just copy-pastes, please note that for efficiency reasons, you would use

public const double SALES_TAX = 1.086;

// http://msdn.microsoft.com/en-us/library/w2kae45k(v=vs.80).aspx
[SqlFunction()]
public static SqlDouble addTax(SqlDouble originalAmount)
{
     return originalAmount * SALES_TAX;
}

if you'd use this function in production.

See here for the edited mono classes:
http://pastebin.com/pXi57iZ3
http://pastebin.com/2bfGKBte

You need to define NET_2_0 in the build options

Solution 3

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")) %>

Solution 4

I don't think data in a database should know or care about the user interface. Display issues should be handled by the presentation layer. I wouldn't want to see any HTML mingled into the database.

Solution 5

You can simply use 'XML PATH in your query'. For example;

DECLARE @encodedString VARCHAR(MAX)

SET @encodedString = 'give your html string you want to encode'

SELECT @encodedString
SELECT (SELECT @encodedString FOR XML PATH(''))

Now as your wish you can you this in your own sql function. Hope this will help.

View more solutions

57,923

Leo Moore

Updated on July 04, 2020

Comments

Leo Moore about 4 years

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.
- Lazarus over 15 years
  
  The answers below are good but if those characters shouldn't be in the data then I'd suggest cleaning the data. Otherwise James is spot on.
- Leo Moore over 15 years
  
  The characters are correct in the data and if I change the data I could break the legacy app. So thats not an option.
- Steve about 14 years
  
  If your problem is in your ASP.NET code, then the 'best practices' way to handle this is to use the Server.HtmlEncode() function in the ASP.NET layer. Technically, you aren't supposed to store 'processed' data in your DB, you want the plain, real data there, not customized for a particular presentation system (HTML). If at some point you needed just the plain text without HTML entities, you still have a clean version of it in your DB.
Amit Patil over 15 years

You can also use a BoundField. msdn.microsoft.com/en-us/library/…
Leo Moore about 15 years

I agree completely, but its not my choice. Its a legacy app with HTML type characters in the Guid (or what passes as the Guid).
duffymo about 15 years

Presentation in the primary key? OMG. I'd refactor that as quickly as possible.
Leo Moore almost 15 years

Thanks, you are correct. I did chnage it in production but forgot to update the previous post.
Giulio Muscarello over 11 years

Welcome to Stack Overflow! Please expand your answer, including other information: this is too short.
Stefan Steiger almost 11 years

@duffymo: Why not ? It may be a busy site. Saving content HtmlEncoded saves the encoding on every request. E.g. Rendered HTML created from Wiki markup - that will save a lengthy render on every request - you can pre-render it when markup is changed and saved.
duffymo almost 11 years

Ridiculous. Lengthy? Nope. I stand by my original ridicule.
Stefan Steiger almost 10 years

@Beniaminus: While it eliminates the most dangerous XML characters, that's actually far from "Html-Encoded", but I guess you know that yourselfs :)
Stefan Steiger almost 10 years

@Rez.Net: Be careful however, this can and will throw on valid html characters that are not specified in XML, like ä ö or © or é â &oelig; ç Ω ß æ &OElig; ñ etc.
Stefan Steiger over 7 years

@duffymo: Just for the record - I meant save the presentation text in an additional db field HTML-encoded. Using presentation text as primary-key is stupid, I agree on that 120%.
duffymo over 7 years

Has this been bothering you for more than three years? Why are you replying again now? You didn't sound like you agreed 120% three years ago.
KekuSemau about 5 years

From 2009 and no comment that this is wrong? Try < and see the result.