【ORACLE】详解oracle数据库UTL_ENCODE包各个函数的模拟算法

前言

在前后端交互的开发中,经常会遇到需要将一些二进制数据,比如图片文件,编码成可打印的ascii字符进行传递；又者,开发人员不希望数据在传递中明文显示传递的文本内容,并且有些字符不是ascii字符,无法很好地兼容各种环境。所以这就涉及到了各种编码的转换。

虽然目前各种开发语言均可处理此类编码,但的确是存在一些场景需要在数据库中对数据直接进行编解码的操作。比如应用直接将编码后的数据存到了数据库,但如果直接在数据库去进行查找时,发现编码后的数据无法读,无法写出想要的数据的检索条件。

在oracle数据库中,有一个UTL_ENCODE包,里面就包含了各种编解码的函数
官方文档 https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/UTL_ENCODE.html

函数列表

函数列表	描述
BASE64_DECODE	Reads the base 64-encoded RAW input string and decodes it to its original RAW value
BASE64_ENCODE	Encodes the binary representation of the RAW value into base 64 elements and returns it in the form of a RAW string
MIMEHEADER_DECODE	Decodes a string from mime header format
MIMEHEADER_ENCODE	Encodes a string into mime header format
QUOTED_PRINTABLE_DECODE	Reads the varchar2 quoted printable format input string and decodes it to the corresponding RAW string
QUOTED_PRINTABLE_ENCODE	Reads the RAW input string and encodes it to the corresponding quoted printable format string
TEXT_DECODE	Decodes a character set sensitive text string
TEXT_ENCODE	Encodes a character set sensitive text string
UUDECODE	Reads the RAW uuencode format input string and decodes it to the corresponding RAW string
UUENCODE	Reads the RAW input string and encodes it to the corresponding uuencode format string

函数分类

首先,"encode"在这里意思是"编码","decode"为解码,编码后的东西无法直接阅读,需要解码后才能识别。

然后来给这几个函数划分一下类型
从这个列表可以看出,它一共包含了5对编解码函数,但是,实际上,关于编解码的算法,这里只有3个,
即

BASE64
QUOTED_PRINTABLE
UUENCODE

并且这3个函数的编码前和编码后的数据参数类型都是二进制类型(RAW),

而另外的MIMEHEADER_ENCODE和TEXT_ENCODE这两个函数,需要传入一个编码类型的参数,如下

  -- Define constants for use by text_encode/decode and mimeheader_encode
  -- in the 'encoding' parameter
  base64           CONSTANT PLS_INTEGER := 1;
  quoted_printable CONSTANT PLS_INTEGER := 2;

可以发现能够通过指定编码类型,来使用base64或者quoted_printable的编码方式(就是上面3个编码函数剔除了UUENCODE)。
并且MIMEHEADER_ENCODE和TEXT_ENCODE这两个函数的编码前和编码后的数据参数类型都是文本(varchar2),
其中TEXT_ENCODE只会输出编码后的字符串本身,而MIMEHEADER_ENCODE会同时输出字符集、编码方式、以及编码后的字符串。
可以看下表的对比

名称	编解码参数类型	函数名是否为编码算法	备注
BASE64	raw	Y	基于64个可打印字符来表示二进制数据
QUOTED_PRINTABLE	raw	Y	可打印字符引用编码
UUENCODE	raw	Y	非标准uuencode算法,存在bug
TEXT	varchar2	N	可以选择BASE64或者QUOTED_PRINTABLE两种编码方式之一
MIMEHEADER	varchar2	N	编码结果是在TEXT编码结果的前面加上字符集和编码方式

下面开始逐个进行说明

BASE64

用法：

---编码
select 
utl_raw.cast_to_varchar2(
utl_encode.base64_encode(
utl_raw.cast_to_raw('今天天气不错哇~')))
from dual;
--输出 '5LuK5aSp5aSp5rCU5LiN6ZSZ5ZOHfg=='

--解码  
select 
utl_raw.cast_to_varchar2(
utl_encode.base64_decode(
utl_raw.cast_to_raw('5LuK5aSp5aSp5rCU5LiN6ZSZ5ZOHfg==')))
from dual;
--输出 '今天天气不错哇~'

BASE64太常见了，oracle/mysql/postgresql等常用数据库和java/python等常用开发语言都有支持。BASE64的算法其实和UUENCODE重合度很高，由于后面会详细介绍UUENCODE的算法,因此本篇暂不会详细说明BASE64的算法了。

