Re: Safe SELECT ... LIKE abc% in 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/
>
>
>
>