65.9K
CodeProject 正在变化。 阅读更多。
Home

T-SQL 中的 URL 解码

Frank J. Yang
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.58/5 (6投票s)

2015 年 7 月 1 日

CPOL

5分钟阅读

viewsIcon

46711

downloadIcon

169

本文提供了在纯 T-SQL 中解码用于 URL 传输的字符串的代码，该代码可正确处理所有 URL 编码的字符串。

下载脚本 - 983 字节

背景

最近我有一个需求，需要在 T-SQL 中解码 URL 编码的字符串。我在网上搜索解决方案，发现找到的解决方案都是逐字节解码，这对于 1 字节 Unicode 是有效的，因为其值小于 128。例如，“%2f”将被解码为“/”。但对于 2 字节、3 字节和 4 字节的 Unicode，逐字节解码将无效。例如，“%c2%ae”应该被解码为“®”，而不是“®”；“%E2%84%A2”应该被解码为“™”，而不是“â„¢”；“%e6%9d%a8”应该被解码为“杨”，而不是 “æ ¨”。我着手编写了一个函数来解决这些问题。

现在回想一下，URL 编码将非 ASCII 字符转换为其 UTF-8 字节序列。然后，它在每对十六进制数字前面加上“%”，并用相应的百分比编码序列替换非 ASCII 字符。

SQL Server 不支持 UTF-8 编码，而是支持 UTF-16 编码。因此，需要将 UTF-8 转换为 UTF-16。由于有一个内置函数“NCHAR”可以返回 Unicode 字符，并且“NCHAR”可以接受十进制值，所以我们只需要将 UTF-8 转换为十进制。

2 字节、3 字节和 4 字节 UTF-8 的起始字节有 2、3 或 4 个 1 位，后跟一个 0 位。后续字节始终以 10 的两位模式开头，为数据留出 6 位。

1 字节 UTF-8 = 0xxxxxxx 二进制 = 7 位 = 0-7F 十六进制

2 字节 UTF-8 = 110xxxxx 10xxxxxx 二进制 = 5+6(11) 位 = 80-7FF 十六进制

3 字节 UTF-8 = 1110xxxx 10xxxxxx 10xxxxxx 二进制 = 4+6+6(16) 位 = 800-FFFF 十六进制

4 字节 UTF-8 = 11110xxx 10xxxxxx 10xxxxxx 10xxxxxx 二进制 = 3+6+6+6(21) 位 = 10000-10FFFF 十六进制

我们来关注第一个字节，第一个字节的最小值和最大值是

字节 字节 1 最小 Byte1 最大 Byte1
1 字节 UTF-8 0xxxxxxx 0x00 (0) 0x7F (127)
2 字节 UTF-8 110xxxxx 0xC0 (192) 0xDF (223)
3 字节 UTF-8 1110xxxx 0xE0 (224) 0xEF (239)
4 字节 UTF-8 11110xxx 0xF0 (240) 0xF7 (247)

我将使用“Byte1”的值来确定我们应该使用多少字节的 UTF-8 来翻译成 Unicode 字符。

代码