简单来说，这个编码就是将数据对应的二进制值取3个字节（即24位2进制数据），按6位作为一个新字节，得到4个新字节，然后每个字节都可以表示64以内的数值，将其对应到64个可打印字符表即可得到其对应的BASE64编码，当然中间还是有一些换算及特殊处理，具体可参考百科
https://baike.baidu.com/item/BASE64/8545775

QUOTED_PRINTABLE

用法：

--编码
select 
utl_raw.cast_to_varchar2(
utl_encode.quoted_printable_encode(
utl_raw.cast_to_raw('ABC567,今天天气不错哇~,A'))) A 
from dual;
--输出 'ABC567,=E4=BB=8A=E5=A4=A9=E5=A4=A9=E6=B0=94=E4=B8=8D=E9=94=99=E5=93=87~,A'

--解码  
select 
utl_raw.cast_to_varchar2(
utl_encode.quoted_printable_decode(
utl_raw.cast_to_raw('ABC567,=E4=BB=8A=E5=A4=A9=E5=A4=A9=E6=B0=94=E4=B8=8D=E9=94=99=E5=93=87~,A')))
from dual;
--输出 'ABC567,今天天气不错哇~,A'

通过对比编码前和编码后可以发现，如果本身就是可打印字符，那么编码后会保持不变；如果是不可打印字符，比如中文，那么编码后会根据其对二进制数据的十六进制数值，使用等于号拼上字节的形式表示，
比如这里默认是UTF8编码，"今"字的UTF8编码的二进制数据的十六进制数值为 "E4BB8A",然后把这三个字节的每个字节前面加上等于号，就得到了"=E4=BB=8A".

既然等于号是个关键的符号，那么如果原始数据里有等于号会不会导致解码出错呢？答案是不会，因为编码的时候如果遇到了等于号，会转换成"=3D"这个"3D" 其实就是等于号的十六进制ascii码。

另外，这种编码方式其实像极了URLENCODE，只是符号有所区别而已，然后URLENCODE还多了几个保留符号

TEXT

用法

--编码，指定字符集,不指定编码方式，默认为 2，即 quoted_printable
select 
utl_encode.text_encode('A今天天气不错哇~2', 
                       encode_charset => 'ZHS16GBK')
from dual;
--输出 'A=BD=F1=CC=EC=CC=EC=C6=F8=B2=BB=B4=ED=CD=DB~2'

--编码,指定字符集，指定编码方式为1，即BASE64
select 
utl_encode.text_encode('A今天天气不错哇~2', 
                       encode_charset => 'ZHS16GBK',
                       encoding => 1)
from dual;
--输出 'Qb3xzOzM7Mb4sru07c3bfjI='

--解码，针对base64字符串，指定字符集
select 
utl_encode.text_decode('Qb3xzOzM7Mb4sru07c3bfjI=', 
                       encode_charset => 'ZHS16GBK',
                       encoding => 1)
from dual
--输出 'A今天天气不错哇~2'

其实可以看到，如果是要对纯文本进行编码或者解码，用TEXT的这种方式会比用base64或者quoted_printable本身的那两个函数要简单得多，不用在raw和varchar2之间转来转去，而且还能指定字符集，要知道对于中文来说，GBK的长度只有UTF8的三分之二。
但是，这种方式解码的时候，必须要知道这个字符串是用什么字符集及什么方式编码得到的，否则很可能就无法正确解码，所以，此时可以用 MIMEHEADER 这种方式

MIMEHEADER

用法

--编码，指定字符集,不指定编码方式，默认为 2，即 quoted_printable
select 
utl_encode.mimeheader_encode('A今天天气不错哇~2', 
                       encode_charset => 'ZHS16GBK')
from dual;
--输出 '=?ZHS16GBK?Q?A=BD=F1=CC=EC=CC=EC=C6=F8=B2=BB=B4=ED=CD=DB~2?='

--编码,指定字符集，指定编码方式为1，即BASE64
select 
utl_encode.mimeheader_encode('A今天天气不错哇~2', 
                       encode_charset => 'ZHS16GBK',
                       encoding => 1)
from dual;
--输出 '=?ZHS16GBK?B?Qb3xzOzM7Mb4sru07c3bfjI=?='

