Обсуждение: 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:
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