Project

General

Profile

Fonctionnalité #16971

ANALYSE - Fonction sql non injectable dans la clause where des champs custom.

Added by Ludovic ARAUJO over 1 year ago. Updated 12 months ago.

Status:
Clôturée
Priority:
1-Majeur
Assignee:
Target version:
Start date:
05/03/2021
Due date:
Tags Courrier:
ROADMAP:

Description

clause de référence :
select * from entities where entity_id = (select entity_by_res_id(100))

test effectué :

ou

pastedImage.png (136 KB) pastedImage.png Ludovic ARAUJO, 05/03/2021 06:23 PM
pastedImage (7).png (79.8 KB) pastedImage (7).png Ludovic ARAUJO, 05/03/2021 06:24 PM
8039
8040

History

#1 Updated by Ludovic ARAUJO over 1 year ago

  • Priority changed from 2-Sérieux to 0-Bloquant

#2 Updated by Ludovic ARAUJO over 1 year ago

résultat en CMD :

psql -U maarch -d 2010 -c "select * from entities where entity_id =  (select entity_by_res_id(100))"
id | entity_id |       entity_label        |        short_label        | entity_full_name | enabled | adrs_1 | adrs_2 | adrs_3 | zipcode | city | country |       email       |     business_id      | parent_entity_id | entity_type | ldap_id | producer_service | folder_import | external_id 
----+-----------+---------------------------+---------------------------+------------------+---------+--------+--------+--------+---------+------+---------+-------------------+----------------------+------------------+-------------+---------+------------------+---------------+-------------
  1 | VILLE     | Ville de Maarch-les-bains | Ville de Maarch-les-bains |                  | Y       |        |        |        |         |      |         | support@maarch.fr | 45239273100025/VILLE |                  | Direction   |         |                  |               | {}

#3 Updated by Ludovic ARAUJO over 1 year ago

fonction sql :

create or replace function entity_by_res_id(id int) returns varchar AS $BODY$
declare
    entity varchar;
    res varchar;
begin
    select initiator into entity from res_view_letterbox where res_id = id;
    select entity_tree(entity) into res;
    return res;
END;
$BODY$ LANGUAGE plpgsql;


create or replace function entity_tree(entity varchar) returns varchar AS $BODY$
declare
    parentid varchar;
    res varchar;
begin
    select parent_entity_id into parentid from entities where entity_id = $1;
    if parentid is null or parentid = '' then
        return $1;
    else
        select entity_tree(parentid) into res;
        return res;
    end if;   
END;
$BODY$ LANGUAGE plpgsql;

#5 Updated by Support Maarch over 1 year ago

  • Status changed from A qualifier to A traiter

#6 Updated by Ludovic ARAUJO over 1 year ago

  • Target version changed from Inscription Backlog Courrier to Courrier 20.10 TMA

#7 Updated by Emmanuel DILLARD over 1 year ago

  • Tracker changed from Anomalie to Fonctionnalité
  • Subject changed from Fonction sql non injectable dans la clause where des champs custom. to ANALYSE - Fonction sql non injectable dans la clause where des champs custom.
  • Status changed from A traiter to Complément d'Informations
  • Assignee changed from EDI PO to Ludovic ARAUJO

A tester avec une vue

-> vue avec 2 colonnes

Select non autorisé (pas de sous-requêtes dans les clauses)

#8 Updated by Ludovic ARAUJO over 1 year ago

code bloquant CustomFieldController.php ligne 356-358:

         if (stripos($body['values']['clause'], 'select') !== false) {
             return ['errors' => 'Clause is not valid', 'lang' => 'invalidClause'];
         }

#9 Updated by Ludovic ARAUJO over 1 year ago

ok je test avec la vue.

#10 Updated by Ludovic ARAUJO over 1 year ago

avec la vue pas de soucis.

create or replace view vue
AS select r.res_id, 
 r.typist,
 e.entity_label as root_entity
from res_letterbox r
left join entities e on entity_id = entity_by_res_id(r.res_id::int)

#11 Updated by Ludovic ARAUJO over 1 year ago

a cloturer

#12 Updated by Ludovic ARAUJO over 1 year ago

  • Assignee changed from Ludovic ARAUJO to EDI PO

#13 Updated by Emmanuel DILLARD over 1 year ago

  • Priority changed from 0-Bloquant to 1-Majeur

#14 Updated by Emmanuel DILLARD over 1 year ago

  • Status changed from Complément d'Informations to Clôturée

#15 Updated by Emmanuel DILLARD over 1 year ago

  • Project changed from Backlog to Backlog Courrier
  • Target version changed from Courrier 20.10 TMA to 287

#16 Updated by Emmanuel DILLARD over 1 year ago

  • Target version changed from 287 to 292

#17 Updated by Emmanuel DILLARD 12 months ago

  • Target version changed from 292 to 20.10 TMA3

Also available in: Atom PDF