在PostgreSQL中将$$放在美元报价的字符串中

问题描述

我在Postgres中有一个函数：

I have a function in Postgres:

CREATE OR REPLACE FUNCTION upsert(sql_insert text, sql_update text) 
RETURNS integer AS
$BODY$
BEGIN
 EXECUTE sql_insert;
 RETURN 1;
EXCEPTION WHEN unique_violation THEN
 EXECUTE sql_update; 
 RETURN 2;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION upsert(text, text) OWNER TO dce;

我通常使用此查询来调用该函数：

I usually use this query to call that function:

select upsert(
  $$INSERT INTO zz(a, b) VALUES (66, 'hahahaha')$$,
  $$UPDATE zz SET a=66, b='hahahaha' WHERE a=66$$
)

作品。不幸的是，我的查询字符串不能包含 $$ ，例如：

It works. Unfortunately, my query string cannot contain $$, like this:

select upsert(
  $$INSERT INTO zz(a, b) VALUES (66, 'ha$$hahaha')$$,
  $$UPDATE zz SET a=66, b='hahahaha' WHERE a=66$$
)

我已阅读 Postgres文档，但如何操作仍需要帮助。

I have read this Postgres documentation but still need assistance how to do it.

推荐答案

因此，请使用其他代替：

So use a different dollar-quote instead:

select upsert(
   $weird_string$INSERT INTO zz(a, b) VALUES (66, 'ha$$hahaha')$weird_string$,
   $weird_string$UPDATE zz SET a=66, b='hahahaha' WHERE a=66$weird_string$
   )

从理论上讲，

如果您是手工构建查询，只需检查 $ 字符串。
如果要通过变量构建查询，则可以使用。

If you are building the query by hand, just check for $ in the string.If you are building the query from variables, you could use quote_literal(querystring) instead.

自Postgres 9.1起，方便的函数。

Since Postgres 9.1, there is also the convenient format() function.

另外：我假设您知道这种形式的动态SQL极易受到SQL注入的攻击？这类任何东西都只能用于非常私人或非常安全的用途。

As an aside: I assume you are aware that this form of dynamic SQL is extremely vulnerable to SQL injection? Anything of the sort should be for very private or very secure use only.

这篇关于在PostgreSQL中将$$放在美元报价的字符串中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！