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/
_______________________________________________
gull mailing list
gull@forum.linux-gull.ch
https://forum.linux-gull.ch/mailman/listinfo/gull
