问题:在PostgreSQL中插入带单引号的文本

我有一张桌子test(id,name)。

我需要插入如下值:user's log、'my user'、customer's。

 insert into test values (1,'user's log');
 insert into test values (2,''my users'');
 insert into test values (3,'customer's');

如果我运行上述任何语句,我会收到错误消息。

如果有任何方法可以正确地做到这一点,请分享。我不想要任何准备好的陈述。

是否可以使用 sql 转义机制?

解答

字符串字面量

通过将它们加倍来转义单引号'→''是标准的方法,并且当然可以:

'user's log'     -- incorrect syntax (unbalanced quote)
'user''s log'

纯单引号(ASCII / UTF-8 代码 39),请注意,不是反引号``` ``, which have no special purpose in Postgres (unlike certain other RDBMS) and not double-quotes ",用于标识符。

在旧版本中,或者如果您仍然使用standard_conforming_strings = off运行,或者通常,如果您在字符串前面加上E以声明 Posix 转义字符串语法,您也可以使用反斜杠\进行转义:

E'user\'s log'

反斜杠本身用另一个反斜杠转义。但这通常不是可取的。

如果您必须处理许多单引号或多层转义,您可以避免在 PostgreSQL 中使用美元引号字符串引用地狱:

'escape '' with '''''
$$escape ' with ''$$

为了进一步避免美元报价之间的混淆,请为每对添加一个唯一的 token:

$token$escape ' with ''$token$

可以嵌套任意数量的级别:

$token2$Inner string: $token1$escape ' with ''$token1$ is nested$token2$

注意$字符在您的客户端软件中是否应该具有特殊含义。此外,您可能还必须逃避它。对于像 psql 或 pgAdmin 这样的标准 PostgreSQL 客户端,情况并非如此。

这对于编写 plpgsql 函数或 ad-hoc SQL 命令非常有用。但是,当用户可以输入时,它不能减轻使用准备好的语句或其他方法来防止应用程序中的 SQL 注入的需要。@Craig 的回答对此有更多的了解。更多细节:

• Postgres 函数中的 SQL 注入与准备好的查询

Postgres 中的值

在处理数据库中的值时,有几个有用的函数可以正确引用字符串:

• quote_literal()或quote_nullable()- 后者输出字符串NULL作为空输入。

在需要获取有效 SQL 标识符的地方,还有quote_ident()到 double-quote 字符串。

• format()与格式说明符%L等价于quote_nullable()。

喜欢:format('%L', string_var)

• concat()或concat_ws()通常不适合此目的,因为它们不会_不_转义嵌套的单引号和反斜杠。