MIME-Version: 1.0
References: <6b4e2581-8b08-4f0c-b159-cd078fd988a9@straussaudio.ch>
In-Reply-To: <6b4e2581-8b08-4f0c-b159-cd078fd988a9@straussaudio.ch>
From: Daniele Varrazzo <daniele.varrazzo@gmail.com>
Date: Mon, 22 Apr 2024 18:59:04 +0200
Message-ID: 
 <CA+mi_8bcYQP79Yv+FsPrK7vWQfZF3fxK7uZDBaexGh1+TH2xHA@mail.gmail.com>
Subject: Re: Safe SELECT ... LIKE abc% in psycopg
To: Philippe Strauss <philippe@straussaudio.ch>
Cc: psycopg@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000913f1d0616b25b82"
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2Bmi_8bcYQP79Yv%2BFsPrK7vWQfZF3fxK7uZDBaexGh1%2BTH2xHA%40mail.gmail.com>
Precedence: bulk

--000000000000913f1d0616b25b82
Content-Type: text/plain; charset="UTF-8"

Hello Philippe,

Your code seems safe to me. Because the `%` is in the value, not in the
query, I don't think you need to escape it.

If any, I would suggest you to avoid using LIKE and to use the Postgres
regular expression operators (
https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-POSIX-REGEXP),
which integrate better with Python regular expression. Using them, your
code (which I assume be looking for a prefix) might end up looking like:

    import re
    ...
    WHERE myco.genus.name ~ %s""", ('^' + re.escape(genus.upper()),))

or using the `~*` operator if you want a non-case-sensitive match.

Note that if your table is large you can index the search using trigram
indexes: see <https://www.postgresql.org/docs/current/pgtrgm.html>. But
this is not related to psycopg.

Cheers

-- Daniele

On Mon, 22 Apr 2024 at 18:34, Philippe Strauss <philippe@straussaudio.ch>
wrote:

> Hello, I'm Philippe from switzerland,
>
> I'm writing using python a small JSON API for a mycology photos archive
> webapp. Aside the main API endpoint are two
> helpers for an autocomplete form.
> Here is the first one:
>
> --8<--
> @app.route('/genus/<genus>')
> def genus(genus):
>      with dbconn.cursor() as cur:
>          cur.execute("""SELECT myco.genus.name
>              FROM myco.genus
>              WHERE myco.genus.name LIKE %s""", (genus.upper()+'%',))
>          lsgenus = cur.fetchall()
>          ls = []
>          for genus in lsgenus:
>              ls.append(genus[0])
>      return jsonify(ls)
> --8<--
>
> My questions:
> - What is the best way to use in psycopg3 to express a SELECT ... WHERE
> ... LIKE blah% ?
> - Is my code above safe or vulnerable to a injection attack?
> - What peoples having passed on the same pattern have to recommend?
>
> Thanks!
>
> --
> Philippe Strauss
> https://straussengineering.ch/
>
>
>
>

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

<div dir=3D"ltr"><div>Hello Philippe,</div><div><br></div><div>Your code se=
ems safe to me. Because the `%` is in the value, not in the query, I don=
9;t think you need to escape it.</div><div><br></div><div>If any, I would s=
uggest you to avoid using LIKE and to use the Postgres regular expression o=
perators (<a href=3D"https://www.postgresql.org/docs/current/functions-matc=
hing.html#FUNCTIONS-POSIX-REGEXP">https://www.postgresql.org/docs/current/f=
unctions-matching.html#FUNCTIONS-POSIX-REGEXP</a>), which integrate better =
with Python regular expression. Using them, your code (which I assume be lo=
oking for a prefix) might end up looking like:<br><br></div><div>=C2=A0=C2=
=A0=C2=A0 import re</div><div>=C2=A0=C2=A0=C2=A0 ...<br></div><div>=C2=A0=
=C2=A0=C2=A0 WHERE <a href=3D"http://myco.genus.name" rel=3D"noreferrer" ta=
rget=3D"_blank">myco.genus.name</a> ~ %s&quot;&quot;&quot;, (&#39;^&#39; + =
re.escape(genus.upper()),))</div><div><br></div><div>or using the `~*` oper=
ator if you want a non-case-sensitive match.<br></div><div><br></div><div>N=
ote that if your table is large you can index the search using trigram inde=
xes: see &lt;<a href=3D"https://www.postgresql.org/docs/current/pgtrgm.html=
">https://www.postgresql.org/docs/current/pgtrgm.html</a>&gt;. But this is =
not related to psycopg.</div><div><br></div><div>Cheers</div><div><br></div=
><div>-- Daniele<br></div></div><br><div class=3D"gmail_quote"><div dir=3D"=
ltr" class=3D"gmail_attr">On Mon, 22 Apr 2024 at 18:34, Philippe Strauss &l=
t;<a href=3D"mailto:philippe@straussaudio.ch">philippe@straussaudio.ch</a>&=
gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0=
px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Hello=
, I&#39;m Philippe from switzerland,<br>
<br>
I&#39;m writing using python a small JSON API for a mycology photos archive=
 <br>
webapp. Aside the main API endpoint are two<br>
helpers for an autocomplete form.<br>
Here is the first one:<br>
<br>
--8&lt;--<br>
@app.route(&#39;/genus/&lt;genus&gt;&#39;)<br>
def genus(genus):<br>
=C2=A0=C2=A0=C2=A0=C2=A0 with dbconn.cursor() as cur:<br>
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 cur.execute(&quot;&quot;&q=
uot;SELECT <a href=3D"http://myco.genus.name" rel=3D"noreferrer" target=3D"=
_blank">myco.genus.name</a><br>
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 FR=
OM myco.genus<br>
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 WH=
ERE <a href=3D"http://myco.genus.name" rel=3D"noreferrer" target=3D"_blank"=
>myco.genus.name</a> LIKE %s&quot;&quot;&quot;, (genus.upper()+&#39;%&#39;,=
))<br>
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 lsgenus =3D cur.fetchall()=
<br>
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ls =3D []<br>
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 for genus in lsgenus:<br>
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ls=
.append(genus[0])<br>
=C2=A0=C2=A0=C2=A0=C2=A0 return jsonify(ls)<br>
--8&lt;--<br>
<br>
My questions:<br>
- What is the best way to use in psycopg3 to express a SELECT ... WHERE <br=
>
... LIKE blah% ?<br>
- Is my code above safe or vulnerable to a injection attack?<br>
- What peoples having passed on the same pattern have to recommend?<br>
<br>
Thanks!<br>
<br>
-- <br>
Philippe Strauss<br>
<a href=3D"https://straussengineering.ch/" rel=3D"noreferrer" target=3D"_bl=
ank">https://straussengineering.ch/</a><br>
<br>
<br>
<br>
</blockquote></div>

--000000000000913f1d0616b25b82--