Sql-Server

為什麼在 SQL Server 中將 Base64 字元串解碼為 NVARCHAR 時得到不正確的字元?

  • July 15, 2018

我一直在研究如何使用 SQL Server 解碼 Base64,並且在網上搜尋了許多解決方案(一些來自這裡)之後似乎是基於這種方法的。

SELECT CAST(CAST('Base64StringHere' as XML ).value('.','varbinary(max)') AS VARCHAR(250))

當我有 ASCII 文本時,這非常有效。但是,當我有以下法語文本時,它會被損壞(可能是由於 VARCHAR 的限制)。

Où est le café le plus proche?
T8O5IGVzdCBsZSBjYWbDqSBsZSBwbHVzIHByb2NoZT8=

並提供以下輸出。

Où est le café le plus proche?

我認為相對簡單的解決方法是將其更改為CASTNVARCHAR但這會再次導致損壞。

SELECT CAST(CAST('T8O5IGVzdCBsZSBjYWbDqSBsZSBwbHVzIHByb2NoZT8=' as XML ).value('.','varbinary(max)') AS NVARCHAR(250) )

썏₹獥⁴敬挠晡꧃氠⁥汰獵瀠潲档㽥

我的搜尋引擎技能可能讓我失望,但我似乎找不到其他有我問題的人。

有什麼想法嗎?

問題是您將 UTF-8 編碼的字元串編碼為 Base64。因此,解碼 Base64 會返回原始的 UTF-8 字節序列。SQL Server 僅將 UTF-16 Little Endian 用於NVARCHAR數據,甚至用於 XML。因此,是(和 )ù的兩字節 UTF-8 序列的 8 位版本。ù``0xC3``0xB9

幸運的是,可以將 UTF-8 編碼的字元串轉換為 UTF-16,甚至轉換為非 Unicode 程式碼頁(如果程式碼頁支持所有被轉換的字元)。訣竅是將 Base64 解碼字節以文本表示形式(即使轉換錯誤的字元)轉換為 XML。這個技巧的訣竅是您需要添加<?xml ...>聲明(通常省略)並指定源編碼:

DECLARE @Base64Value NVARCHAR(500) = N'T8O5IGVzdCBsZSBjYWbDqSBsZSBwbHVzIHByb2NoZT8=';

DECLARE @BinaryValue VARBINARY(500) =
   CONVERT(XML, @Base64Value).value('.','varbinary(max)');

DECLARE @IntermediaryValue VARCHAR(500) = CONVERT(VARCHAR(500), @BinaryValue);

SELECT @BinaryValue, @IntermediaryValue;
-- 0x4FC3B920657374206C6520636166C3A9206C6520706C75732070726F6368653F
-- Oֳ¹ est le cafֳ© le plus proche?


-- This is to NVARCHAR, which will always work:
SELECT CONVERT(NVARCHAR(500),
   CONVERT(XML, '<?xml version="1.0" encoding="UTF-8"?>' +  @IntermediaryValue)
             );
-- Où est le café le plus proche?


-- This is to VARCHAR, but "success" will depend on the Code Page
-- specified by the default Collation of the current Database:
SELECT CONVERT(VARCHAR(500),
   CONVERT(XML, '<?xml version="1.0" encoding="UTF-8"?>' +  @IntermediaryValue)
             );

-- In a DB with a Latin1_General Collation it works:
-- Où est le café le plus proche?


-- In a DB with a Hebrew Collation, it gets the following error:
/*
  Msg 6355, Level 16, State 1, Line XXXXX
  Conversion of one or more characters from XML to target collation impossible
*/

請注意,此技巧僅適用於從各種源編碼轉換為UTF-16 Little Endian(因為這就是 SQL Server 中的 XML 數據類型在內部儲存字元串的方式)。此方法不能用於將 UTF-16 轉換為 UTF-8 或某些其他非 SQL Server 支持的編碼。


下面是一個封裝了上述步驟的 Inline-TVF:

GO
CREATE FUNCTION dbo.ConvertBase64EncodedUTF8ToUTF16LE
(
 @Base64EncodedUTF8String VARCHAR(8000)
)
RETURNS TABLE
AS RETURN

   SELECT 
       CONVERT(NVARCHAR(500),
               CONVERT(XML,
                       '<?xml version="1.0" encoding="UTF-8"?>' +
                       CONVERT(VARCHAR(500),
                               CONVERT(XML, @Base64EncodedUTF8String)
                                 .value('.','varbinary(max)')
                              )
                      )
              ) AS [DecodedValue];

GO

然後測試:

SELECT *
FROM   dbo.ConvertBase64EncodedUTF8ToUTF16LE(
         'T8O5IGVzdCBsZSBjYWbDqSBsZSBwbHVzIHByb2NoZT8=');
-- Où est le café le plus proche?

CROSS APPLY如果進行基於集合的操作,只需使用 with 。

引用自:https://dba.stackexchange.com/questions/212160