PostgreSQL, Greenplum ETL 之 - 非法字符(如0x00)过滤、转换(blob2text, bytea2text)
背景
如果输入的数据不合法，那么PostgreSQL数据库会报错，不允许写入。
postgres=# select E'\000';
ERROR: invalid byte sequence for encoding "UTF8": 0x00
LOCATION: report_invalid_encoding, wchar.c:2017
报错代码为wchar.c的report_invalid_encoding函数。
实际上这个问题在很多文章中有分析过：
《PostgreSQL WHY ERROR: invalid byte sequence for encoding “UTF8”》
《PostgreSQL 转义、UNICODE、与SQL注入》
《PostgreSQL 和 Greenplum pgcrypto 加解密bytea处理差异》
最好的解决办法是客户端在写入前检查输入字符的合法性，过滤不合法的字符。例如：
https://www.bbsmax.com/A/kjdwxDy6zN/
http://www.codeweblog.com/sqlserve%E5%88%B0pg%E8%BF%81%E7%A7%BB%E9%94%99%E8%AF%AF-%E6%97%A0%E6%95%88%E7%9A%84%E7%BC%96%E7%A0%81%E5%BA%8F%E5%88%97-utf8-0x00/
在数据库中，有没有方法实现ETL中数据清洗的部分呢？
对于不合法的字符，我们可以使用二进制写入，然后在二进制中过滤不合法字符并转化为字符串。
PostgreSQL 不合法字符清洗
输入为bytea，过滤非法字符，转换为字符串。
1、创建转换函数，将NUL字符转换为空格。
set escape_string_warning=on;
set standard_conforming_strings=on;
create or replace function blob2text (bytea) returns text as $$
declare
i int;
val bytea := $1;
res text;
sql text;
encoding name ;
begin
set escape_string_warning=on;
set standard_conforming_strings=on;
select getdatabaseencoding() into encoding;
i := position(bytea '\000' in val);
WHILE i > 0 LOOP
val := set_byte(val, i-1, 32);
i := position(bytea '\000' in val);
END LOOP;
sql := format('select convert_from(encode(%L,''escape'')::bytea, %L)', val, encoding) ;
-- raise notice '%', sql;
execute sql into res;
return res;
end;
$$ language plpgsql strict;
2、测试转换函数，符合要求
postgres=# select blob2text(bytea '你好\000哈哈\000hello');
NOTICE: select convert_from(encode(E'\\xc4e3bac320b9feb9fe','escape')::bytea, 'EUC_CN')
blob2text
-----------
你好 哈哈
(1 row)
Greenplum 不合法字符清洗
输入为bytea，过滤非法字符，转换为字符串。
1、创建转换函数，将NUL字符转换为空格。
set escape_string_warning=on;
set standard_conforming_strings=on;
create or replace function blob2text (bytea) returns text as $$
declare
i int;
val bytea := $1;
res text;
sql text;
encoding bytea ;
begin
set escape_string_warning=on;
set standard_conforming_strings=on;
select getdatabaseencoding() into encoding;
i := position(bytea '\000' in val);
WHILE i > 0 LOOP
val := set_byte(val, i-1, 32);
i := position(bytea '\000' in val);
END LOOP;
sql := $_$select convert(E'$_$||val||$_$', '$_$||encoding||$_$', '$_$||encoding||$_$')$_$;
-- raise notice '%', sql;
execute sql into res;
return res;
end;
$$ language plpgsql strict;
2、测试转换函数，符合要求
postgres=# select blob2text(bytea '你好\000哈哈\000hello');
NOTICE: select convert(E'\344\275\240\345\245\275 \345\223\210\345\223\210 hello', 'UTF8', 'UTF8')
blob2text
---------------------
你好 哈哈 hello
(1 row)
使用规则，自动清洗不合法字符
1、创建入口表test，创建目标表dest_test。用户将数据写入test，自动进行清洗，并写入目标表。
create table test (id int, info bytea);
create table dest_test (id int, info text);
-- 在入口表创建清洗规则
create rule r1 as on insert to test do instead insert into dest_test(id, info) values (NEW.id, blob2text(NEW.info));
2、测试，转换正常，非法字符转换为合法字符，可以写入了。
insert into test values (1, '你好\000哈哈\000hello');
db=# select * from test;
id | info
----+------
(0 rows)
db=# select * from dest_test;
id | info
----+-----------------
1 | 你好 哈哈 hello
(1 row)
小结
对于非法字符，建议在应用层进行过滤，在数据库中转换，影响写入速度，同时会使得数据库的开销变大。实在是万不得已的行为。
参考
《PostgreSQL WHY ERROR: invalid byte sequence for encoding “UTF8”》
《PostgreSQL 转义、UNICODE、与SQL注入》
《PostgreSQL 和 Greenplum pgcrypto 加解密bytea处理差异》
https://www.bbsmax.com/A/kjdwxDy6zN/
http://www.codeweblog.com/sqlserve%E5%88%B0pg%E8%BF%81%E7%A7%BB%E9%94%99%E8%AF%AF-%E6%97%A0%E6%95%88%E7%9A%84%E7%BC%96%E7%A0%81%E5%BA%8F%E5%88%97-utf8-0x00/
如果是从ORACLE迁移过来，可以使用Oracle的empty_clob()处理一下0x00字符。