Stably escaping an identifier
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
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
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