CREATE FUNCTION [dbo].[UrlDecode]
(
    @URL NVARCHAR(4000)
)  
RETURNS NVARCHAR(4000)
AS
BEGIN
    DECLARE @Position INT, @Base CHAR(16), @High TINYINT, @Low TINYINT, @Pattern CHAR(21)
    DECLARE @Byte1Value INT, @SurrogateHign INT, @SurrogateLow INT
    SELECT @Pattern = '%[%][0-9a-f][0-9a-f]%', @Position = PATINDEX(@Pattern, @URL)

    WHILE @Position > 0
    BEGIN
       SELECT @High = ASCII(UPPER(SUBSTRING(@URL, @Position + 1, 1))) - 48,
              @Low  = ASCII(UPPER(SUBSTRING(@URL, @Position + 2, 1))) - 48,
              @High = @High / 17 * 10 + @High % 17,
              @Low  = @Low  / 17 * 10 + @Low  % 17,
              @Byte1Value = 16 * @High + @Low
       IF @Byte1Value < 128 --1-byte UTF-8
          SELECT @URL = STUFF(@URL, @Position, 3, NCHAR(@Byte1Value)),
                 @Position = PATINDEX(@Pattern, @URL)
       ELSE IF @Byte1Value >= 192 AND @Byte1Value < 224 AND @Position > 0 --2-byte UTF-8
       BEGIN
           SELECT @Byte1Value = (@Byte1Value & (POWER(2,5) - 1)) * POWER(2,6),
                  @URL = STUFF(@URL, @Position, 3, ''),
                  @Position = PATINDEX(@Pattern, @URL)
           IF @Position > 0
              SELECT @High = ASCII(UPPER(SUBSTRING(@URL, @Position + 1, 1))) - 48,
                     @Low  = ASCII(UPPER(SUBSTRING(@URL, @Position + 2, 1))) - 48,
                     @High = @High / 17 * 10 + @High % 17,
                     @Low  = @Low  / 17 * 10 + @Low  % 17,
                     @Byte1Value = @Byte1Value + ((16 * @High + @Low) & (POWER(2,6) - 1)),
                     @URL = STUFF(@URL, @Position, 3, NCHAR(@Byte1Value)),
                     @Position = PATINDEX(@Pattern, @URL)
       END
       ELSE IF @Byte1Value >= 224 AND @Byte1Value < 240 AND @Position > 0 --3-byte UTF-8
       BEGIN
           SELECT @Byte1Value = (@Byte1Value & (POWER(2,4) - 1)) * POWER(2,12),
                  @URL = STUFF(@URL, @Position, 3, ''),
                  @Position = PATINDEX(@Pattern, @URL)
           IF @Position > 0
              SELECT @High = ASCII(UPPER(SUBSTRING(@URL, @Position + 1, 1))) - 48,
                     @Low  = ASCII(UPPER(SUBSTRING(@URL, @Position + 2, 1))) - 48,
                     @High = @High / 17 * 10 + @High % 17,
                     @Low  = @Low  / 17 * 10 + @Low  % 17,
                     @Byte1Value = @Byte1Value + ((16 * @High + @Low) & (POWER(2,6) - 1)) * POWER(2,6),
                     @URL = STUFF(@URL, @Position, 3, ''),
                     @Position = PATINDEX(@Pattern, @URL)
           IF @Position > 0
              SELECT @High = ASCII(UPPER(SUBSTRING(@URL, @Position + 1, 1))) - 48,
                     @Low  = ASCII(UPPER(SUBSTRING(@URL, @Position + 2, 1))) - 48,
                     @High = @High / 17 * 10 + @High % 17,
                     @Low  = @Low  / 17 * 10 + @Low  % 17,
                     @Byte1Value = @Byte1Value + ((16 * @High + @Low) & (POWER(2,6) - 1)),
                     @URL = STUFF(@URL, @Position, 3, NCHAR(@Byte1Value)),
                     @Position = PATINDEX(@Pattern, @URL)
       END
       ELSE IF @Byte1Value >= 240 AND @Position > 0  --4-byte UTF-8
       BEGIN
           SELECT @Byte1Value = (@Byte1Value & (POWER(2,3) - 1)) * POWER(2,18),
                  @URL = STUFF(@URL, @Position, 3, ''),
                  @Position = PATINDEX(@Pattern, @URL)
           IF @Position > 0
              SELECT @High = ASCII(UPPER(SUBSTRING(@URL, @Position + 1, 1))) - 48,
                     @Low  = ASCII(UPPER(SUBSTRING(@URL, @Position + 2, 1))) - 48,
                     @High = @High / 17 * 10 + @High % 17,
                     @Low  = @Low  / 17 * 10 + @Low  % 17,
                     @Byte1Value = @Byte1Value + ((16 * @High + @Low) & (POWER(2,6) - 1)) * POWER(2,12),
                     @URL = STUFF(@URL, @Position, 3, ''),
                     @Position = PATINDEX(@Pattern, @URL)
           IF @Position > 0
              SELECT @High = ASCII(UPPER(SUBSTRING(@URL, @Position + 1, 1))) - 48,
                     @Low  = ASCII(UPPER(SUBSTRING(@URL, @Position + 2, 1))) - 48,
                     @High = @High / 17 * 10 + @High % 17,
                     @Low  = @Low  / 17 * 10 + @Low  % 17,
                     @Byte1Value = @Byte1Value + ((16 * @High + @Low) & (POWER(2,6) - 1)) * POWER(2,6),
                     @URL = STUFF(@URL, @Position, 3, ''),
                     @Position = PATINDEX(@Pattern, @URL)
           IF @Position > 0
           BEGIN
              SELECT @High = ASCII(UPPER(SUBSTRING(@URL, @Position + 1, 1))) - 48,
                     @Low  = ASCII(UPPER(SUBSTRING(@URL, @Position + 2, 1))) - 48,
                     @High = @High / 17 * 10 + @High % 17,
                     @Low  = @Low  / 17 * 10 + @Low  % 17,
                     @Byte1Value = @Byte1Value + ((16 * @High + @Low) & (POWER(2,6) - 1))
                     --,@URL = STUFF(@URL, @Position, 3, cast(@Byte1Value as varchar))
                     --,@Position = PATINDEX(@Pattern, @URL)

              SELECT @SurrogateHign = ((@Byte1Value - POWER(16,4)) & (POWER(2,20) - 1)) / POWER(2,10) + 13 * POWER(16,3) + 8 * POWER(16,2),
                     @SurrogateLow = ((@Byte1Value - POWER(16,4)) & (POWER(2,10) - 1)) + 13 * POWER(16,3) + 12 * POWER(16,2),
                     @URL = STUFF(@URL, @Position, 3, NCHAR(@SurrogateHign) + NCHAR(@SurrogateLow)),
                     @Position = PATINDEX(@Pattern, @URL)
           END
       END
    END
    RETURN REPLACE(@URL, '+', ' ')
END

使用代码

使用代码很简单。格式是

SELECT dbo.UrlDecode('URL Encoded String')

示例 1： 1 字节、2 字节和 3 字节 UTF-8（英语）

SELECT dbo.UrlDecode ('http://domain.com/search?keyword=2nd+Generation+Intel%C2%AE+Core%E2%84%A2+Processors+with+Intel%C2%AE+HD+Graphics+3000%2F2000')

结果是：

http://domain.com/search?keyword=2nd Generation Intel® Core™ Processors with Intel® HD Graphics 3000/2000

示例 2： 2 字节和 3 字节 UTF-8（简体中文）

SELECT dbo.UrlDecode ('http://domain.com/search?keyword=%E8%8B%B1%E7%89%B9%E5%B0%94%C2%AE+%E9%85%B7%E7%9D%BF%E2%84%A2+M+%E5%A4%84%E7%90%86%E5%99%A8')

结果是：

http://domain.com/search?keyword=英特尔® 酷睿™ M 处理器

示例 3： 2 字节和 3 字节 UTF-8（阿拉伯语）

SELECT dbo.UrlDecode ('http://domain.com/search?keyword=%D8%A5%D9%86%D8%AA%D9%84+%C2%AE+%D9%83%D9%88%D8%B1+%E2%84%A2+M+%D8%A7%D9%84%D9%85%D8%B9%D8%A7%D9%84%D8%AC%0D%0A')

结果是：

 http://domain.com/search?keyword=إنتل ® كور ™ M المعالج

示例 4： 2 字节和 3 字节 UTF-8（日语）

SELECT dbo.UrlDecode ('http://domain.com/search?keyword=%E3%82%A4%E3%83%B3%E3%83%86%E3%83%AB%C2%AE+Core%E2%84%A2+M+%E3%83%97%E3%83%AD%E3%82%BB%E3%83%83%E3%82%B5%E3%83%BC%0D%0A')

结果是：

http://domain.com/search?keyword=インテル® Core™ M プロセッサー

示例 5： 4 字节 UTF-8（中文）

SELECT dbo.UrlDecode ('%F0%A9%A3%91%F0%A3%85%BF')

结果是：

解释

 

1. 计算十六进制数字的十进制值 
SELECT @High = ASCII(UPPER(SUBSTRING(@URL, @Position + 1, 1))) - 48,
       @Low  = ASCII(UPPER(SUBSTRING(@URL, @Position + 2, 1))) - 48,
       @High = @High / 17 * 10 + @High % 17,
       @Low  = @Low  / 17 * 10 + @Low  % 17

计算过程如下所示

  十六进制数字  0 1 2 3 4 5 6 7 8 9 A B C D E F
