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 <phili...@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/ > > > >
