T-SQL 中的 HTML 编码？

Question

提问by Leo Moore

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?

是否有任何函数可以在 T-SQL 中编码 HTML 字符串？我有一个旧数据库，其中包含诸如“<”、“>”等狡猾字符。我可以编写一个函数来替换这些字符，但有没有更好的方法？

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 应用程序，当它返回一个字符串时，它包含导致错误的字符。ASP.Net 应用程序正在从数据库表中读取数据。它不会写入表本身。

Answer 1

回答by Beniaminus

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:

我们有一个遗留系统，它使用触发器和 dbmail 在输入表格时发送 HTML 编码的电子邮件，因此我们需要在电子邮件生成中进行编码。我注意到 Leo 的版本有一个小错误，它对 & 进行编码 <，>我使用这个版本：

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

Answer 2

回答by Stefan Steiger

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

有点晚了，但无论如何，这里有正确的方法：

HTML-Encode (HTML encoding = XML encoding):

HTML 编码（HTML 编码 = XML 编码）：

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

HTML-encode in a query:

查询中的 HTML 编码：

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

HTML-Decode:

HTML 解码：

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.

如果您想正确地执行此操作，可以使用 CLR 存储过程。
然而，它变得有点复杂，因为你不能在 CLR 存储过程中使用 System.Web-Assembly（所以你不能做 System.Web.HttpUtility.HtmlDecode(htmlEncodedStr);）。因此，您必须编写自己的 HttpUtility 类，我不建议这样做，尤其是用于解码。

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

幸运的是，您可以从 Mono 源代码（Linux 的 .NET）中提取 System.Web.HttpUtility。然后你可以在不引用 system.web 的情况下使用 HttpUtility。

Then you write this CLR-Stored-Procedure:

然后你写这个 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

有关编辑后的单声道类，请参见此处：
http: //pastebin.com/pXi57iZ3
http://pastebin.com/2bfGKBte

You need to define NET_2_0 in the build options

您需要在构建选项中定义 NET_2_0

Answer 3

回答by James

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.

您不应该修复 SQL 中的字符串。更好的方法是在 ASP.net 中使用一个名为 HtmlEncode 的函数，这将处理导致您看到的问题的特殊字符，请参见下面的示例。我希望这有帮助。

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.

编辑：由于您是从数据表绑定数据。使用内联表达式在 GridView 或您使用的任何控件的标记中调用 HTMLEncode，这仍将满足您的数据绑定要求。请参阅下面的示例。或者，您可以循环数据表对象中的每条记录，并在数据绑定之前使用 html 编码的字符串更新每个单元格。

<%# System.Web.HttpUtility.HtmlEncode(Eval("YourColumnNameHere")) %>

Answer 4

回答by duffymo

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.

我认为数据库中的数据不应该了解或关心用户界面。显示问题应由表示层处理。我不想看到任何 HTML 混入数据库。

Answer 5

回答by Andomar

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

如果您在 Web 上显示字符串，则可以使用 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.

如果您在数据库中存储字符串，请确保数据库字段是“nchar”，而不是“char”。这将允许它存储 unicode 字符串。

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

如果您无法控制数据库，则可以使用 Encoding.ASCII.GetString 将字符串“展平”为 ASCII。

Answer 6

回答by sumith madhushan

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

您可以简单地在查询中使用“XML PATH”。例如;

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.

现在，如您所愿，您可以在自己的 sql 函数中使用它。希望这会有所帮助。

Answer 7

回答by Jeremy Giaco

I've been trying to do this today in T-SQL, mostly for fun at this point since my requirements changed, but i figured one way out. You can use a table of unicode characters, built from the NCHAR() function or just import it, iterating from 0 to 65535 (or less if you just need the first 512 or something). Then rebuild the string. There are probably better ways to rebuild the string, but this works in a pinch.

我今天一直在尝试用 T-SQL 来做这件事，主要是为了好玩，因为我的要求发生了变化，但我想出了一个办法。您可以使用由 NCHAR() 函数构建的 unicode 字符表，也可以直接导入它，从 0 到 65535 迭代（如果您只需要第一个 512 或其他，则可以更少）。然后重建字符串。可能有更好的方法来重建字符串，但这在紧要关头有效。

---store unicode chars into a table so you can replace those characters withthe decimal value

