MIME-Version: 1.0
From: Phillip Diffley <phillip6402@gmail.com>
Date: Sun, 15 Jun 2025 21:55:10 +0200
Message-ID: <CAGAwPgQ+rhRaiPyrjG2DZhEgtYxRnsAa6jZYq2FdHu8Se+YWdg@mail.gmail.com>
Subject: Stably escaping an identifier
To: pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000ca86910637a1a82c"
Archived-At: <https://www.postgresql.org/message-id/CAGAwPgQ%2BrhRaiPyrjG2DZhEgtYxRnsAa6jZYq2FdHu8Se%2BYWdg%40mail.gmail.com>
Precedence: bulk

--000000000000ca86910637a1a82c
Content-Type: text/plain; charset="UTF-8"

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

--000000000000ca86910637a1a82c
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div>I am in a situation where I need to =
run dynamically generated queries with identifiers from an untrusted source=
. For example</div><div>SELECT * FROM &lt;untrusted_table_name&gt; WHERE &l=
t;untrusted_column_name&gt; =3D $1;</div><div><br></div><div>We can use=C2=
=A0format(&#39;%I&#39;, &lt;untrusted_value&gt;) to escape the identifier a=
nd avoid a security vulnerability, but if the provided identifier is alread=
y escaped, this introduces a problem. For example,</div><div>SELECT format(=
&#39;%I&#39;, &#39;my identifier&#39;);</div><div>returns=C2=A0&quot;my ide=
ntifier&quot;, but=C2=A0</div><div>SELECT format(&#39;%I&#39;, format(&#39;=
%I&#39;, &#39;my identifier&#39;));</div><div>returns=C2=A0&quot;&quot;&quo=
t;my identifier&quot;&quot;&quot;</div><div>because it is escaping the prev=
iously added quotation marks.</div><div><br></div><div>Is there a reliable =
way to determine if an identifier has already been escaped, or alternativel=
y is there a function that will stably escape an identifier such that the i=
dentifier will not change if the function is called repeatedly?</div><div><=
br></div><div>Thanks,</div><div>Phillip</div></div>
</div>

--000000000000ca86910637a1a82c--