作者
digoal
日期
2018-01-28
标签
PostgreSQL , Greenplum , 0x00 , 空字符 , NUL , 数据清洗 , 规则 , 过滤 , 非法字符
背景
如果输入的数据不合法，那么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字符。
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
