Question about safety and good practices

[monsieurmerle]

I am currently developping an app for a company. The aim of this app is to allow clients to access some documents tied to a product made by the company I work for. There are different levels of users, where client companies can create managers (who can access multiple groups of products) and workers (who can access multiple products).

At the moment, I have organised the files under this directory structure :

/ model

db.sql (the database definition)
/handlers

handler-entity.sql

/ back

manage-entity.sql
/ displays

entity.sql

/forms

entity.sql

/ front

The handlers take care of all the writing, modifying and deleting from the database (I am using the 'http-header' component to hide the handlers and redirect to pages).
The manage-entity.sql files show a page with options to see a certain entity (manage-companies.sql to see a table of companies, with options to add, delete etc).
The displays show a specific entity with its details in a datagrid, and the forms are quite self descriptive.

The back is composed of all pages that the administrators will have access to to create most of the users and products.
The front is dedicated for users, and as some users can create other users of products (a company can create manager accounts for its employees if needed etc).

Here is my question : Would it be safer to have a dedicated handlers folder in the front folder for any interaction that the users can have with the database ? Or can the users just call the handlers and it should be no problem ?

[lovasoa]

Hi! I'm not sure I really understand the question.

How you organize folders is not really related to how secure your application is. Everything under the web root will be accessible to everyone.

Before any sensitive database operation, you should use the redirect component with a where clause that checks the current user is authorized.

[monsieurmerle]

So you are saying that I should have a redirect at the top of my handlers to check if people are authorised to change things in the database ? Could I use my handlers for all users, with correctly set conditions that prevent the wrong user from making changes they are not allow to in the database ?

Could it be possible to use the path parametre on the cookie for different sessions to limit the access to some parts of the files ? (I am not sure on how it works)

I am trying to make it work the right way : thankfully we plan on having the least data required and the least sensible as possible, but I would like to avoid making mistakes.

[monsieurmerle]

A continuation of my previous answer and another question :

I have multiple user roles (company level, manager level and technician level) with different data stored in three dedicated tables. Would it be a bad pratice to add an id (i.e. 1 for company, 2 for manager, etc) at the end of the session token to use when I want to check for the user rights ?
Or should I just make three different sessions tables ? Having different login pages for different user types is not an issue in my case (the technician are expected to arrive for a specific redirection).

[SebastiendOrnano]

the way I work :
I have created several "shell.sql" files such as shell_4.sql (4 means spervisors rights : CRUD, 3 means editor rights : CRU) ; those files check the rights level of the user before opening the main shell with the menu, footer... (shell_core). In the core shell, there is some filters in the menu depending the rights level of the user.

SET user_role = (
    SELECT user_role FROM users
    INNER JOIN sessions ON users.user_id = sessions.user_id
    WHERE sessions.session_token = sqlpage.cookie('session_token')
); 

SELECT 'redirect' AS component,        
'/a_sessions/session_create_alert4.sql' AS link 
WHERE NOT ($user_role = 'supervisor') OR sqlpage.cookie('session_token') IS NULL;

SELECT 'dynamic' AS component, sqlpage.run_sql('a_shells/shell_core.sql') AS properties;

and I created one specific file for each type of users starting with the redirect component to check the rights of the user :
so I have a display_doc_4.sql with edit and delete rights for supervisors and a doc_display_3.sql with only edit rights :
display_doc_4.sql

SELECT 'dynamic' AS component, sqlpage.run_sql('a_shells/shell_4.sql') AS properties;

SET orga_dept_name = SELECT orga_dept_name FROM orga_dept WHERE orga_dept_id=$orga_dept_id::INTEGER;
SET orga_id = SELECT orga_id FROM orga_dept WHERE  orga_dept_id=$orga_dept_id::INTEGER;

SELECT 
    'breadcrumb' as component;
SELECT 
    'Home' as title,
    '/'    as link;
SELECT 
    'Gestion Superviseur'         as title,
    '/a_panels/panel_supervisor_4.sql' as link;
SELECT 
   '/b_orga_dept/orga_dept_main_display_4.sql?orga_id='||$orga_id   AS link,
    'Retour à  l''organisation'                  AS title;

-- Supervisor panel to manage docs
SELECT 
    'datagrid'              as component,
    'panel_orga_doc_display' as id,
    'Gestion des documents de l''organisation : '||$orga_dept_name as title;

SELECT 
    '/b_orga_dept_doc/orga_dept_doc_main_form_4.sql'          as link,
    'Création d''un nouveau document'          as description,
    'books'                                    as icon,
    'yellow'                                   as color;
