PostgreSQL. Is using random tag in dollar quoting without escaping input safe

performancepostgresqlquery-performancesql-injection

Is that safe to run such query without escaping input? Assume noone can guess the random string I generate.

SELECT * FROM foo WHERE bar = $long random tag$ THIS NOT ESCAPED $long random tag$

What drawbacks are?
For example if the tag will change for each transaction, will it have performance penalties?

EDIT: Doing escaping correctly is also sometimes tricky – remember PHP mysql_escape and mysql_real_escape and sometimes can be fooled or just forgotten. Proposed approach can be automatic.

EDIT 2:
Any ideas if that can be made on pure Postgres SQL? Remember that dollar quoting can be nested.

Best Answer

Postgres doesn't cache ad-hoc SQL query plans; only if your host environment is using prepare will the query plan be cached. But if prepare is used, then parameterisation is also being used so dollar quoting won't be necessary.

It follows, then, if you're not using prepare that each query will be parsed. In that case, you're probably best off accepting the 2 * (32 + 2) = 68 byte penalty and use UUIDs inside dollar quoting.

You could write code to generate a random short string -- eg $a$ and test if that appears in the input, and loop making that string longer if it appears. But the cost of such code might exceed just generating a UUID and using that.

Related Question