标签

PostgreSQL、json、字符串转义、unicode、SQL 注入、反斜杠_quote、转义_string_warning、标准_conforming_strings

背景

通过这篇文章,你可以了解到:

1\。如何在字符串中输入特殊字符,例如回车。

2\。如何在字符串中输入单引号。

3\。什么是转义字符?用法。

4\。反斜杠是 SQL 标准中的转义字符吗? PostgreSQL 如何使用反斜杠转义?写作格式如何?

5\。为什么转义单引号会有SQL注入的风险?数据库的哪些参数可以控制这种风险?可以警告哪些参数?

6\。如何以 UNICODE 的形式输入字符串。

7\。以UNICODE格式输入,支持所有字符集?当前支持哪些字符集以及支持哪些 CODE 范围?

8\。在 Greenplum 的 JSON 中输入 UNICODE 值有什么问题? PostgreSQL中是否有同样的问题?

逃逸配置

三个配置控件转义如下:

1\。 (与SQL注入有关)是否允许使用反斜杠转义单引号?

backslash_quote u003d on 允许,off 不允许,safe_encoding (仅当client_encoding 不允许反斜杠\ 出现在多字节字符中(大多数字符集都是单字节表示),则允许转义单引号.)

为什么要控制转义单引号?

因为这样可能会引入SQL注入的风险,比如最终客户放在最后,单引号可以转义,本来应该正常打的字符串终止符就不存在了。

反斜杠引用(枚举)

这控制引号是否可以在字符串文字中由 ' 表示。

表示引号的首选 SQL 标准方式是双引号 (''),但 PostgreSQL 历来也接受 '。

但是,使用 ' 会带来安全风险,因为在某些客户端字符集编码中,存在最后一个字节在数字上等同于 ASCII \ 的多字节字符。

如果客户端代码没有正确转义,则可能发生 SQL 注入攻击。

通过使服务器拒绝其中引号似乎被反斜杠转义的查询,可以防止这种风险。

