From: | Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com> |
---|---|
To: | Philippe Strauss <philippe(at)straussaudio(dot)ch> |
Cc: | psycopg(at)lists(dot)postgresql(dot)org |
Subject: | Re: Safe SELECT ... LIKE abc% in psycopg |
Date: | 2024-04-22 16:59:04 |
Message-ID: | CA+mi_8bcYQP79Yv+FsPrK7vWQfZF3fxK7uZDBaexGh1+TH2xHA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | psycopg |
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(at)straussaudio(dot)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/
>
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Daniele Varrazzo | 2024-07-01 09:37:21 | Psycopg 3.2 released |
Previous Message | Adrian Klaver | 2024-04-22 16:57:10 | Re: Safe SELECT ... LIKE abc% in psycopg |