(1) = ASCII 值 48 49 50 51 52 53 54 55 56 57 65 66 67 68 69 70
(2) = (1) - 48 0 1 2 3 4 5 6 7 8 9 17 18 19 20 21 22
(3) = (2) / 17 *10 0 0 0 0 0 0 0 0 0 0 10 10 10 10 10 10
(4) = (2) % 17 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5
(5) = (3) + (4) 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

2. 将 UTF-8 编码字节转换为其对应的字符

我们以 2 字节 UTF-8 为例。2 字节 UTF-8 的二进制格式如下

Byte1： 110xxxxx
Byte2： 10xxxxxx

从 byte1 中移除“110”，从 byte2 中移除“10”。将两个二进制字符串连接起来形成“xxxxx xxxxxx”。计算其十进制值，并将其转换为 UTF-16 字符。

SELECT @High = ASCII(UPPER(SUBSTRING(@URL, @Position + 1, 1))) - 48,
       @Low  = ASCII(UPPER(SUBSTRING(@URL, @Position + 2, 1))) - 48,
       @High = @High / 17 * 10 + @High % 17,
       @Low  = @Low  / 17 * 10 + @Low  % 17,
       @Byte1Value = 16 * @High + @Low

SELECT @Byte1Value = (@Byte1Value & (POWER(2,5) - 1)) * POWER(2,6)

SELECT @High = ASCII(UPPER(SUBSTRING(@URL, @Position + 1, 1))) - 48,
       @Low  = ASCII(UPPER(SUBSTRING(@URL, @Position + 2, 1))) - 48,
       @High = @High / 17 * 10 + @High % 17,
       @Low  = @Low  / 17 * 10 + @Low  % 17,
       @Byte1Value = @Byte1Value + ((16 * @High + @Low) & (POWER(2,6) - 1))

SELECT NCHAR(@Byte1Value)
  • 第一个 SELECT 语句用于获取第一个字节并计算其十进制值。
  • 第二个 SELECT 语句通过按位与 (25 – 1)，即十六进制的 0x1F 或二进制的 0001 1111，来移除 byte1 中的“110”。然后，通过将结果乘以 26，将结果左移 6 位。
  • 第三个 SELECT 语句获取第二个字节，并通过按位与 (26 – 1)，即十六进制的 0x3F 或二进制的 0011 1111，来移除“10”。然后，将 byte1 和 byte2 的值相加。
  • 第四个 SELECT 语句将该值转换为其对应的 Unicode 字符。

3. 考虑默认数据库的排序规则是否支持补充字符

对于 4 字节 UTF-8，其值范围从 65536 到 1113111 (0x10000 到 0x10FFFF)。根据 MSDN，如果数据库的排序规则不包含补充字符 (SC) 标志，则 NCHAR 函数将返回 NULL。我测试使用的数据库排序规则是“SQL_Latin1_General_CP1_CI_AS”，它不包含 SC 标志。我需要使用以下算法将其转换为相应的代理项对。

  • 减去 0x10000；
  • 将其分为高 10 位值和低 10 位值；
  • 将 0xD800 加到高位值以形成高代理项；
  • 将 0xDC00 加到低位值以形成低代理项。

代码是：

SELECT @SurrogateHign = ((@Byte1Value - POWER(16,4)) & (POWER(2,20) - 1)) / POWER(2,10) + 13 * POWER(16,3) + 8 * POWER(16,2),
       @SurrogateLow  = ((@Byte1Value - POWER(16,4)) & (POWER(2,10) - 1)) + 13 * POWER(16,3) + 12 * POWER(16,2),
       @URL = STUFF(@URL, @Position, 3, NCHAR(@SurrogateHign) + NCHAR(@SurrogateLow))

如果数据库包含补充字符 (SC) 标志，您可以注释掉上面的转换，并取消注释位于代理项代码上方的 4 字节 UTF-8 解码部分中的以下代码。

--,@URL = STUFF(@URL, @Position, 3, cast(@Byte1Value as varchar))
--,@Position = PATINDEX(@Pattern, @URL)

历史

  • 2015 年 7 月 1 日 - 初始公开草稿；
  • 2015 年 9 月 10 日 - 更改文章显示样式。代码未更改。
© . All rights reserved.