Plantage sur modification de l'id CAS d'un utilisateur via le formulaire utilisateur #631

New Issue

jmplace · 2023-04-26T10:16:48+02:00

jmplace commented

2023-04-26 10:16:48 +02:00

il y a un soucis de syntaxe dans la requete sql quand on saisi une valeur numerique (ici l uid) comme idCAS dans le formulaire de modification d'un utilisateur (creat_user_form)
quand on passe par l import de fichier excel pas de problème


Traceback (most recent call last):
  File "/opt/scodoc/venv/lib/python3.9/site-packages/flask/app.py", line 2077, in wsgi_app
    response = self.full_dispatch_request()
  File "/opt/scodoc/venv/lib/python3.9/site-packages/flask/app.py", line 1525, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/opt/scodoc/venv/lib/python3.9/site-packages/flask/app.py", line 1523, in full_dispatch_request
    rv = self.dispatch_request()
  File "/opt/scodoc/venv/lib/python3.9/site-packages/flask/app.py", line 1509, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
  File "/opt/scodoc/app/decorators.py", line 80, in scodoc_function
    return func(*args, **kwargs)
  File "/opt/scodoc/app/decorators.py", line 92, in decorated_function
    return f(*args, **kwargs)
  File "/opt/scodoc/app/decorators.py", line 227, in scodoc7func_decorator
    value = func(*pos_arg_values, **kwargs)
  File "/opt/scodoc/app/views/users.py", line 608, in create_user_form
    ok_modif, msg = sco_users.check_modif_user(
  File "/opt/scodoc/app/scodoc/sco_users.py", line 354, in check_modif_user
    cas_users = User.query.filter_by(cas_id=cas_id).all()
  File "/opt/scodoc/venv/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 2768, in all
    return self._iter().all()
  File "/opt/scodoc/venv/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 2903, in _iter
    result = self.session.execute(
  File "/opt/scodoc/venv/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1692, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File "/opt/scodoc/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1631, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/opt/scodoc/venv/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 325, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/opt/scodoc/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1498, in _execute_clauseelement
    ret = self._execute_context(
  File "/opt/scodoc/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1862, in _execute_context
    self._handle_dbapi_exception(
  File "/opt/scodoc/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2043, in _handle_dbapi_exception
    util.raise_(
  File "/opt/scodoc/venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/opt/scodoc/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "/opt/scodoc/venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedFunction) ERREUR:  l'opérateur n'existe pas : text = integer
LINE 3: WHERE "user".cas_id = 5770
                            ^
HINT:  Aucun opérateur ne correspond au nom donné et aux types d'arguments.
Vous devez ajouter des conversions explicites de type.

[SQL: SELECT "user".id AS user_id, "user".user_name AS user_user_name, "user".email AS user_email, "user".email_institutionnel AS user_email_institutionnel, "user".nom AS user_nom, "user".prenom AS user_prenom, "user".dept AS user_dept, "user".active AS user_active, "user".cas_id AS user_cas_id, "user".cas_allow_login AS user_cas_allow_login, "user".cas_allow_scodoc_login AS user_cas_allow_scodoc_login, "user".cas_last_login AS user_cas_last_login, "user".password_hash AS user_password_hash, "user".password_scodoc7 AS user_password_scodoc7, "user".last_seen AS user_last_seen, "user".date_modif_passwd AS user_date_modif_passwd, "user".date_created AS user_date_created, "user".date_expiration AS user_date_expiration, "user".passwd_temp AS user_passwd_temp, "user".token AS user_token, "user".token_expiration AS user_token_expiration
FROM "user"
WHERE "user".cas_id = %(cas_id_1)s]
[parameters: {'cas_id_1': 5770}]

il y a un soucis de syntaxe dans la requete sql quand on saisi une valeur numerique (ici l uid) comme idCAS dans le formulaire de modification d'un utilisateur (creat_user_form) quand on passe par l import de fichier excel pas de problème ```The above exception was the direct cause of the following exception: Traceback (most recent call last): File "/opt/scodoc/venv/lib/python3.9/site-packages/flask/app.py", line 2077, in wsgi_app response = self.full_dispatch_request() File "/opt/scodoc/venv/lib/python3.9/site-packages/flask/app.py", line 1525, in full_dispatch_request rv = self.handle_user_exception(e) File "/opt/scodoc/venv/lib/python3.9/site-packages/flask/app.py", line 1523, in full_dispatch_request rv = self.dispatch_request() File "/opt/scodoc/venv/lib/python3.9/site-packages/flask/app.py", line 1509, in dispatch_request return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args) File "/opt/scodoc/app/decorators.py", line 80, in scodoc_function return func(*args, **kwargs) File "/opt/scodoc/app/decorators.py", line 92, in decorated_function return f(*args, **kwargs) File "/opt/scodoc/app/decorators.py", line 227, in scodoc7func_decorator value = func(*pos_arg_values, **kwargs) File "/opt/scodoc/app/views/users.py", line 608, in create_user_form ok_modif, msg = sco_users.check_modif_user( File "/opt/scodoc/app/scodoc/sco_users.py", line 354, in check_modif_user cas_users = User.query.filter_by(cas_id=cas_id).all() File "/opt/scodoc/venv/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 2768, in all return self._iter().all() File "/opt/scodoc/venv/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 2903, in _iter result = self.session.execute( File "/opt/scodoc/venv/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1692, in execute result = conn._execute_20(statement, params or {}, execution_options) File "/opt/scodoc/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1631, in _execute_20 return meth(self, args_10style, kwargs_10style, execution_options) File "/opt/scodoc/venv/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 325, in _execute_on_connection return connection._execute_clauseelement( File "/opt/scodoc/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1498, in _execute_clauseelement ret = self._execute_context( File "/opt/scodoc/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1862, in _execute_context self._handle_dbapi_exception( File "/opt/scodoc/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2043, in _handle_dbapi_exception util.raise_( File "/opt/scodoc/venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_ raise exception File "/opt/scodoc/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context self.dialect.do_execute( File "/opt/scodoc/venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute cursor.execute(statement, parameters) sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedFunction) ERREUR: l'opérateur n'existe pas : text = integer LINE 3: WHERE "user".cas_id = 5770 ^ HINT: Aucun opérateur ne correspond au nom donné et aux types d'arguments. Vous devez ajouter des conversions explicites de type. [SQL: SELECT "user".id AS user_id, "user".user_name AS user_user_name, "user".email AS user_email, "user".email_institutionnel AS user_email_institutionnel, "user".nom AS user_nom, "user".prenom AS user_prenom, "user".dept AS user_dept, "user".active AS user_active, "user".cas_id AS user_cas_id, "user".cas_allow_login AS user_cas_allow_login, "user".cas_allow_scodoc_login AS user_cas_allow_scodoc_login, "user".cas_last_login AS user_cas_last_login, "user".password_hash AS user_password_hash, "user".password_scodoc7 AS user_password_scodoc7, "user".last_seen AS user_last_seen, "user".date_modif_passwd AS user_date_modif_passwd, "user".date_created AS user_date_created, "user".date_expiration AS user_date_expiration, "user".passwd_temp AS user_passwd_temp, "user".token AS user_token, "user".token_expiration AS user_token_expiration FROM "user" WHERE "user".cas_id = %(cas_id_1)s] [parameters: {'cas_id_1': 5770}] ```

jmplace commented

2023-05-11 15:51:52 +02:00

en regardant le source, on s aperçoit que c est dans la vérification de l'unicité de l'Idcas.

app/scodoc/sco_users.py , fonction check_modif_user.

l'unicité se vérifie en constatant une réponse vide à la requete ;filter_by(cas_id=cas_id) (ligne 354)

quand le cas_id proposé est numérique la requete sql est fautive (pas de ' dans la requete autour de 5770

LINE 3: WHERE "user".cas_id = 5770

pourtant on a bien cas_id: str (dans les paramètres de la fonction)
il y a peut être une conversion en chaîne à tenter daans le filter_by (je ne peux pas tester facilement la seule machine cas que j'ai est mon serveur de prod)

en regardant le source, on s aperçoit que c est dans la vérification de l'unicité de l'Idcas. app/scodoc/sco_users.py , fonction check_modif_user. l'unicité se vérifie en constatant une réponse vide à la requete ;filter_by(cas_id=cas_id) (ligne 354) quand le cas_id proposé est numérique la requete sql est fautive (pas de ' dans la requete autour de 5770 > LINE 3: WHERE "user".cas_id = 5770 pourtant on a bien cas_id: str (dans les paramètres de la fonction) il y a peut être une conversion en chaîne à tenter daans le filter_by (je ne peux pas tester facilement la seule machine cas que j'ai est mon serveur de prod)

viennet added this to the ScoDoc 9.4 milestone 2023-05-12 12:52:01 +02:00

viennet added the

bug

label 2023-05-12 12:52:08 +02:00

viennet referenced this issue from a commit

2023-05-12 12:59:30 +02:00

Fix #631: cas_id numérique

viennet closed this issue

2023-05-12 12:59:30 +02:00

viennet commented

2023-05-12 12:59:58 +02:00

cas_idest une chaîne (texten postgresql).

>>> User.query.filter_by(cas_id="XXX").all()
[]
>>> User.query.filter_by(cas_id=12).all()
Traceback (most recent call last):
  File "/opt/scodoc/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
    self.dialect.do_execute(
  File "/opt/scodoc/venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.UndefinedFunction: operator does not exist: text = integer
LINE 3: WHERE "user".cas_id = 12

Il faut donc caster explicitement: User.query.filter_by(cas_id=str(cas_id))

Fixed by 866efbed92

`cas_id`est une chaîne (`text`en postgresql). ```py >>> User.query.filter_by(cas_id="XXX").all() [] >>> User.query.filter_by(cas_id=12).all() Traceback (most recent call last): File "/opt/scodoc/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context self.dialect.do_execute( File "/opt/scodoc/venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute cursor.execute(statement, parameters) psycopg2.errors.UndefinedFunction: operator does not exist: text = integer LINE 3: WHERE "user".cas_id = 12 ``` Il faut donc caster explicitement: `User.query.filter_by(cas_id=str(cas_id))` Fixed by 866efbed92260dcc86af83e8995a40deb7171b82

Sign in to join this conversation.