[Auth] - Problem managing session cookie

[Ajotah98]

Hi all,

First of all, I would like to congratulate you on this amazing piece of work you are doing. I discovered this project yesterday and I am truly amazed.

To test it, I want to develop a rather ambitious project, specifically a laboratory information system (commonly known as LIS).

For this, I need to handle user authentication.

Currently, I have the following files that handle this:

-- Index.sql

-- Shell enviroment
select 
    'shell'                   as component,
    'LaboratorIS'             as title,
    '/'                       as link,
    'Login'                   as menu_item,
    'About'                   as menu_item,
    'en-US'                   as language,
    'A Free and Open Source LIS (Laboratory Information System) project' as description,
    'Poppins'                 as font,
    'cell'                    as icon,
    'Proudly made with [SQLPage](https://sql.ophir.dev)' as footer;

-- Login form
select 
    'form' as component,
    'Login' as title,
    'Enter' as validate,
    'azure' as validate_color,
    '/actions/login_action.sql' as action;
select 
    'Username' as label,
    'username' as name;
select 
    'Password' as label,
    'password_hash' as name,
    'password' as type;
-- Sign up
select 'text' as component,
    'You don''t have an account? [Sign up](sign_up.sql)' as contents_md;


select 'text' as component,
    'Cookie session = '|| sqlpage.cookie('session_token');

-- login_action.sql

-- Autenticación: el link es para redirigir en caso de no estar autenticado
SELECT 'authentication' AS component,
    'login.sql' AS link,
    (SELECT password_hash FROM users WHERE username = :username) AS password_hash,
    :password_hash AS password;
-- Generate a random session token
INSERT INTO session (id, username)
SELECT sqlpage.random_string(32), :username
WHERE NOT EXISTS (
    SELECT 1 FROM session WHERE username = :username
)
RETURNING 
    'cookie' AS component,
    'session_token' AS name,
    id AS value,
    false as secure;
--select 'debug' as component;
--select * from session;
select 'redirect' as component, '../home.sql' as link;

According to what I have read in the documentation and in the authentication example, I should assign "session_token" as a Cookie and send it with each request.

The problem is that this is not happening, since when accessing "home.sql", the cookie is not being sent as a header:

GET /home.sql HTTP/1.1
Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7
Accept-Encoding: gzip, deflate, br
Accept-Language: es-ES,es;q=0.9,ca;q=0.8,en;q=0.7
Connection: keep-alive
Host: 127.0.0.1:8080
Sec-Fetch-Dest: document
Sec-Fetch-Mode: navigate
Sec-Fetch-Site: none
Sec-Fetch-User: ?1
Upgrade-Insecure-Requests: 1
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/121.0.0.0 Safari/537.36
sec-ch-ua: "Not A(Brand";v="99", "Google Chrome";v="121", "Chromium";v="121"
sec-ch-ua-mobile: ?0
sec-ch-ua-platform: "Windows"

What am I doing wrong exactly?

Sorry for the length, and thank you very much again for the work you are doing.

[lovasoa]

Hi ! It looks like your code references tables and other sql files that are not included here.
Can you share a github repo that reproduces your issue ?

A previous discussion that may be relevant: #200

[lovasoa]

Looking at your code more closely:

• index.sql lacks a as contents at the end:

select 'text' as component,
    'Cookie session = '|| sqlpage.cookie('session_token') as contents;

• login_action.sql is not at the root, and sets the cookie only for its own subfolder by default. You can add '/' as path.
• login_action.sql sliently prevents logging in if a session already exists, this is potentially a very frustrating behavior: WHERE NOT EXISTS (SELECT 1 FROM session WHERE username = :username)

[lovasoa]

Alternatively, you can have an INSERT OR REPLACE (with a unique index on the username column), and thus log out the previous session when an user logs in again.

[Ajotah98]

I replaced what you said, but i cannot get it.

The login_action.sql returns correctly the cookie:

But it's not passed to 'home.sql'

Also, using the correction with contents:

select 'text' as component,
'Cookie session = '|| sqlpage.cookie('session_token') as contents;

I cannot see the text.

I'll paste you the rest of the project here:

-- Index.sql (right now)

-- Shell enviroment
select 
    'shell'                   as component,
    'LaboratorIS'             as title,
    '/'                       as link,
    'Login'                   as menu_item,
    'About'                   as menu_item,
    'en-US'                   as language,
    'A Free and Open Source LIS (Laboratory Information System) project' as description,
    'Poppins'                 as font,
    'cell'                    as icon,
    'Proudly made with [SQLPage](https://sql.ophir.dev)' as footer;

-- Login form
select 
    'form' as component,
    'Login' as title,
    'Enter' as validate,
    'azure' as validate_color,
    '/actions/login_action.sql' as action;
select 
    'Username' as label,
    'username' as name;
select 
    'Password' as label,
    'password_hash' as name,
    'password' as type;
-- Sign up
select 'text' as component,
    'You don''t have an account? [Sign up](sign_up.sql)' as contents_md;


select 'text' as component,
'Cookie session = '|| sqlpage.cookie('session_token') as contents;

-- 0001_first_structure.sql

-- Creación de la tabla sample
CREATE TABLE sample (
    sampleid INTEGER PRIMARY KEY AUTOINCREMENT,
    sampledescription TEXT NOT NULL
);

