Help with session based authentication flow

[matthewlarkin]

Greetings SQLPagers!

I'm reading through the documentation, and I'm having trouble wrapping my head around the session based authentication flow. I've gotten as far as setting up:

• users table
• sessions table
• login.sql route (login form)
• process-login.sql (authentication component + session setup)

✅ Working

• login.sql form is posting credentials correctly to process-login.sql
• session record gets inserted correctly into sessions table

❌ Not working

• I'm having trouble getting the session cookie after that on authentication-required pages.

Here are my files so far:

---

sqlpage/migrations/0001_initial_setup.sql

-- SQLite3

-- users
CREATE TABLE IF NOT EXISTS "users" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "email" TEXT NOT NULL UNIQUE check (email LIKE '%@%'),
    "username" TEXT NOT NULL UNIQUE check (length("username") <= 50),
    "password_hash" TEXT NOT NULL,
    "created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- user sessions
CREATE TABLE IF NOT EXISTS "sessions" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "user" INTEGER NOT NULL REFERENCES "users" ("id"),
    "token" TEXT NOT NULL UNIQUE,
    "created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

---

index.sql

Let's say I want to protect this page. Here, I'm just trying to show the session token so I know I have access to it (and can check the authentication).

select 'card' as component,
    'Hello, ' || sqlpage.cookie('session_token') || '!' as title;

---

login.sql

SELECT 'form' AS component,
    'Login' AS title,
    'ui/procedures/process-login.sql' AS action;

SELECT 'text' AS type,
    'username' AS name,
    'Username' AS label;

SELECT 'password' AS type,
    'password' AS name,
    'Password' AS label;

---

ui/procedures/process-login.sql

SELECT 'authentication' AS component,
    'login.sql' AS link,
    (SELECT password_hash FROM users WHERE username = :username) AS password_hash,
    :password AS password;

-- The code after this point is only executed if the user has sent the correct password

-- Generate a random session token
INSERT INTO sessions (token, user)
VALUES (sqlpage.random_string(32), (select id from users where username = :username))
RETURNING 
    'cookie' AS component,
    'session_token' AS name,
    token AS value;

-- redirect
SELECT 'redirect' AS component,
    '/' AS link;

---

Am I missing someting in this flow? I'm not getting a result from the sqlpage.cookie() function.

Thank you! 🙏

[lovasoa]

Hello !

Your example looks fine to me and works on my machine.

SQLPage-Authentication-Example.zip

How are you testing it ?

Depending on your configuration, you may want to add 'lax' as same_site and false as secure to the cookie component (see the docs). If you are testing on repl.it, note that due to their iframe embedding, setting cookies does not work from the main dev window (but it works when you open your site in a distinct tab):

https://replit.com/@pimaj62145/SQLPage-Authentication-Example

[matthewlarkin]

Thank you for the points about local dev nuances Ophir! But actually, I think it had to do with the path parameter in my case. I manually set path to '/' in the cookie component like so:

-- Generate a random session token
INSERT INTO sessions (token, user)
VALUES (sqlpage.random_string(32), (select id from users where username = :username))
RETURNING 
    'cookie' AS component,
    'session_token' AS name,
    token AS value,
    '/' AS path;

I maybe misunderstood the cookie component documentation. It says the path parameter defaults to all paths when left blank, but that seems to be the case for top-level files only.

I was using a file structure like:

├── sqlpage/
│   └── ...
├── ui/
│   └── procedures/
│       └── process-login.sql
├── index.sql
└── login.sql

It turns out process-login.sql was setting the cookie the whole time, but since process-login.sql was a couple directories down, the resulting cookie's path was being set relative to process-login.sql's location (and thus was not present when loading /index.sql)

---

Glad to have it resolved, and thank you for your help! 🙏

[lovasoa]

I just realised that the documentation said that the default for path was all web pages on the site, whereas it actually was only web pages under the current directory.