Stably escaping an identifier

Started by Phillip Diffley10 months ago3 messagesgeneral
Jump to latest
#1Phillip Diffley
phillip6402@gmail.com

I am in a situation where I need to run dynamically generated queries with
identifiers from an untrusted source. For example
SELECT * FROM <untrusted_table_name> WHERE <untrusted_column_name> = $1;

We can use format('%I', <untrusted_value>) to escape the identifier and
avoid a security vulnerability, but if the provided identifier is already
escaped, this introduces a problem. For example,
SELECT format('%I', 'my identifier');
returns "my identifier", but
SELECT format('%I', format('%I', 'my identifier'));
returns """my identifier"""
because it is escaping the previously added quotation marks.

Is there a reliable way to determine if an identifier has already been
escaped, or alternatively is there a function that will stably escape an
identifier such that the identifier will not change if the function is
called repeatedly?

Thanks,
Phillip

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Phillip Diffley (#1)
Re: Stably escaping an identifier

Phillip Diffley <phillip6402@gmail.com> writes:

Is there a reliable way to determine if an identifier has already been
escaped, or alternatively is there a function that will stably escape an
identifier such that the identifier will not change if the function is
called repeatedly?

This is impossible in general, because you can't know if the
double-quotes are meant to be part of the identifier value.

My advice here would be to flat-out reject input identifiers that
contain double quotes. I'd suggest banning newlines too while
at it, as those are known to create security issues in some
contexts.

regards, tom lane

#3Phillip Diffley
phillip6402@gmail.com
In reply to: Tom Lane (#2)
Re: Stably escaping an identifier

Thanks!

On Sun, Jun 15, 2025 at 10:11 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Show quoted text

Phillip Diffley <phillip6402@gmail.com> writes:

Is there a reliable way to determine if an identifier has already been
escaped, or alternatively is there a function that will stably escape an
identifier such that the identifier will not change if the function is
called repeatedly?

This is impossible in general, because you can't know if the
double-quotes are meant to be part of the identifier value.

My advice here would be to flat-out reject input identifiers that
contain double quotes. I'd suggest banning newlines too while
at it, as those are known to create security issues in some
contexts.

regards, tom lane