Put $$ in dollar-quoted string in PostgreSQL

Question

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

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

Grosgrain

Answer 1

Use different dollar-quotes instead:

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

Each end has to match each start. The two pairs do not have to be distinct, but it's safest that way.

This still leaves a theoretical chance that the dollar-quote might be matched inside the string.

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

There is also the convenient format() function.

See:

• Insert text with single quotes in PostgreSQL

Aside: 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.

Debacle

i building the query by variables.So, i think i would use quote_literal(). it's very usefull. – Grosgrain

Just ran into a similar situation, a big 'Dankeschön' for your proposed solution(s).. – Daniels

You may need to quote $$ in a dynamic sql statement within a function. In that case, use adapted $ quoting for the function, such as $Q$ instead of $$. See postgresql.org/docs/11/plpgsql-development-tips.html – Duodecillion