--解码,
select 
utl_encode.mimeheader_decode('=?ZHS16GBK?Q?A=BD=F1=CC=EC=CC=EC=C6=F8=B2=BB=B4=ED=CD=DB~2?=')
from dual;
--输出 'A今天天气不错哇~2'

--解码
select 
utl_encode.mimeheader_decode('=?ZHS16GBK?B?Qb3xzOzM7Mb4sru07c3bfjI=?=')
from dual;
--输出 'A今天天气不错哇~2'

和text方式对比可以发现，mimeheader这种方式只是在text的基础上，前后增加了一点东西而已，具体格式为

=?字符集?编码方式?text编码字符串?=

即等于号开始、等于号结尾，用问号把3个参数及开始结束符分隔开，其中编码方式只有2种

B :BASE64
Q :quoted_printable

由于在编码后输出的字符串中已经带上了编码信息，因此解码的时候就不需要再指定字符集和编码方式了。

UUENCODE

用法:
有4个输入参数，

r:需要编码的raw值
type:类型（1表示头加身体加尾巴，2表示头加身体，3表示只要身体，4表示身体加尾巴）
filename:文件名
permission:许可

其中文件名和许可都包含在输出的头部信息里，也就是说，只有类型为1或2时，文件名和许可才会被输出

--编码，其余参数值默认
select 
utl_raw.cast_to_varchar2(
utl_encode.uuencode(
utl_raw.cast_to_raw('今天天气真好哇~') )) 
from dual;

--输出
begin 0 uuencode.txt
>Y+N*Y:2IY:2IYK"4YYR?Y:6]Y9.'?@  

end

--编码，指定type，指定文件名，指定许可
select 
utl_raw.cast_to_varchar2(
utl_encode.uuencode(r          => utl_raw.cast_to_raw('今天天气真好哇~'),
                    type       => 1 ,
                    filename   => 'filename.txt', 
                    permission => 777)
) 
from dual;

--输出
begin 777 filename.txt
>Y+N*Y:2IY:2IYK"4YYR?Y:6]Y9.'?@  

end

首先要说明一下，以上执行结果是来自于oracle数据库，但实际上这个输出结果是错误的！
将生成的这串编码放到在线uuencode解码的网站中去解码的话，解码后的数据会存在数据缺失的情况！
因此不建议使用ORACLE数据库中的UUENCODE编码函数，这个函数有严重的BUG!
这是Oracle中少见的持续了几十年还不修复的BUG！

ORACLE数据库的utl_encode.uuencode函数,和标准的uuencode有区别,标准里规定每行60个字符,除最后一行外,应该都是大写字母"M"开头,但oracle中的是每行77个字符,以小写字母"l"开头,在mos上有记录BUG,
UTL_ENCODE.UUENCODE Does Not Follow The Standard Uuencode Format (文档 ID 2197134.1)
官方解决方案是

Until Bug:6655881 is addressed, use a workaround or use a 3rd party external procedure based on the uuencode/uudecode standard

简单来说就是这个bug已经收录,官方建议使用第三方外部过程,直到BUG修复

但这个bug是2016年报告的呀,这都5年多了。。。不过utl_encode.uudecode倒是能解析各种各样的长度(但是oracle解析标准的uuencode编码会丢失数据)

当然,uuencode这种编码已经被base64取代了,oracle不把这个bug当回事也情有可原,毕竟这个功能开发出来几十年了,也没多少人反馈有问题。

但强迫症难忍啊。

于是，我仔细研究uuencode的通用标准，然后在openGauss里，完整地重写了一次uuencode和uudecode函数。

uuencode算法

以下面这句话为例，字符集为UTF8

今天天气真好哇~明天天气怎么样呢？

获取其二进制数据（这里为了节省长度，以十六进制展示）

e4bb8ae5a4a9e5a4a9e6b094e79c9fe5a5bde593877ee6988ee5a4a9e5a4a9e6b094e6808ee4b988e6a0b7e591a2

取前3个字节

e4 bb 8a

其二进制表达形式为

11100100 10111011 10001010

然后按6位一组，前面补两位0(可以不补，这里仅做完整字节的示意)，变成4个字节

00111001 00001011 00101110 00001010

得到这4个字节的十进制数值为

57 11 46 10

这4个数字分别都加上十进制的32，得

89 43 78 42

