在PostgreSQL中插入带单引号的文本 [英] Insert text with single quotes in PostgreSQL

问题描述

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

我需要插入类似user's log，'my user'，customer's的值.

I need to insert values like: 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.

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

Is it possible using sql escaping mechanism?

推荐答案

字符串文字

通过加倍将单引号'转义-> ''是标准方法，当然可以:

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 = off or, generally, if you prepend your string with E to declare Posix escape string syntax, you can also escape with the backslash \:

E'user\'s log'

反斜杠本身被另一个反斜杠转义.但这通常不是可取的.
如果必须处理多个单引号或多个转义符，则可以避免在PostgreSQL中使用

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:

'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$

请注意，如果$字符在客户端软件中应具有特殊含义.您可能还必须逃脱它.标准PostgreSQL客户端(例如psql或pgAdmin)不是这种情况.

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.

这对于编写plpgsql函数或临时SQL命令非常有用.但是，当可以进行用户输入时，它不能减轻使用准备好的语句或其他方法来防止在应用程序中进行SQL注入的需要. @Craig的答案还有更多相关信息.详细信息:

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 answer has more on that. More details:

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

在处理数据库内部的值时，有几个有用的函数可以正确地引用字符串:

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

• quote_literal()或quote_nullable() -后者为空输入输出字符串NULL. (还有 quote_ident() 到双引号字符串，以获取有效的SQL 标识符.)
• format() ，其格式为说明符%L等效于quote_nullable().
  像:format('%L', string_var)
• concat() 或 concat_ws() 通常是不好的，因为 not 不能使嵌套的单引号和反斜杠转义.

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

这篇关于在PostgreSQL中插入带单引号的文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！