-- Creación de la tabla test
CREATE TABLE test (
    testid INTEGER PRIMARY KEY AUTOINCREMENT,
    testdescription TEXT NOT NULL
);

-- Creación de la tabla testsample
CREATE TABLE testsample (
    testid INTEGER,
    sampleid INTEGER,
    FOREIGN KEY (testid) REFERENCES test(testid),
    FOREIGN KEY (sampleid) REFERENCES sample(sampleid)
);

-- Creación de la tabla users
CREATE TABLE users (
    username TEXT NOT NULL,
    password_hash TEXT NOT NULL, -- Asumiendo que 'password hash' se refiere a la columna que almacenará el hash de la contraseña
    PRIMARY KEY (username)
);

-- Creación de la tabla patient
CREATE TABLE patient (
    patientid INTEGER PRIMARY KEY AUTOINCREMENT,
    patientname TEXT NOT NULL
);

-- Creación de la tabla request
CREATE TABLE request (
    requestid INTEGER PRIMARY KEY AUTOINCREMENT,
    patientid INTEGER,
    date_of_request DATE,
    is_open BOOLEAN,
    FOREIGN KEY (patientid) REFERENCES patient(patientid)
);

-- Creación de la tabla requesttest
CREATE TABLE requesttest (
    requestid INTEGER,
    testid INTEGER,
    result_status TEXT NOT NULL DEFAULT 'NR',
    date_of_validation DATE,
    FOREIGN KEY (requestid) REFERENCES request(requestid),
    FOREIGN KEY (testid) REFERENCES test(testid),
    CHECK (result_status IN ('NR', 'WR', 'VA', 'ST')) -- Restricción para asegurar que result_status solo tome ciertos valores
);

-- 0002_session_table.sql

CREATE TABLE IF NOT EXISTS session (
    id TEXT NOT NULL,
    username TEXT NOT NULL,
    FOREIGN KEY (username) REFERENCES users(username)
)

-- sign_up.sql

select 
    'shell'                   as component,
    'LaboratorIS'             as title,
    '/'                       as link,
    'Login'                   as menu_item,
    'About'                   as menu_item,
    'en-US'                   as language,
    'A Free and Open Source LIS (Laboratory Information System) project' as description,
    'Poppins'                 as font,
    'cell'                    as icon,
    'Proudly made with [SQLPage](https://sql.ophir.dev)' as footer;

select 
    'form' as component,
    'Sign up' as title,
    'Create user' as validate,
    'red' as validate_color,
    '/actions/sign_up_action.sql' as action;
select 
    'Username' as label,
    'username' as name;
select 
    'Password' as label,
    'password_hash' as name,
    'password' as type;

-- /actions/sign_up_action.sql

INSERT INTO users (username, password_hash) values (
    :username,
    sqlpage.hash_password(:password_hash)
)
ON CONFLICT DO NOTHING
RETURNING
    'alert' as component,
    'User created' as title,
    'green' as color,
    'Click here to go back to [Login](../Login.sql)' as description_md;


select 
    'alert' as component,
    'User was not created' as title,
    'red' as color,
    'Probably user was created previously.
    Click here to go back to [Sign Up](../sign_up.sql)' as description_md;

-- actions/login_action.sql

-- Autenticación: el link es para redirigir en caso de no estar autenticado
SELECT 'authentication' AS component,
    'login.sql' AS link,
    (SELECT password_hash FROM users WHERE username = :username) AS password_hash,
    :password_hash AS password;
-- Generate a random session token
INSERT INTO session (id, username) values (sqlpage.random_string(32), :username)
RETURNING 
    'cookie' AS component,
    'session_token' AS name,
    id AS value,
    false as secure,
    '/' as link;
--select 'debug' as component;
--select * from session;
select 'redirect' as component, '../home.sql' as link;

-- home.sql

SELECT 'redirect' AS component, 'login.sql' AS link
WHERE NOT EXISTS (SELECT 1 FROM session WHERE id = sqlpage.cookie('session_token'));
-- Shell enviroment
select 
    'shell'                   as component,
    'LaboratorIS'             as title,
    '/'                       as link,
    'Login'                   as menu_item,
    'About'                   as menu_item,
    'en-US'                   as language,
    'A Free and Open Source LIS (Laboratory Information System) project' as description,
    'Poppins'                 as font,
    'cell'                    as icon,
    'Proudly made with [SQLPage](https://sql.ophir.dev)
    ' as footer;


select 'card' as component,

The 'login.sql' file it's just a redirect to index.sql

Thank you,

[lovasoa]

I think you are missing '/' as path in login_action.sql

[lovasoa]

INSERT INTO session (id, username) values (sqlpage.random_string(32), :username)
RETURNING 
    'cookie' AS component,
    'session_token' AS name,
    id AS value,
    false as secure,
-    '/' as link;
+    '/' as path;

The documentation for the path attribute says

If not specified, the cookie will be sent for all paths.

There was a mismatch between the documentation and the actual behavior. Current versions of SQLPage set the cookie only for the current foler (/action/ in your case) when the path property is omitted. The behavior has been fixed to match the documentation, and will be released in sqlpage 0.19. In the meantime, you have to explicitly set '/' as path in the cookie component.

[Ajotah98]

That fixed it, thank you so much!