对照ASCII码表，得这4个数字分别对应的ascii字符为

Y + N *

这样就处理好了前三个字节的数据。
这里有个需要注意的地方，由于6位二进制最小可以为'000000'，其十进制为0，再加32得32，对应的ascii字符为一个空格（space），此时需要将其替换成十进制96对应的ascii字符 "`",即常规电脑键盘左上角的重音符号。
接着继续处理后三个字节，就这么一直循环处理到最后，如果最后不足3个字节，则用二进制的"00000000"补足到3个字节来处理，因此你会发现，使用uuencode编码的结果，后面经常会出现重音符号，这和base64编码末尾经常出现的等于号其实是一个原因。

所有的三个字节编码完后，拼起来得到:

Y+N*Y:2IY:2IYK"4YYR?Y:6]Y9.'?N:8CN6DJ>6DJ>:PE.:`CN2YB.:@M^61HN^\GP``

接下来就要做换行处理了，标准是60个字符一行，即

Y+N*Y:2IY:2IYK"4YYR?Y:6]Y9.'?N:8CN6DJ>6DJ>:PE.:`CN2YB.:@M^61
HN^\GP``

然后在每行前面补一个代表长度的字符

此时的字符，其实每4个字符来源之前的3个字节，也就是说，60个字符是对应之前的45个字节，将这个45加32，得77，取77对应的ascii字符"M",所以这个编码的第一行前面要拼个"M",
同理，第2行有8个字符，对应之前6个字节，6加32得38，即 "&" (其实这里有个问题，有些算法会算出来这里为36,即"$"，但这一般不会影响解码结果，因为大部分解码程序都不会去校验这个长度，所以这也是这种编码的一个槽点)，

MY+N*Y:2IY:2IYK"4YYR?Y:6]Y9.'?N:8CN6DJ>6DJ>:PE.:`CN2YB.:@M^61
$HN^\GP``

然后换一行，加一个重音符号

MY+N*Y:2IY:2IYK"4YYR?Y:6]Y9.'?N:8CN6DJ>6DJ>:PE.:`CN2YB.:@M^61
$HN^\GP``
`

到此，主体部分编码就完成了，然后根据需要，在前面或者后面加上头部信息及尾部信息

begin 0 uuencode.txt
MY+N*Y:2IY:2IYK"4YYR?Y:6]Y9.'?N:8CN6DJ>6DJ>:PE.:`CN2YB.:@M^61
$HN^\GP``
`
end

这样就完成了。

可以看到，一般情况下，如果生成多行主体，那么除去最后一行外的每一行，第一个字符都应该为大写的"M"（ascii码77），而ORACLE为小写的"l",很可能是因为ORACLE开发人员自作聪明，以为是每行放77个字符，减去表示长度的这个字符，还剩76个字符，然后76加32得108，108对应的ascii字符就是小写的英文字母"l"!

从整个编码过程来看，中间的3字节转4字节是相当巧妙，但是那个分行加长度，以及空格替换成重音符号又是相当坑，而且这64个字符中是可能出现其他开发语言的保留符号的，传输过程中还得进行转义，所以这种编码方式已经被base64取代了。

base64与uuencode的不同点是，uuencode是直接映射到ascii码表，而base64则是单独定义了要映射到哪64个字符，并且base64没有这么坑爹的要加行长度。

下面是两种编码方式的映射字符表，可以看到都是64个字符，但BASE64少了很多奇奇怪怪的符号

base64      ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/
uuencode    `!"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_

因此，如果懂了UUENCODE是如何编码的，那么同理也可以写出BASE64的编码程序

总结

这篇虽然不及上一篇关于UTL_RAW的更接近底层逻辑，但是这篇更接近开发的实际使用场景，让大家知道其实数据库里也是可以做一些复杂的事情的。

另外，这篇文章也算是我在写完openGauss的UTL_ENCODE兼容包的一个总结吧，兼容代码可以在我的项目或者compat_tools项目中找到
utl_encode.sql
compat-tools

本文作者： DarkAthena

本文链接： https://www.darkathena.top/archives/about-utl-encode-and-emulate-cal

版权声明： 本博客所有文章除特别声明外，均采用CC BY-NC-SA 3.0 许可协议。转载请注明出处！

posted on 2022-03-15 21:50 DarkAthena 阅读(888) 评论(0) 编辑收藏举报

刷新页面返回顶部