SELECT 
    '/b_orga_dept_doc/orga_dept_doc_archive_display_4.sql?orga_dept_id='||$orga_dept_id   as link,
    'Docs archivés'          as description,
    'archive'                as icon,
    'blue'                   as color;
SELECT 
    '/b_orga_dept_doc/orga_dept_doc_select_display_4.sql?orga_dept_id='||$orga_dept_id   as link,
    'Actualiser une série de documents'      as description,
    'refresh'                                as icon,
    'green'                                  as color;

-- Display list of  docs
SELECT 'table' AS component, 
    'Liste des documents'   AS title,
    'Titre'           AS markdown,
    '/b_orga_dept_doc/orga_dept_doc_main_edit_4.sql?orga_doc_id={id}' as edit_url,
    '/b_orga_dept_doc/orga_dept_doc_main_delete_alert1_4.sql?orga_doc_id={id}' as delete_url,
    TRUE             AS sort, 
    TRUE             AS search;

SELECT
    orga_doc_id        AS Id,
    orga_doc_id        AS _sqlpage_id,
   '['||orga_doc_title||']('||orga_doc_url||')'    AS Titre,
   orga_doc_date_publication     AS Date_Pub,
   orga_doc_date_period          AS Period,
   orga_doc_category             AS Categorie,
   orga_doc_edition              as Edition,
   orga_doc_status               AS Pubication
FROM orga_dept_docs
WHERE orga_doc_status != 'archived' AND orga_dept_id=$orga_dept_id::INTEGER
ORDER BY created_at DESC;

display_doc_3.sql

SELECT 'dynamic' AS component, sqlpage.run_sql('a_shells/shell_3.sql') AS properties;

SET orga_dept_name = SELECT orga_dept_name FROM orga_dept WHERE orga_dept_id=$orga_dept_id::INTEGER;
SET orga_id = SELECT orga_id FROM orga_dept WHERE  orga_dept_id=$orga_dept_id::INTEGER;

SELECT 
    'breadcrumb' as component;
SELECT 
    'Home' as title,
    '/'    as link;
SELECT 
    'Gestion Superviseur'         as title,
    '/a_panels/panel_editor_3.sql' as link;

SELECT 
   '/b_orga_dept/orga_dept_main_display_3.sql?orga_id='||$orga_id   AS link,
    'Retour à  l''organisation'                  AS title;


-- Supervisor panel to manage members
SELECT 
    'datagrid'              as component,
    'panel_orga_doc_display' as id,
    'Gestion des documents de l''organisation : '||$orga_dept_name as title;

SELECT 
    '/b_orga_dept_doc/orga_dept_doc_main_form_3.sql'          as link,
    'Création d''un nouveau document'          as description,
    'books'                                    as icon,
    'yellow'                                   as color;
SELECT 
    '/b_orga_dept_doc/orga_dept_doc_archive_display_3.sql?orga_dept_id='||$orga_dept_id   as link,
    'Docs archivés'          as description,
    'archive'                as icon,
    'blue'                   as color;
SELECT 
    '/b_orga_dept_doc/orga_dept_doc_select_display_3.sql?orga_dept_id='||$orga_dept_id   as link,
    'Actualiser une série de documents'      as description,
    'refresh'                                as icon,
    'green'                                  as color;


-- Display list of  docs

SELECT 'table' AS component, 
    'Liste des documents'   AS title,
    'Titre'           AS markdown,
    '/b_orga_dept_doc/orga_dept_doc_main_edit_3.sql?orga_doc_id={id}' as edit_url,

    TRUE             AS sort, 
    TRUE             AS search;

SELECT
    orga_doc_id        AS Id,
    orga_doc_id        AS _sqlpage_id,
   '['||orga_doc_title||']('||orga_doc_url||')'    AS Titre,
   orga_doc_date_publication     AS Date_Pub,
   orga_doc_date_period          AS Period,
   orga_doc_category             AS Categorie,
   orga_doc_edition              as Edition,
   orga_doc_status               AS Pubication
FROM orga_dept_docs
WHERE orga_doc_status != 'archived' AND orga_dept_id=$orga_dept_id::INTEGER
ORDER BY created_at DESC;

It is a rather crude way to manage the rights since you have to create 2 files for the same purpose ; you can choose to create only one file with a check of the rights level at the beginning of each file and a filter on the delete option (if user type is editor then nothing, if user type is supervisor then delete.....) but I prefer the double files options since it is easier to read and even it means more files in your folder, you know where you are.
(_you can have a look to the shells folder here and to a example folder here )

[monsieurmerle]

Even if I am not sure I have understood it all, it gives me an idea of how to do it.

Thank you