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

Insert text with single quotes in PostgreSQL

提问by MAHI

I have a table test(id,name).

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

I need to insert values like: user's log, 'my user', customer's.

我需要插入如下值： 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');

I am getting an error if I run any of the above statements.

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

If there is any method to do this correctly please share. I don't want any prepared statements.

如果有任何方法可以正确执行此操作，请分享。我不想要任何准备好的陈述。

Is it possible using sql escaping mechanism?

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

回答by Erwin Brandstetter

String literals

字符串文字

Escaping single quotes 'by doubling them up -> ''is the standard way and works of course:

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

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

In old versions or if you still run with standard_conforming_strings = offor, generally, if you prepend your string with Eto declare Posix escape string syntax, you can also escape with the backslash \:

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

E'user\'s log'

Backslash itself is escaped with another backslash. But that's generally not preferable.
If you have to deal with many single quotes or multiple layers of escaping, you can avoid quoting hell in PostgreSQL with dollar-quoted strings:

反斜杠本身用另一个反斜杠转义。但这通常不是可取的。
如果您必须处理许多单引号或多层转义，则可以避免在 PostgreSQL 中用美元引用的字符串引用地狱：

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

To further avoid confusion among dollar-quotes, add a unique token to each pair:

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

$token$escape ' with ''$token$

Which can be nested any number of levels:

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

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

Pay attention if the $character should have special meaning in your client software. You may have to escape it in addition. This is not the case with standard PostgreSQL clients like psql or pgAdmin.

请注意该$字符在您的客户端软件中是否应具有特殊含义。此外，您可能还需要逃避它。标准 PostgreSQL 客户端（如 psql 或 pgAdmin）不是这种情况。

That is all very useful for writing plpgsql functions or ad-hoc SQL commands. It cannot alleviate the need to use prepared statements or some other method to safeguard against SQL injection in your application when user input is possible, though. @Craig's answerhas more on that. More details:

这对于编写 plpgsql 函数或临时 SQL 命令非常有用。但是，当用户输入可能时，它不能减少使用准备好的语句或其他一些方法来防止应用程序中的 SQL 注入的需要。@Craig 的回答有更多相关内容。更多细节：

• SQL injection in Postgres functions vs prepared queries

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

Values inside Postgres

Postgres 中的值

When dealing with values inside the database, there are a couple of useful functions to quote strings properly:

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

• quote_literal()or quote_nullable()- the latter outputs the string NULLfor null input. (There is also quote_ident()to double-quotestrings where needed to get valid SQL identifiers.)
• format()with the format specifier %Lis equivalent to quote_nullable().
  Like: format('%L', string_var)
• concat()or concat_ws()are typically no good as those do notescape nested single quotes and backslashes.

• quote_literal()或quote_nullable()- 后者输出NULL空输入的字符串。（在需要获取有效 SQL标识符的地方也quote_ident()需要双引号字符串。）
• format()与格式说明符%L等效于quote_nullable().
  喜欢：format('%L', string_var)
• concat()或者concat_ws()通常不好，因为那些不会转义嵌套的单引号和反斜杠。

回答by Craig Ringer

This is so many worlds of bad, because your question implies that you probably have gaping SQL injectionholes in your application.

这是一个糟糕的世界，因为您的问题意味着您的应用程序中可能存在巨大的SQL 注入漏洞。

You should be using parameterized statements. For Java, use PreparedStatementwith placeholders. You say you don't want to use parameterised statements, but you don't explain why, and frankly it has to be a very good reason not to use them because they're the simplest, safest way to fix the problem you are trying to solve.

您应该使用参数化语句。对于 Java，请PreparedStatement与占位符一起使用。你说你不想使用参数化语句，但你没有解释为什么，坦率地说，这必须是一个很好的理由不使用它们，因为它们是解决你正在尝试的问题的最简单、最安全的方法来解决。

See Preventing SQL Injection in Java. Don't be Bobby's next victim.

请参阅在 Java 中防止 SQL 注入。不要成为鲍比的下一个受害者。

There is no public function in PgJDBC for string quoting and escaping. That's partly because it might make it seem like a good idea.

PgJDBC 中没有用于字符串引用和转义的公共函数。这部分是因为它可能使它看起来像一个好主意。

There arebuilt-in quoting functions quote_literaland quote_identin PostgreSQL, but they are for PL/PgSQLfunctions that use EXECUTE. These days quote_literalis mostly obsoleted by EXECUTE ... USING, which is the parameterised version, because it's saferand easier. You cannot use them for the purpose you explain here, because they're server-side functions.

有是内置的报价功能quote_literal和quote_identPostgreSQL中，但它们对于PL/PgSQL函数使用EXECUTE。这些天quote_literal大多被 淘汰EXECUTE ... USING，这是参数化版本，因为它更安全和更容易。您不能将它们用于您在此处解释的目的，因为它们是服务器端功能。

---

---

Imagine what happens if you get the value ');DROP SCHEMA public;--from a malicious user. You'd produce:

想象一下，如果您');DROP SCHEMA public;--从恶意用户那里获得价值会发生什么。你会产生：

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

which breaks down to two statements and a comment that gets ignored:

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

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

Whoops, there goes your database.

哎呀，有你的数据库。

回答by Claudix

According to PostgreSQL documentation (4.1.2.1. String Constants):

根据PostgreSQL 文档（4.1.2.1. 字符串常量）：

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

See also the standard_conforming_stringsparameter, which controls whether escaping with backslashes works.

另请参阅standard_conforming_strings参数，该参数控制是否使用反斜杠进行转义。

回答by Hunter

In postgresql if you want to insert values with 'in it then for this you have to give extra '

在 postgresql 中，如果你想在其中插入值，'那么为此你必须提供额外的'

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

回答by Slava Struminski

you can use the postrgesql chr(int) function:

您可以使用 postrgesql chr(int) 函数：

insert into test values (2,'|| chr(39)||'my users'||chr(39)||');

回答by hatenine

If you need to get the work done inside Pg:

如果您需要在 Pg 中完成工作：

to_json(value)

to_json(value)

https://www.postgresql.org/docs/9.3/static/functions-json.html#FUNCTIONS-JSON-TABLE

https://www.postgresql.org/docs/9.3/static/functions-json.html#FUNCTIONS-JSON-TABLE

回答by Rushi The Sharma

select concat('''','abc','''')