User Authentication

Learn how to implement a complete user authentication system in SQLPage with secure password hashing, session management, and protected pages.

What You’ll Build

A full authentication system with:

User signup with password validation
Secure login with password verification
Session management with cookies
Protected pages that require authentication
Logout functionality

Database Schema

Create two tables: one for user credentials and one for active sessions. sqlpage/migrations/0000_init.sql

CREATE TABLE user_info (
    username TEXT PRIMARY KEY,
    password_hash TEXT NOT NULL
);

CREATE TABLE login_session (
    id TEXT PRIMARY KEY,
    username TEXT NOT NULL REFERENCES user_info(username),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Never store passwords in plain text. SQLPage provides sqlpage.hash_password() which follows OWASP best practices for password storage.

Create the Signup Form

signup.sql

SELECT 'form' AS component,
    'Create a new user account' AS title,
    'Sign up' AS validate,
    'create_user.sql' AS action;

SELECT 'username' AS name;

SELECT 'password' AS name, 
    'password' AS type, 
    '^(?=.*[A-Za-z])(?=.*\d)[A-Za-z\d]{8,}$' AS pattern, 
    'Password must be at least 8 characters long and contain at least one letter and one number.' AS description;

SELECT 'terms' AS name, 
    'I accept the terms and conditions' AS label, 
    TRUE AS required, 
    FALSE AS value, 
    'checkbox' AS type;

The form uses HTML5 validation with a regex pattern to enforce password strength.

Handle User Creation

create_user.sql

INSERT INTO user_info (username, password_hash)
VALUES (:username, sqlpage.hash_password(:password))
ON CONFLICT (username) DO NOTHING
RETURNING 
    'redirect' AS component,
    'create_user_welcome_message.sql?username=' || :username AS link;

-- If we reach here, username was already taken
SELECT 'redirect' AS component, 
    'create_user_welcome_message.sql?error&username=' || :username AS link;

Key Function: sqlpage.hash_password(:password) securely hashes the password using industry-standard bcrypt algorithm with appropriate cost factor.

Create Login Form

signin.sql

SELECT 'login' AS component,
    'login.sql' AS action,
    'Sign in' AS title,
    'Username' AS username,
    'Password' AS password,
    'user' AS username_icon,
    'lock' AS password_icon,
    case when $error is not null 
        then 'We could not authenticate you. Please log in or [create an account](signup.sql).' 
    end as error_message_md,
    'Sign in' AS validate;

The login component provides a beautiful, pre-built login interface.

Verify Password

login.sql

-- Verify the password
SELECT 'authentication' AS component,
    'signin.sql?error' AS link,
    (SELECT password_hash FROM user_info WHERE username = :username) AS password_hash,
    :password AS password;

-- Generate a session ID and store it
INSERT INTO login_session (id, username)
VALUES (sqlpage.random_string(32), :username)
RETURNING 
    'cookie' AS component,
    'session' AS name,
    id AS value,
    FALSE AS secure;

-- Redirect to protected page
SELECT 'redirect' AS component, 'protected_page.sql' AS link;

How Authentication Works

Password Verification

The authentication component compares the submitted password with the stored hash:

If passwords don’t match: SQLPage redirects to the link parameter and stops execution
If passwords match: Execution continues to the next query

This component prevents timing attacks and uses constant-time comparison.

Session Generation

sqlpage.random_string(32)

Generates a cryptographically secure random session ID. This is stored in:

The login_session table (server-side)
A browser cookie (client-side)

Cookie Storage

Creating Protected Pages

Protected pages check for a valid session before displaying content. protected_page.sql

-- Get the username from the session
SET username = (
    SELECT username 
    FROM login_session 
    WHERE id = sqlpage.cookie('session')
);

-- Redirect to login if not authenticated
SELECT 'redirect' AS component,
    'signin.sql?error' AS link
WHERE $username IS NULL;

-- Display the page shell
SELECT 'shell' AS component, 
    'Protected page' AS title, 
    'lock' AS icon, 
    '/' AS link, 
    'logout' AS menu_item;

-- Show protected content
SELECT 'text' AS component,
    'Welcome, ' || $username || ' !' AS title,
    'This content is [top secret](https://youtu.be/dQw4w9WgXcQ).
    You cannot view it if you are not connected.' AS contents_md;

Authentication Check Pattern

Retrieve Session

Use sqlpage.cookie('session') to get the session ID from the browser cookie.

Validate Session

Query the login_session table to check if the session ID exists and get the associated username.

Redirect if Invalid

If $username is NULL, the session is invalid or expired - redirect to login.

Display Content

If authentication succeeds, display the protected content with personalized greeting.

Implementing Logout

logout.sql

-- Delete the session from the database
DELETE FROM login_session WHERE id = sqlpage.cookie('session');

-- Clear the session cookie
SELECT 'cookie' AS component,
    'session' AS name,
    '' AS value;

-- Redirect to home
SELECT 'redirect' AS component, '/' AS link;

Logout performs three actions:

Deletes the session from the database
Clears the cookie by setting it to an empty value
Redirects the user to the home page

Show different menu items based on whether the user is logged in. index.sql

SELECT 'shell' AS component,
    'User Management App' AS title,
    'user' AS icon,
    '/' AS link,
    CASE COALESCE(sqlpage.cookie('session'), '')
        WHEN '' THEN '["signin", "signup"]'::json
        ELSE '["logout"]'::json
    END AS menu_item;

This dynamically changes the menu:

Not logged in: Shows “signin” and “signup” links
Logged in: Shows “logout” link

Security Best Practices

Password Hashing

Always use sqlpage.hash_password() - never store plain text passwords. SQLPage uses bcrypt with appropriate cost factor.

Secure Cookies

Set secure to TRUE in production and use HTTPS to prevent cookie theft over insecure connections.

Session Expiration

Implement session timeout by checking created_at:

WHERE created_at > datetime('now', '-1 day')

CSRF Protection

SQLPage automatically includes CSRF tokens in forms. Ensure you’re using POST for state-changing operations.

Complete Application Structure

auth-app/
├── sqlpage/
│   └── migrations/
│       └── 0000_init.sql           # User and session tables
├── index.sql                       # Home page with dynamic menu
├── signup.sql                      # Signup form
├── signin.sql                      # Login form
├── login.sql                       # Password verification
├── create_user.sql                 # User creation handler
├── create_user_welcome_message.sql # Success message
├── protected_page.sql              # Example protected page
└── logout.sql                      # Logout handler

Session Management Patterns

Basic Session Check
With Expiration
Reusable Component

SELECT 'redirect' AS component, 'signin.sql' AS link
WHERE sqlpage.cookie('session') NOT IN (
    SELECT id FROM login_session
);

SELECT 'redirect' AS component, 'signin.sql' AS link
WHERE NOT EXISTS (
    SELECT 1 FROM login_session
    WHERE id = sqlpage.cookie('session')
    AND created_at > datetime('now', '-7 days')
);

Create check_auth.sql:

SELECT 'redirect' AS component, 'signin.sql' AS link
WHERE NOT EXISTS (
    SELECT 1 FROM login_session
    WHERE id = sqlpage.cookie('session')
);

Then include it in protected pages:

SELECT 'dynamic' AS component,
    sqlpage.run_sql('check_auth.sql') AS properties;

Production Deployment Checklist

Enable HTTPS

Always use HTTPS in production. Set secure: TRUE for cookies.

Strong Password Policy

Enforce minimum password requirements with regex patterns in your forms.

Rate Limiting

Implement rate limiting for login attempts to prevent brute force attacks.

Session Cleanup

Regularly clean up expired sessions from the database:

DELETE FROM login_session 
WHERE created_at < datetime('now', '-30 days');

Audit Logging

Log authentication events for security monitoring.

Advanced Authentication Topics

OAuth/SSO Integration

Follow the progress on OAuth and Single Sign-On support

Multi-Factor Authentication

Implement 2FA by storing and verifying TOTP codes

Password Reset Flow

Build password reset with email verification using sqlpage.exec()

Remember Me

Extend session duration with long-lived tokens

Complete Example

The full working example with Docker setup is available in the SQLPage repository.

Examples

User Authentication

What You’ll Build

Database Schema

How Authentication Works

Creating Protected Pages

Authentication Check Pattern

Implementing Logout

Dynamic Navigation Based on Auth State

Security Best Practices

Password Hashing

Secure Cookies

Session Expiration

CSRF Protection

Complete Application Structure

Session Management Patterns

Production Deployment Checklist

Advanced Authentication Topics

OAuth/SSO Integration

Multi-Factor Authentication

Password Reset Flow

Remember Me

Complete Example

Further Reading

Build docs developers (and LLMs) love

Examples

​What You’ll Build

​Database Schema

​Building the Signup Form

​Implementing Login

​How Authentication Works

​Creating Protected Pages

​Authentication Check Pattern

​Implementing Logout

​Dynamic Navigation Based on Auth State

​Security Best Practices

Password Hashing

Secure Cookies

Session Expiration

CSRF Protection

​Complete Application Structure

​Session Management Patterns

​Production Deployment Checklist

​Advanced Authentication Topics

OAuth/SSO Integration

Multi-Factor Authentication

Password Reset Flow

Remember Me

​Complete Example

​Further Reading

Build docs developers (and LLMs) love

What You’ll Build

Database Schema

Building the Signup Form

Implementing Login

How Authentication Works

Creating Protected Pages

Authentication Check Pattern

Implementing Logout

Dynamic Navigation Based on Auth State

Security Best Practices

Complete Application Structure

Session Management Patterns

Production Deployment Checklist

Advanced Authentication Topics

Complete Example

Further Reading