I have seen a number of posts on various forums regarding issues with loading the System.Web assembly as Unsafe in SQL Server so that the HttpUtility.HtmlEncode/HtmlDecode method can be used to encode/decode an input for usage. The following function can be cataloged as SAFE in SQL Server and does exactly the same things that the Method in System.Web does for the HtmlEncode.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Globalization;
using System.Text;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static string HtmlEncode(string s)
{
if (s == null)
{
return null;
}
int num = IndexOfHtmlEncodingChars(s, 0);
if (num == -1)
{
return s;
}
StringBuilder builder = new StringBuilder(s.Length + 5);
int length = s.Length;
int startIndex = 0;
Label_002A:
if (num > startIndex)
{
builder.Append(s, startIndex, num - startIndex);
}
char ch = s[num];
if (ch > '>')
{
builder.Append("&#");
builder.Append(((int)ch).ToString(NumberFormatInfo.InvariantInfo));
builder.Append(';');
}
else
{
char ch2 = ch;
if (ch2 != '"')
{
switch (ch2)
{
case '<':
builder.Append("<");
goto Label_00D5;
case '=':
goto Label_00D5;
case '>':
builder.Append(">");
goto Label_00D5;
case '&':
builder.Append("&");
goto Label_00D5;
}
}
else
{
builder.Append(""");
}
}
Label_00D5:
startIndex = num + 1;
if (startIndex < length)
{
num = IndexOfHtmlEncodingChars(s, startIndex);
if (num != -1)
{
goto Label_002A;
}
builder.Append(s, startIndex, length - startIndex);
}
return builder.ToString();
}
private static int IndexOfHtmlEncodingChars(string s, int startPos)
{
char[] chars = s.Substring(startPos).ToCharArray();
for (int i = 0; i < chars.Length; i++)
{
char ch = chars[i];
if (ch <= '>')
{
switch (ch)
{
case '<':
case '>':
case '"':
case '&':
return (startPos + i);
case '=':
break;
}
}
else if ((ch >= '\x00a0') && (ch < 'Ā'))
{
return (startPos + i);
}
}
return -1;
}
};