public inbox for [email protected]  

help / color / mirror / Atom feed

From: Daniele Varrazzo <[email protected]>
To: Philippe Strauss <[email protected]>
Cc: [email protected]
Subject: Re: Safe SELECT ... LIKE abc% in psycopg
Date: Mon, 22 Apr 2024 18:59:04 +0200
Message-ID: <CA+mi_8bcYQP79Yv+FsPrK7vWQfZF3fxK7uZDBaexGh1+TH2xHA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

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 <[email protected]>
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/
>
>
>
>

view thread (4+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Safe SELECT ... LIKE abc% in psycopg
  In-Reply-To: <CA+mi_8bcYQP79Yv+FsPrK7vWQfZF3fxK7uZDBaexGh1+TH2xHA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox