我有一张桌子 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)不是反引号 `
,它在 Postgres 中没有特殊用途(与某些其他 RDBMS 不同),也不是用于标识符的双引号 "
。
在旧版本中,或者如果您仍然使用 standard_conforming_strings = off
运行,或者通常,如果您在字符串前面加上 E
来声明 Posix 转义字符串语法,您也可以使用反斜杠 \
进行转义:
E'user\'s log'
反斜杠本身用另一个反斜杠转义。但这通常不是可取的。
如果您必须处理许多单引号或多层转义,您可以避免在 PostgreSQL 中使用 dollar-quoted strings 引用地狱:
'escape '' with '''''
$$escape ' with ''$$
为了进一步避免美元报价之间的混淆,请为每一对添加一个唯一的标记:
$token$escape ' with ''$token$
可以嵌套任意数量的级别:
$token2$Inner string: $token1$escape ' with ''$token1$ is nested$token2$
请注意 $
字符在您的客户端软件中是否应具有特殊含义。此外,您可能还必须逃避它。对于像 psql 或 pgAdmin 这样的标准 PostgreSQL 客户端,情况并非如此。
这对于编写 plpgsql 函数或 ad-hoc SQL 命令非常有用。但是,当用户可以输入时,它不能减轻使用准备好的语句或其他方法来防止应用程序中的 SQL 注入的需要。 @Craig's answer 对此有更多说明。更多细节:
Postgres 函数中的 SQL 注入与准备好的查询
Postgres 中的值
在处理数据库中的值时,有几个有用的函数可以正确引用字符串:
quote_literal() 或 quote_nullable() - 后者为空输入输出字符串 NULL。还有 quote_ident() 在需要获取有效 SQL 标识符的地方用双引号引起来。
带有格式说明符 %L 的 format() 等价于 quote_nullable()。像:format('%L', string_var)
concat() 或 concat_ws() 通常不适用于此目的,因为它们不会转义嵌套的单引号和反斜杠。
根据PostgreSQL documentation (4.1.2.1. String Constants):
要在字符串常量中包含单引号字符,请写入两个相邻的单引号,例如'Dianne''s horse'。
另请参阅 standard_conforming_strings
参数,该参数控制是否使用反斜杠进行转义。
这是一个糟糕的世界,因为您的问题暗示您的应用程序中可能存在巨大的 SQL injection 漏洞。
您应该使用参数化语句。对于 Java,请使用 PreparedStatement
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;
--');
哎呀,你的数据库到了。
database is accessed by java
所以这确实直接解决了这个问题。让来到这里的人意识到潜在的危险也非常重要,特别是考虑到 SQL 注入是软件漏洞的第一大原因。一旦意识到问题,人们就可以就何时无关紧要做出明智的决定,例如您的引导用例。
在 postgresql 中,如果您想插入带有 '
的值,那么为此您必须提供额外的 '
insert into test values (1,'user''s log');
insert into test values (2,'''my users''');
insert into test values (3,'customer''s');
您可以使用 postrgesql chr(int) 函数:
insert into test values (2,'|| chr(39)||'my users'||chr(39)||');
如果您需要在 Pg 中完成工作:
to_json(value)
https://www.postgresql.org/docs/9.3/static/functions-json.html#FUNCTIONS-JSON-TABLE
format()
、quote_literal()
或 quote_nullable()
来转义引号。请参阅:stackoverflow.com/a/25143945/939860
当我使用 Python 向 PostgreSQL 中插入值时,我也遇到了一个问题:列“xxx”不存在。
我在 wiki.postgresql 中找到原因:
PostgreSQL 对此只使用单引号(即 WHERE name = 'John')。双引号用于引用系统标识符;字段名、表名等(即 WHERE “姓氏” = 'Smith')。 MySQL 使用 `(重音符号或反引号)来引用系统标识符,这显然是非标准的。
这意味着 PostgreSQL 只能对字段名、表名等使用单引号。因此不能在值中使用单引号。
我的情况是:我想在 PostgreSQL 中插入值“它是 sb 的 adj 和它的 sb 的 adj 的差异”。
我如何解决这个问题:
我将 '
替换为 ’
,并将 "
替换为 '
。 因为 PostgreSQL 值不支持双引号。
所以我认为您可以使用以下代码插入值:
insert into test values (1,'user’s log');
insert into test values (2,'my users');
insert into test values (3,'customer’s');
'
。在 Postgres 和标准 SQL 中都没有。您可以轻松地将单引号嵌入到字符串常量 user''s log'
中,这就是 SQL 标准中定义的方式。
不定期副业成功案例分享
E
明确请求该行为。