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

Question

我有一张桌子。

我需要插入如下值：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转义机制？

Answer 1

这是如此多的坏世界，因为您的问题意味着您的应用程序中可能有很多漏洞。

您应该使用参数化语句。对于Java，使用^{} with placeholders。你说你不想使用参数化语句，但你不解释为什么要使用参数化语句，坦率地说，这是一个不使用参数化语句的很好的理由，因为它们是解决你试图解决的问题的最简单、最安全的方法。

见Preventing SQL Injection in Java。别成为Bobby的下一个受害者。

PgJDBC中没有用于字符串引用和转义的公共函数。部分原因是这可能会让人觉得这是个好主意。

PostgreSQL中有内置的引用函数quote_literal和quote_ident，但它们用于使用EXECUTE的PL/PgSQL函数。如今quote_literal大部分被EXECUTE ... USING淘汰，这是参数化版本，因为它更安全，也更容易。您不能将它们用于此处解释的目的，因为它们是服务器端函数。

---

想象一下，如果从恶意用户获取值');DROP SCHEMA public;--，会发生什么情况。你会产生：

insert into test values (1,'');DROP SCHEMA public;--');

它分解为两个语句和一个被忽略的注释：

insert into test values (1,'');
DROP SCHEMA public;
--');

喔，你的数据库到了。

Answer 2

根据PostgreSQL documentation (4.1.2.1. String Constants)：

 To include a single-quote character within a string constant, write two 
 adjacent single quotes, e.g. 'Dianne''s horse'.

另请参见standard_conforming_strings参数，该参数控制使用反斜杠转义是否有效。

Answer 3

字符串文本

通过将单引号'加倍来转义->；''是标准的方法，当然有效：

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

在旧版本中，或者如果您仍然使用^{}运行，或者，通常情况下，如果您使用E预处理字符串以声明Posix转义字符串语法，则还可以使用反斜杠\转义：

E'user\'s log'

反斜杠本身用另一个反斜杠转义。但这通常并不可取。
如果需要处理多个单引号或多层转义，可以避免在PostgreSQL中使用dollar-quoted strings引用hell：

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

为了进一步避免美元报价之间的混淆，请在每对报价中添加一个唯一的标记：

$token$escape ' with ''$token$

可以嵌套任意数量的层：

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

请注意，如果$字符在客户端软件中有特殊含义。你可能还得逃离它。标准PostgreSQL客户机（如psql或pgAdmin）并非如此。

这对于编写plpgsql函数或特殊SQL命令非常有用。但是，当用户可以输入时，它不能减少使用准备好的语句或其他方法来防止应用程序中的SQL注入的需要。@Craig's answer有更多的信息。更多细节：

• SQL injection in Postgres functions vs prepared queries

Postgres中的值

在处理数据库中的值时，有两个有用的函数可以正确引用字符串：

• ^{} or ^{}-后者为空输入输出字符串NULL。
• ^{}与格式说明符%L等价于quote_nullable()。
  比如：format('%L', string_var)
• ^{} or ^{}通常不是很好，因为它们不会不转义嵌套的单引号和反斜杠。