[ACCEPTED]-Put $$ in dollar-quoted string in PostgreSQL-quoting

Accepted answer

Score: 12

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 13 each start. The two pairs do not have to 12 be distinct, but it's safest that way.

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

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

There is also the convenient 5 format() function.

See:

Insert text with single quotes in PostgreSQL

Aside: I assume you are aware 4 that this form of dynamic SQL is extremely 3 vulnerable to SQL injection? Anything of 2 the sort should be for very private or very 1 secure use only.

Source: stackoverflow.com

[ACCEPTED]-Put $$ in dollar-quoted string in PostgreSQL-quoting

How do I escape single quote in command line query of psql?

Python module to shellquote/unshellquote?

Bash: How to use operator parameter expansion ${parameter@operator}?

Bash doesn't parse quotes when converting a string to arguments

quoting constants in php: "this is a MY_CONSTANT

General string quoting for TCL

Postgresql - how to run a query on multiple tables with same schema

Function to return dynamic set of columns for given table

Define table and column names as arguments in a plpgsql function?

Dynamic SELECT INTO in PL/pgSQL function