Skip to main content
The authentication component enables password-protected access to your SQLPage pages. It provides flexible authentication options including HTTP basic auth, custom login forms, and session token management.
The authentication component must be placed at the very top of your SQL file, before any other component. Once page content is sent to the browser, it’s too late to restrict access.

​
Authentication Options

SQLPage offers two main authentication approaches:
  1. Authentication component: Self-managed usernames and passwords
    • No external dependencies
    • Full control over login forms, session duration, and permissions
    • Requires password hash management
  2. Single sign-on (SSO): Use existing identity providers
    • Google, Microsoft, or organizational OAuth/OIDC providers
    • No password management required
    • See SSO documentation for setup

​
HTTP Basic Authentication

The simplest authentication method uses browser popups: 
SELECT 'authentication' AS component,
    '$argon2i$v=19$m=8,t=1,p=1$YWFhYWFhYWE$oKBq5E8XFTHO2w' AS password_hash,
    sqlpage.basic_auth_password() AS password;
The password hash above corresponds to the password “password”. Generate your own using sqlpage.hash_password().

​
Multiple Users with Basic Auth

Handle different users with a CASE statement: 
SELECT 'authentication' AS component,
    CASE sqlpage.basic_auth_username()
        WHEN 'admin' THEN '$argon2i$v=19$m=8,t=1,p=1$YWFhYWFhYWE$oKBq5E8XFTHO2w'
        WHEN 'user' THEN '$argon2i$v=19$m=8,t=1,p=1$YWFhYWFhYWE$qsrWdjgl96ooYw'
    END AS password_hash,
    sqlpage.basic_auth_password() AS password;
Or query from a database: 
SELECT 'authentication' AS component,
    (SELECT password_hash FROM users WHERE username = sqlpage.basic_auth_username()) AS password_hash,
    sqlpage.basic_auth_password() AS password;

​
Custom Login Forms

For better user experience, create custom login forms instead of browser popups.

​
login.sql

SELECT 'login' AS component;

​
protected_page.sql

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

-- Code below only executes if authentication succeeds
SELECT 'shell' AS component, 'Protected Page' AS title;
SELECT 'text' AS component, 'Welcome! You are authenticated.' AS contents;
If authentication fails, the user is redirected to login.sql.

​
Session Token Management

Password hashing is computationally expensive by design (to prevent brute-force attacks). For better performance, use session tokens instead of validating passwords on every page.

​
Create Session on Login

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

-- Authentication succeeded, create session token
INSERT INTO sessions (token, username, created_at, expires_at)
VALUES (
    sqlpage.random_string(32),
    :username,
    CURRENT_TIMESTAMP,
    datetime('now', '+7 days')
)
RETURNING
    'cookie' AS component,
    'session_token' AS name,
    token AS value,
    expires_at AS expires;

SELECT 'redirect' AS component, 'dashboard.sql' AS link;

​
Validate Session Token

protected_page.sql 
-- Check if session token is valid
SELECT 'redirect' AS component, 'login.sql' AS link
WHERE NOT EXISTS (
    SELECT 1 FROM sessions
    WHERE token = sqlpage.cookie('session_token')
    AND expires_at > CURRENT_TIMESTAMP
);

-- Token is valid, show protected content
SELECT 'shell' AS component, 'Dashboard' AS title;

​
Create Sessions Table

CREATE TABLE sessions (
    token TEXT PRIMARY KEY,
    username TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    FOREIGN KEY (username) REFERENCES users(username)
);

CREATE INDEX idx_sessions_expires ON sessions(expires_at);

​
Password Hashing

Always store password hashes, never plain passwords: 
-- Generate hash for a new user
INSERT INTO users (username, password_hash)
VALUES (
    :username,
    sqlpage.hash_password(:password)
);
You can also generate hashes using online tools like argon2.online.

​
Advanced: Role-Based Access

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

-- Check user role
SET role = (SELECT role FROM users WHERE username = :username);

SELECT 'redirect' AS component, 'access_denied.sql' AS link
WHERE $role != 'admin';

-- Admin-only content below
SELECT 'shell' AS component, 'Admin Panel' AS title;

​
Logout Functionality

logout.sql 
-- Delete session from database
DELETE FROM sessions WHERE token = sqlpage.cookie('session_token');

-- Clear cookie
SELECT 'cookie' AS component,
    'session_token' AS name,
    '' AS value,
    datetime('now', '-1 day') AS expires;

SELECT 'redirect' AS component, 'login.sql' AS link;

​
Top-level Parameters

​
link
string
URL to redirect users who are not authenticated. If omitted, uses HTTP basic authentication with a browser popup.
​
password
string
The password submitted by the user. Typically set to :password from a login form or sqlpage.basic_auth_password() for basic auth.
​
password_hash
string
The stored password hash to validate against. Use sqlpage.hash_password() to generate hashes. Use the Argon2 format.

​
Security Best Practices

​
Use Strong Password Hashing

SQLPage uses Argon2, a modern and secure hashing algorithm. Always use sqlpage.hash_password() to generate hashes: 
SELECT sqlpage.hash_password('user_password_here');

​
Implement Session Expiration

Automatically clean up expired sessions: 
DELETE FROM sessions WHERE expires_at < CURRENT_TIMESTAMP;
Run this periodically or add it to frequently accessed pages.

​
Use HTTPS

Always use HTTPS in production to encrypt credentials in transit. Configure your web server or use SQLPage’s built-in HTTPS support.

​
Rate Limiting

Prevent brute-force attacks by tracking failed login attempts: 
CREATE TABLE login_attempts (
    username TEXT,
    attempt_time TIMESTAMP,
    ip_address TEXT
);

-- Block users with too many failed attempts
SELECT 'redirect' AS component, 'too_many_attempts.sql' AS link
WHERE (
    SELECT COUNT(*) FROM login_attempts
    WHERE username = :username
    AND attempt_time > datetime('now', '-15 minutes')
) >= 5;

​
Complete Login Example

Here’s a full working example: users table 
CREATE TABLE users (
    username TEXT PRIMARY KEY,
    password_hash TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (username, password_hash)
VALUES ('admin', sqlpage.hash_password('admin123'));
login.sql 
SELECT 'form' AS component, 'create_session.sql' AS action, 'Login' AS title;
SELECT 'username' AS name, 'text' AS type, TRUE AS required;
SELECT 'password' AS name, 'password' AS type, TRUE AS required;
create_session.sql 
SELECT 'authentication' AS component,
    'login.sql' AS link,
    (SELECT password_hash FROM users WHERE username = :username) AS password_hash,
    :password AS password;

INSERT INTO sessions (token, username, expires_at)
VALUES (
    sqlpage.random_string(32),
    :username,
    datetime('now', '+7 days')
)
RETURNING
    'cookie' AS component,
    'session_token' AS name,
    token AS value;

SELECT 'redirect' AS component, 'dashboard.sql' AS link;
dashboard.sql 
SELECT 'redirect' AS component, 'login.sql' AS link
WHERE NOT EXISTS (
    SELECT 1 FROM sessions
    WHERE token = sqlpage.cookie('session_token')
    AND expires_at > CURRENT_TIMESTAMP
);

SELECT 'shell' AS component, 'Dashboard' AS title;
SELECT 'text' AS component, 'Welcome to your dashboard!' AS contents;

Build docs developers (and LLMs) love

Get started for freeTalk to us