Subject: Safe SELECT ... LIKE abc% in psycopg

From: Philippe Strauss <[email-masked]>
Date: Mon, 22 Apr 2024 18:34:40 +0200

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: Adrian Klaver <[email-masked]>
Date: Mon, 22 Apr 2024 09:50:31 -0700

> - What peoples having passed on the same pattern have to recommend?

Read:

https://www.psycopg.org/psycopg3/docs/basic/params.html

It will answer the above.

For this case from link:

"When parameters are used, in order to include a literal % in the query 
you can use the %% string:"

-- 
Adrian Klaver
[email-masked]

---

From: Adrian Klaver <[email-masked]>
Date: Mon, 22 Apr 2024 09:57:10 -0700

On 4/22/24 09:50, Adrian Klaver wrote:
>> I'm writing using python a small JSON API for a mycology photos 
>> archive webapp. Aside the main API endpoint are two
>> - What is the best way to use in psycopg3 to express a SELECT ... 
>> WHERE ... LIKE blah% ?

Actually ignore the above, that only applies if you are using % in the 
query itself not in the supplied arguments.

---

From: Daniele Varrazzo <[email-masked]>
Date: Mon, 22 Apr 2024 18:59:04 +0200

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