[gull] [SPAM] Re: Requêtes SQL en LIKE ...% avec psycopg3

[Philippe Strauss]

Merci à Marc et Daniel pour leur éclairages, hier soir j'ai posté la 
même question sur la mailing-list
de psycopg, le binding intelligent de postgresql pour python, une 
réponse était très bonne:

--
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.
--

Je vais en rester à ceci je crois (à noter que psycopg fait très 
probablement mieux que
printf pour passer les arguments, le bidule à l'air assez évolué):

--8<--
reGS = re.compile(r'^[A-Za-z\.\ ]+$')

def queryfield(field):
     m = reGS.match(field)
     if m != None:
         return '^'+re.escape(field[m.pos:m.endpos])
     else:
         return None

@app.route('/genus/<genus>')
def genus(genus):
     validated = queryfield(genus)
     if validated == None:
         return jsonify([])
     else:
         with dbconn.cursor() as cur:
             cur.execute("""SELECT myco.genus.name
                 FROM myco.genus
                 WHERE myco.genus.name ~* %s""", (validated,))
             lsgenus = cur.fetchall()
             ls = []
             for genus in lsgenus:
                 ls.append(genus[0])
         return jsonify(ls)

--8<--

aplus.

-- 
Philippe Strauss
https://straussengineering.ch/