backslash_quote 的允许值为 on(始终允许 ')、off(始终拒绝)和 safe_encoding(仅当客户端编码不允许多字节字符中的 ASCII \ 时才允许)。

safe_encoding 是默认设置。

请注意,在符合标准的字符串文字中,\ 只是表示 \。此参数仅影响对不符合标准的文字的处理,包括转义字符串语法 (E'...')。

请注意,反斜杠_quote 的配置仅在standard_conforming_stringsu003doff 或使用E'' 时有效。

否则,当standard_conforming_stringsu003don 时,将被视为普通字符串。

示例 1:当standard_conforming_stringsu003don 时,被视为普通字符串

postgresu003d# 设置standard_conforming_stringsu003don;

设置

postgresu003d# 选择'';

?列?

---

\

(1 行)

示例 2:反斜杠_quote 的配置在standard_conforming_stringsu003doff 或使用E'' 表示法时有效。

zoz100057`

postgresu003d# 设置反斜杠引号 u003d 关闭; -- 不允许逃跑

设置

postgresu003d#选择E'';

';

错误:22P06:在字符串文字中不安全地使用 '

第 1 行:选择 E'';

^

提示:使用 '' 在字符串中写引号。 ' 在仅限客户端的编码中是不安全的。

位置:core_yylex,scan.l:641

postgresu003d# 设置反斜杠引号 u003d 关闭; -- 不允许逃跑

设置

postgresu003d# 设置 standard_conforming_stringsu003doff;

设置

postgresu003d# 选择'';

';

错误:22P06:在字符串文字中不安全地使用 '

第 1 行:选择“\”;

^

提示:使用 '' 在字符串中写引号。 ' 在仅限客户端的编码中是不安全的。

位置:core_yylex,scan.l:641

postgresu003d# 设置反斜杠引用 u003d on; -- 允许逃跑'

设置

postgresu003d# 选择'';

';

警告:22P06:在字符串文字中非标准使用 '

第 1 行:选择“\”;

^

提示:使用 '' 在字符串中写入引号,或使用转义字符串语法 (E'...')。

位置:check_string_escape_warning,scan.l:1483

?列?

---

'; +

(1 行)

postgresu003d#setbackslash_quote u003d safe_encoding; -- 当client_encoding不允许反斜杠时````` to appear in multi-byte characters (most character sets ````are single-byte representations'), it allows the use of ```转义单引号'。

设置

postgresu003d# 显示客户端编码;

客户端编码

---

UTF8

(1 行)

postgresu003d# 选择'';

';

警告:22P06:在字符串文字中非标准使用 '

第 1 行:选择“\”;

^

提示:使用 '' 在字符串中写入引号,或使用转义字符串语法 (E'...')。

位置:check_string_escape_warning,scan.l:1483

?列?

---

'; +

(1 行)

2\。是否在standard_conforming_stringsu003doff 时输出警告并在''string 中包含反斜杠。

因为在 SQL 标准中,字符串中的反斜杠不是转义字符,而是普通字符。

如果您想逃脱,请使用 E'。

escape_string_warning(布尔值)

开启时,如果反斜杠 () 出现在普通字符串文字('...' 语法)中并且 standard_conforming_strings 关闭,则会发出警告。

默认为开启。

希望使用反斜杠作为转义的应用程序应修改为使用转义字符串语法 (E'...'),

因为普通字符串的默认行为现在是按照 SQL 标准将反斜杠视为普通字符。

可以启用此变量来帮助定位需要更改的代码。

示例 1

zoz100069`

postgresu003d# 设置 escape_string_warningu003don;

设置

postgresu003d# 设置 standard_conforming_stringsu003doff;

设置

postgresu003d# 选择'';

';

WARNING: 22P06: nonstandard use of ' in a string literal -- 收到警告消息

第 1 行:选择“\”;

^

提示:使用 '' 在字符串中写入引号,或使用转义字符串语法 (E'...')。

位置:check_string_escape_warning,scan.l:1483

?列?

---

'; +

(1 行)

示例 2

postgresu003d# 设置 escape_string_warningu003don;

设置

postgresu003d# 设置 standard_conforming_stringsu003doff;

设置

postgresu003d#选择E';; -- 使用 E',作为转义字符

';

?列?

---

'; +

(1 行)

3\。告诉数据库''字符串中的反斜杠是否是普通字符。

standard_conforming_strings u003d on 反斜杠用作普通字符(标准 SQL 用法),off 反斜杠用作转义字符。

standard_conforming_strings(布尔值)

这控制普通字符串文字 ('...') 是否按照 SQL 标准中的规定按字面意思对待反斜杠。

从 PostgreSQL 9.1 开始,默认为开启(之前的版本默认为关闭)。

应用程序可以检查此参数以确定如何处理字符串文字。

此参数的存在也可以被视为支持转义字符串语法 (E'...') 的指示。

如果应用程序希望将反斜杠视为转义字符,则应使用转义字符串语法(第 4.1.2.2 节)。

如果standard_conforming_stringsu003don,那么使用转义呢?采样 E'' 就足够了

postgresu003d# 设置standard_conforming_stringsu003don;

设置

postgresu003d#选择';'; - SQL的标准写作

?列?

---

\

(1 行)

postgresu003d#选择E';'; -- 转义写作,此时作为转义字符

?列?

---

'

(1 行)

如何音译

1\。 standard_conforming_stringsu003don(SQL标准),那么字符串中的反斜杠''就是一个普通字符。使用 E'' 表示法,反斜杠是转义字符。

2\。 Standard_conforming_strings u003d off(非SQL标准),那么字符串中的反斜杠''是转义字符。

C语言风格字符串

反斜杠转义序列

解释

\b

退格

\F

换页

\n

新队

\r

回车

\t

标签

\o, \oo, \ooo (o u003d 0 - 7)

八进制字节值

\xh, \xhh (h u003d 0 - 9, A - F)

十六进制字节值

\uxxxx, \Uxxxxxxxx (x u003d 0 - 9, A - F)

16 位或 32 位十六进制 Unicode 字符值

\

反斜杠

'

单引号

unicode输入

如何输入 unicode 字符串?

用户可以直接向数据库输入UNICODE编码,省去编码转换过程,但用户必须保证编码与数据库服务器编码一致,否则可能出现编码溢出或乱码问题。一旦保存,就和直接存储字符串一样。

unicode输入格式1

当为转义字符配置反斜杠时,像这样输入 UNICODE

(注意这种输入格式要求反斜杠必须是转义字符,参考上一节,如果反斜杠变成转义字符)

\uxxxx, \Uxxxxxxxx (x u003d 0 - 9, A - F)

一个小写字母

16 位或 32 位十六进制 Unicode 字符值

例子

zoz100077`

postgresu003d#设置standard_conforming_stringsu003don;

设置

postgresu003d#选择00f7'; --_ 是转义字符

?列?

---

÷

(1 行)

postgresu003d#选择'00f7'; -- 是一个普通字符,不是 unicode,而是一个字符串。

?列?

---

\u00f7

(1 行)

postgresu003d# 设置standard_conforming_strings u003doff; -- 是一个转义字符

设置

postgresu003d# 选择'\u00f7';

警告:22P06:在字符串文字中非标准使用转义

第 1 行:选择“\u00f7”;

^

提示:使用转义字符串语法进行转义,例如 E'

'。

位置:check_escape_warning,scan.l:1508

?列?

---

÷

(1 行)

postgresu003d# 创建表 u(id int, info text);

创建表

postgresu003d#选择E'\u00f0';

?列?

---

电子

(1 行)

postgresu003d# 插入 u 值 (1, 'e');

插入 0 1

postgresu003d# 插入 u 值 (2, E'\u00f0');

插入 0 1

postgresu003d# select * from u;

身份证 |信息

-+--------

1 | e

2 | e

(2 行)

目前只有UTF8字符集允许输入大于007f的unicode,而其他字符集只能输入ascii范围内的unicode。

postgresu003d#\l

数据库列表

姓名 |所有者 |编码 |整理 |类型 |访问权限

--------+----------+------------+-- -------+------------+-----------

贡献回归 |后勤 | UTF8 | C | C |

分贝 |后勤 | SQL_ASCII | C | C |

数据库1 |后勤 | EUC_CN | C | C |

postgres |后勤 | UTF8 | C | C |

模板0 |后勤 | UTF8 | C | C | u003dc/postgres +

| | | | | postgresu003dCTc/postgres

模板1 |后勤 | UTF8 | C | C | u003dc/postgres +

| | | | | postgresu003dCTc/postgres

测试 |后勤 | UTF8 | en_US.UTF8 | en_US.UTF8 |

测试01 |后勤 | UTF8 | C | C |

测试02 |后勤 | UTF8 | zh_CN.UTF8 | zh_CN.UTF8 |

(9 行)

postgresu003d#\C db1-EUC_CN 编码

您现在以用户“postgres”的身份连接到数据库“db1”。

db1u003d#选择U&'fe';

错误:42601:当服务器编码在“fe'”处或附近不是 UTF8 时,Unicode 转义值不能用于 007F 以上的代码点值

LINE 1:选择U&'fe';

^

位置:scanner_yyerror,scan.l:1086

db1u003d#\C Postgres - UTF8编码,允许输入大于007F的unicode

您现在以用户“postgres”的身份连接到数据库“postgres”。

postgresu003d#选择U&'fe';

?列?

---

吨

(1 行)

细节

您创建的字节序列(尤其是在使用八进制或十六进制转义时)在服务器字符集编码中构成有效字符是您的责任。

当服务器编码为 UTF-8 时,应使用 Unicode 转义或替代 Unicode 转义语法,如第 4.1.2.3 节中所述。

(另一种方法是手动进行 UTF-8 编码并写出字节,这将非常麻烦。)

只有当服务器编码为 UTF8 时,Unicode 转义语法才能完全发挥作用。

使用其他服务器编码时,只能指定 ASCII 范围内的代码点(最高为 \u007F)。

4 位和 8 位形式都可用于指定 UTF-16 代理对来组成代码点大于 U+FFFF 的字符,尽管 8 位形式的可用性在技术上使得这没有必要。

(当服务器编码为 UTF8 时使用代理对时,它们首先组合成单个代码点,然后以 UTF-8 编码。)

注意

如果配置参数 standard_conforming_strings 关闭,那么 PostgreSQL 会识别常规和转义字符串常量中的反斜杠转义。

但是,从 PostgreSQL 9.1 开始,默认值为 on,这意味着反斜杠转义仅在转义字符串常量中被识别。

这种行为更符合标准,但可能会破坏依赖历史行为的应用程序,其中反斜杠转义总是被识别。

作为一种解决方法,您可以将此参数设置为关闭,但最好不要使用反斜杠转义。

如果您需要使用反斜杠转义来表示特殊字符,请使用 E 编写字符串常量。

除了standard_conforming_strings,配置参数escape_string_warning 和backslash_quote 控制字符串常量中反斜杠的处理。

代码为零的字符不能在字符串常量中。

unicode输入格式2

格式 2 相反,转义字符时反斜杠不是 unicode。

此格式支持 2 或 3 个字节的 UNICODE。

格式如下。

U&'4[0-F]'

U &'+6 [0-F]'

你不区分大小写

字符串中其他位置的字符被识别为标准字符,而被识别为 UNICODE 的转义字符。后面必须跟四个 [0-F] 或 + 六个 [0-F]。如果要输入,请输入\。

如果要替换转义字符,请使用 UECAPE 语法。

示例 1

U&'d1t+000061'

U&'!#B#E#D'

U&'d!0061t!+000061' UESCAPE '!' -- 将转义字符改为!

U&'' -- 输入两个转义字符,输出转义字符本身

示例 2,不能使用转义格式

postgresu003d# 设置standard_conforming_strings u003doff; -- 转义字符模式,''符号中是转义字符,不是普通字符

设置

postgresu003d#选择U&'d1t+000061';

错误:0A000:不安全地使用带有 Unicode 转义的字符串常量

LINE 1: 选择 U&'d1t+000061';

^

详细信息:当 standard_conforming_strings 关闭时,不能使用带有 Unicode 转义的字符串常量。

位置:core_yylex,scan.l:534

postgresu003d#设置standard_conforming_stringsu003don; -- 非转义字符模式,\in''是普通字符,此时使用u&''是正常的。

设置

postgresu003d#选择U&'d1t+000061';

?列?

---

数据

(1 行)

示例 3

postgresu003d#选择U&'d1t+000061';

?列?

---

数据

(1 行)

postgresu003d#选择U&'!#B#E#D';

?列?

---

一头大象

(1 行)

postgresu003d# 选择 U&'d!0061t!+000061' UESCAPE '!';

?列?

---

数据

(1 行)

postgresu003d#选择U&'';

?列?

---

\

(1 行)

postgresu003d#选择U&'\+000061';

?列?

---

(1 行)

postgresu003d# 选择U&'!+000061' UESCAPE '!';

?列?

---

\一个

(1 行)

细节

PostgreSQL 还支持另一种类型的字符串转义语法,允许通过代码点指定任意 Unicode 字符。

Unicode 转义字符串常量在开始引号之前以 U&(大写或小写字母 U 后跟 & 号)开头,中间没有任何空格,例如 U&'foo'。

(请注意,这会与运算符 & 产生歧义。在运算符周围使用空格以避免此问题。)

在引号内,可以通过写反斜杠后跟四位十六进制代码点编号以转义形式指定 Unicode 字符

或者反斜杠后跟加号,后跟六位十六进制代码点编号。

例如,字符串 'data' 可以写成

U&'d1t+000061'

下面的例子用西里尔字母写出俄语单词“slon”(大象):

U&'!#B#E#D'

如果需要与反斜杠不同的转义字符,可以在字符串后面使用 UECAPE 子句指定,例如:

U&'d!0061t!+000061' UESCAPE '!'

转义字符可以是除十六进制数字、加号、单引号、双引号或空白字符之外的任何单个字符。

Unicode 转义语法仅在服务器编码为 UTF8 时有效。当使用其他服务器编码时,只能指定 ASCII 范围(最多 F)中的代码点。

4位和6位形式都可以用来指定UTF-16代理对组成码点大于U+FFFF的字符,

尽管 6 位数表格的可用性在技术上使这变得不必要。

(当服务器编码为 UTF8 时使用代理对时,它们首先组合成单个代码点,然后以 UTF-8 编码。)

此外,字符串常量的 Unicode 转义语法仅在打开配置参数 standard_conforming_strings 时有效。

这是因为否则这种语法可能会使解析 SQL 语句的客户端感到困惑,从而导致 SQL 注入和类似的安全问题。

如果参数设置为 off,此语法将被拒绝并显示错误消息。

要在字符串中包含转义字符,请写两次。

字符串写入

1\。单引号

例子

'字符串内容'

2\。美元符号

例子

$字符串内容$$

greenplum JSON中的unicode问题

在greenplum的json中存储unicode原生字符串时,使用->提取可能会报错。 (估计是转换过程引起的异常)

创建测试表

祖兹 100093-

postgresu003d# 创建表 tbl(id int, info json);

注意:表没有“DISTRIBUTED BY”子句——使用名为“id”的列作为该表的Greenplum 数据库数据分布键。

提示:“DISTRIBUTED BY”子句确定数据的分布。确保选择的列是最佳数据分布键,以最大限度地减少偏差。

创建表

目前是转义模式,也就是说UNICODE会提前转换存储。

postgresu003d# 设置standard_conforming_strings u003doff; -- 设置为转义模式,即使用\作为转义字符,也可以正常识别和转换UNICODE格式。

插入,UNICODE转换为最终字符串插入,提取正常

祖兹100097

postgresu003d#插入TBL(id,info)值(1,'{activitytitle":"Hello People's Republic of China","giftname":"_______________

警告:字符串文字中转义的非标准使用

第 1 行:插入 tbl(id,info) 值 (1,'{"activitytitle":"How are you doing?...

^

提示:使用转义字符串语法进行转义,例如 E'

'。

插入 0 1

postgresu003d# select info->'activitytitle' from tbl;

?列?

---

《中华人民共和国你好》

(1 行)

postgresu003d# 从 tbl 中选择 *;

身份证 |信息

----+-------------------------------------------- ----------------------------------

1 | {"activitytitle":"你好中华人民共和国","giftname":"摇个红包","ruledesc":null}

(1 行)

使用非转义模式,即按原样插入UNICODE字符串

此时,提取发生异常。

postgresu003d#设置standard_conforming_stringsu003don; -- 在非转义模式下,会作为普通字符插入,所以不会被识别为unicode模式,插入原始字符。

设置

postgresu003d#插入TBL(id,info)值(1,'{activitytitle":"Hello People's Republic of China","giftname":"_______________

插入 0 1

postgresu003d# 从 tbl 中选择 *;

身份证 |信息

----+-------------------------------------------- -------------------------------------------------- ------

1 | {"activitytitle":"你好中华人民共和国","giftname":"摇个红包","ruledesc":null}

1 | {"activitytitle":"你好中华人民共和国","giftname":"\u6447\u4E00\u6447\u7EA2\u5305","ruledesc":null}

(2 行)

-- 提取unicode对应字段失败

postgresu003d# \set VERBOSITY 详细

postgresu003d# select info->'activitytitle' from tbl;

错误:22021:用于编码“UTF8”的无效字节序列:0xe6 (seg0 slice1 digoal:39999 pidu003d22528)

位置:cdbdisp_finishCommand,cdbdisp.c:1326

postgresu003d# select info->'giftname' from tbl;

错误:22021:用于编码“UTF8”的无效字节序列:0xe6 (seg0 slice1 digoal:39999 pidu003d27053)

位置:cdbdisp_finishCommand,cdbdisp.c:1326

解决方案

使用转义模式,UNICODE 被转义并存储在 JSON 中。

普通字符串不会有这样的问题,只是Greenplum的JSON类型。

PG 9.4的JSON就没有这个问题了,如下。

postgresu003d# 创建表 tbl(id int, info json);

创建表

postgresu003d#设置standard_conforming_stringsu003don;

设置

postgresu003d#插入TBL(id,info)值(1,'{activitytitle":"Hello People's Republic of China","giftname":"_______________

插入 0 1

postgresu003d# 从 tbl 中选择 *;

身份证 |信息

----+-------------------------------------------- -------------------------------------------------- ------

1 | {"activitytitle":"你好中华人民共和国","giftname":"\u6447\u4E00\u6447\u7EA2\u5305","ruledesc":null}

(1 行)

postgresu003d# select info->'activitytitle' from tbl;

?列?

---

《中华人民共和国你好》

(1 行)

postgresu003d# select info->'giftname' from tbl;

?列?

---

"\u6447\u4E00\u6447\u7EA2\u5305"

(1 行)

总结

1\。如何在字符串中输入特殊字符,例如回车。

1.通过转义输入特殊字符

E'?'

2.使用UNIDOCE方法输入特殊字符,standard_conforming_stringsu003doff, escape_string_warningu003doff

U&'\xxxx'

2\。如何在字符串中输入单引号。

4 以一种或另一种方式在字符串中输入单引号

U&'????'

E'''

$$'$$

''''

3\。什么是转义字符? PostgreSQL 如何使用反斜杠转义?写作格式如何?

反斜杠作为转义字符,类似于 C 语言风格,可以输入特殊字符。

E'?'

也许

标准\符合\字符串u003d关闭,escape_string\警告u003d关闭

'?'

4\。反斜杠是 SQL 标准中的转义字符吗?

SQL 在标准中,反斜杠是常用字符

standard_conforming_stringsu003don

'' 中\是常用字符。

5\。为什么转义单引号会有SQL注入的风险?数据库的哪些参数可以控制这种风险?可以警告哪些参数?

转义字符可以将单引号转换为普通字符,这可能导致正常的字符串终止未完成。

参数允许您控制是否允许转义单引号

反斜杠_quote u003d on 允许,off 不允许,safe_encoding(仅当client_encoding 不允许反斜杠```'to appear in multi-byte characters (most character sets `````are `single-byte') is allowed to use ``````转义单引号')。

6\。如何以 UNICODE 的形式输入字符串。

6种写作

U&'\xxxx'

U&'+xxxxxx'

E'\uxxxx'

E'\Uxxxxxxxx'

standard_conforming_stringsu003doff

'\uxxxx'

'\Uxxxxxxxx'

7\。以UNICODE格式输入,支持所有字符集?当前支持哪些字符集以及支持哪些 CODE 范围?

数据库编码为UTF8时,支持除零字符以外的所有合法字符

数据库编码<>UTF8时,只支持ASCII字符,即CODE<007F。

8\。在 Greenplum 的 JSON 中输入 UNICODE 值有什么问题? PostgreSQL中是否有同样的问题?

目前如果在青梅中使用json,不建议在value中存储UNICODE,请保存目标字符,否则会出错。

为了存储目标字符,将 UNICODE 转换为与字符集对应的字符串。

四种实现方式

当输入为'???'时,设置standard_conforming_stringsu003doff。

或者使用以下格式

欧盟????'

也许

你&'\xxxx'

也许

U&'+xxxxxx'

PostgreSQL 没问题。

参考资源

https://www.postgresql.org/docs/9.6/static/runtime-config-compatible.html#RUNTIME-CONFIG-COMPATIBLE-VERSION

https://www.postgresql.org/docs/9.6/static/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-UESCAPE