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 WHERE = $1;
We can use format('%I', ) 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
Re: Stably escaping an identifier
Phillip Diffley 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
Re: pg_restore ERROR: permission denied to change default privileges
On 6/15/25 01:06, Rachel Roch wrote: 14 Jun 2025, 16:21 by adrian.kla...@aklaver.com: Isn't fgrep -F redundant? As I understand it fgrep = grep -F You have the wrong end of the stick. ;-) Don't think so, the -F is redundant. Try grep -F then fgrep and then fgrep -F on the same file. As per Grep 3.8 release notes (https://lists.gnu.org/archive/html/info-gnu/2022-09/msg1.html): "The egrep and fgrep commands, which have been deprecated since release 2.5.3 (2007), now warn that they are obsolescent and should be replaced by grep -E and grep -F." -- Adrian Klaver adrian.kla...@aklaver.com
Re: pg_restore ERROR: permission denied to change default privileges
On 6/15/25 08:15, Adrian Klaver wrote: On 6/15/25 01:06, Rachel Roch wrote: 14 Jun 2025, 16:21 by adrian.kla...@aklaver.com: Isn't fgrep -F redundant? As I understand it fgrep = grep -F You have the wrong end of the stick. ;-) Don't think so, the -F is redundant. It is redundant for fgrep. Try grep -F then fgrep and then fgrep -F on the same file. As per Grep 3.8 release notes (https://lists.gnu.org/archive/html/info-gnu/2022-09/msg1.html): "The egrep and fgrep commands, which have been deprecated since release 2.5.3 (2007), now warn that they are obsolescent and should be replaced by grep -E and grep -F." -- Adrian Klaver adrian.kla...@aklaver.com
Re: pg_restore ERROR: permission denied to change default privileges
14 Jun 2025, 16:21 by adrian.kla...@aklaver.com: > > Isn't fgrep -F redundant? As I understand it fgrep = grep -F > You have the wrong end of the stick. ;-) As per Grep 3.8 release notes (https://lists.gnu.org/archive/html/info-gnu/2022-09/msg1.html): "The egrep and fgrep commands, which have been deprecated since release 2.5.3 (2007), now warn that they are obsolescent and should be replaced by grep -E and grep -F."
