[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.


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.

More Related questions