`

CREATE TABLE #UnicodeCharacters( DecimalValue INT, UnicodeCharacter NCHAR ) ;

创建表 #UnicodeCharacters( DecimalValue INT, UnicodeCharacter NCHAR ) ;

--loop from 0 to highest unicode value you want and dump to the table you created
DECLARE @x INT = 0;
WHILE @x <= 65535
    BEGIN
        BEGIN
            INSERT INTO #UnicodeCharacters(DecimalValue, UnicodeCharacter)
            SELECT  @x,NCHAR(@x)
        END
        ;

        SET @x = @x + 1
        ;
    END
;

--index for fast retrieval
CREATE CLUSTERED INDEX CX_UnicodeCharacter_DecimalValue ON #UnicodeCharacters(UnicodeCharacter, DecimalValue);

--this is the string that you want to html-encode...
DECLARE @String NVARCHAR(100) = N'人This is a test - ?';

--other vars
DECLARE @NewString NVARCHAR(100) = '';
DECLARE @Word TABLE(Character NCHAR(1));
DECLARE @Pos INT = 1;

--run through the string and check each character to see if it is outside the regex expression
WHILE @Pos <= LEN(@String)
BEGIN
    DECLARE @Letter NCHAR(1) = SUBSTRING(@String,@Pos,1);
    PRINT @Letter;
    --rebuild the string replacing each unicode character outside the regex with &#[unicode value];
    SELECT  @NewString = @NewString + 
                CASE 
                    WHEN @Letter LIKE N'%[0-9abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ-!@#$%^&*()_+-= ]%' THEN @Letter
                    ELSE '&#' + CAST(uc.DecimalValue AS VARCHAR(10)) + ';'
                END
    FROM    #UnicodeCharacters uc
    WHERE   @Letter = uc.UnicodeCharacter COLLATE JAPANESE_UNICODE_BIN

    SET @Pos += 1
END

--end result
SELECT @NewString
;

` I know typically you would use [0-9A-Za-z], but for some reason, it considered accented characters within the scope of that expression when I did that. So I explicitly used every character that i didn't want to convert to Unicode in the expression.

` 我知道通常您会使用 [0-9A-Za-z]，但出于某种原因，当我这样做时，它认为该表达式范围内的重音字符。所以我明确使用了我不想在表达式中转换为 Unicode 的每个字符。

Last note, I had to use a different collation to do matches on Unicode characters, because the default LATIN collation (CI or otherwise) seemed to incorrectly match on accented characters, much like the regex in the LIKE.

最后一点，我必须使用不同的排序规则来匹配 Unicode 字符，因为默认的 LATIN 排序规则（CI 或其他）似乎与重音字符不正确匹配，很像 LIKE 中的正则表达式。

Answer 8

回答by Saurabh

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

将其分配给标签的 Text 属性，它将由 .NET 自动编码

Answer 9

回答by Mark

I haven't tried this solution myself but what I would try is utilise the sql server / .NET CLR integration and actually call the C# HTMLEncode function from the T-SQL. This may be inefficient but I suspect it would give you the most accurate result.

我自己还没有尝试过这个解决方案，但我会尝试利用 sql server / .NET CLR 集成并实际从 T-SQL 调用 C# HTMLEncode 函数。这可能效率低下，但我怀疑它会给你最准确的结果。

My starting point for working out how to do this would be http://msdn.microsoft.com/en-us/library/ms254498%28VS.80%29.aspx

我制定如何做到这一点的起点是http://msdn.microsoft.com/en-us/library/ms254498%28VS.80%29.aspx

Answer 10

回答by Leo Moore

OK here is what I did. I created a simple function to handle it. Its far from complete but at least handles the standard <>&characters. I'll just add to it as I go along.

好的，这就是我所做的。我创建了一个简单的函数来处理它。它远未完成，但至少可以处理标准<>&字符。我会在继续的过程中添加它。

CREATE FUNCTION HtmlEncode
(
    @UnEncoded as varchar(500)
)
RETURNS varchar(500)
AS
BEGIN
    DECLARE @Encoded as varchar(500)   
    SELECT @Encoded = Replace(@UnEncoded,'<','&lt;')
    SELECT @Encoded = Replace(@Encoded,'>','&gt;')
    SELECT @Encoded = Replace(@Encoded,'&','&amp;')   
    RETURN @Encoded    
END

I can then use:

然后我可以使用：

Select Ref,dbo.HtmlEncode(RecID) from Customers

This gives me a HTML safe Record ID. There is probably a built in function but I can't find it.

这给了我一个 HTML 安全的记录 ID。可能有一个内置函数，但我找不到它。

T-SQL 中的 HTML 编码？

提问by Leo Moore

回答by Beniaminus

回答by Stefan Steiger

回答by James

回答by duffymo

回答by Andomar

回答by sumith madhushan

回答by Jeremy Giaco

回答by Saurabh

回答by Mark

回答by Leo Moore

相关推荐

最近更新

标签

T-SQL 中的 HTML 编码？

提问by Leo Moore

回答by Beniaminus

回答by Stefan Steiger

回答by James

回答by duffymo

回答by Andomar

回答by sumith madhushan

回答by Jeremy Giaco

回答by Saurabh

回答by Mark

回答by Leo Moore

相关推荐

SQL 从多个表中选择 count(*)

SQL 将 varchar 列转换为浮点数

为什么要大写我的 SQL 关键字？

如何在 SQL Server 中声明全局变量..？

相关推荐

最近更新

标签