SQL Parameters

SQLPage allows you to access HTTP request data directly in your SQL queries through SQL parameters. This page explains how parameters work and the different types available.

Parameter Types

SQLPage supports three parameter syntaxes:

$param

GET or SET variables

WHERE id = $user_id

Accesses URL parameters or SET variables

:param

POST or SET variables

VALUES (:username, :email)

Accesses form data or SET variables

?param

GET only

WHERE category = ?filter

Accesses URL parameters exclusively

Parameter Resolution Order

$param (GET or SET)
:param (POST or SET)
?param (GET only)

SELECT $user_id as id;

Resolution order:

Check SET variables (from SET statements)
Check URL query parameters
Return NULL if not found

Example:

GET /page.sql?user_id=42

-- $user_id = '42'

INSERT INTO users (name) VALUES (:name);

Resolution order:

Check SET variables
Check POST/form data
Return NULL if not found

Example:

POST /page.sql
Content-Type: application/x-www-form-urlencoded

name=Alice&email=alice@example.com

-- :name = 'Alice'
-- :email = 'alice@example.com'

SELECT * FROM products WHERE category = ?cat;

Resolution order:

Check URL query parameters only
Return NULL if not found
Never checks SET or POST variables

Example:

GET /products.sql?cat=electronics

-- ?cat = 'electronics'

Deprecation Notice: Using $param to access POST variables is deprecated. Use :param explicitly for form data.

How Parameter Extraction Works

SQLPage uses sqlparser-rs to parse SQL and extract parameters:

Parsing Example

Original SQL
After Parsing
Database-Specific

SELECT 
    name,
    email
FROM users
WHERE id = $user_id
  AND status = :status
  AND role IN ($role1, $role2);

Prepared Statement (PostgreSQL):

SELECT 
    name,
    email  
FROM users
WHERE id = $1
  AND status = $2
  AND role IN ($1, $3);

Parameter Mapping:

$1 ← $user_id (GET parameter)
$2 ← :status (POST parameter)
$3 ← $role2 (GET parameter)

$role1 reuses $1 because it has the same source ($user_id).

Different databases use different placeholder syntax:

Database	Placeholder	Example
PostgreSQL	`$1`, `$2`	`WHERE id = $1`
MySQL	`?`, `?`	`WHERE id = ?`
SQL Server	`@p1`, `@p2`	`WHERE id = @p1`
SQLite	`?1`, `?2`	`WHERE id = ?1`

SQLPage automatically translates to the correct syntax.

Common Parameter Patterns

URL Query Parameters

users.sql

-- URL: /users.sql?id=42&role=admin

SELECT 'card' as component;

SELECT 
    name as title,
    email as description
FROM users
WHERE id = $id        -- Gets '42'
  AND role = $role;   -- Gets 'admin'

Form Submissions

create_user.sql

-- Display form
SELECT 'form' as component,
       'Create User' as title,
       'create_user.sql' as action;

SELECT 'text' as type, 'username' as name;
SELECT 'email' as type, 'email' as name;

-- Process form submission
INSERT INTO users (username, email, created_at)
SELECT :username, :email, CURRENT_TIMESTAMP
WHERE :username IS NOT NULL;

-- Redirect after insert
SELECT 'redirect' as component,
       '/users.sql' as link
WHERE :username IS NOT NULL;

Conditional Logic

products.sql

SELECT 'table' as component;

SELECT 
    product_name,
    price,
    category
FROM products
WHERE 
    ($category IS NULL OR category = $category)
    AND ($min_price IS NULL OR price >= CAST($min_price AS DECIMAL))
    AND ($max_price IS NULL OR price <= CAST($max_price AS DECIMAL))
    AND ($search IS NULL OR product_name LIKE '%' || $search || '%');

Use $param IS NULL to make filters optional. When the parameter isn’t provided, the condition is ignored.

SET Variables

Store query results in variables for later use:

Basic SET
Computed Values
Conditional Assignment

-- Store a value
SET user_name = SELECT name FROM users WHERE id = $id;

-- Use the variable
SELECT 'text' as component,
       'Welcome, ' || $user_name as contents;

-- Compute a value
SET total_sales = SELECT SUM(amount) FROM orders;
SET avg_sale = SELECT AVG(amount) FROM orders;

-- Use in multiple places
SELECT 'card' as component;
SELECT 'Total Sales' as title, $total_sales as value;
SELECT 'Average Sale' as title, $avg_sale as value;

-- Set based on condition
SET discount = 
    CASE 
        WHEN :customer_type = 'premium' THEN 0.20
        WHEN :customer_type = 'regular' THEN 0.10
        ELSE 0.05
    END;

-- Apply discount
SELECT 
    product_name,
    price,
    price * (1 - $discount) as discounted_price
FROM products;

SET vs Parameters

Feature	GET/POST Parameters	SET Variables
Source	HTTP request	Query results
Scope	Entire request	After SET statement
Mutability	Read-only	Can be overwritten
Type	Always string	Preserves SQL type

SQLPage Functions

Access request metadata and utilities:

Request Information

SELECT 
    sqlpage.request_method() as method,
    sqlpage.path() as path,
    sqlpage.protocol() as protocol,
    sqlpage.header('User-Agent') as user_agent;

Access HTTP request details:

sqlpage.request_method() - GET, POST, PUT, DELETE
sqlpage.path() - Current URL path
sqlpage.header(name) - HTTP header value

User & Session

SELECT 
    sqlpage.cookie('session_id') as session,
    sqlpage.basic_auth_username() as username,
    sqlpage.basic_auth_password() as password;

User identification:

sqlpage.cookie(name) - Cookie value
sqlpage.basic_auth_username() - HTTP Basic Auth username
sqlpage.basic_auth_password() - HTTP Basic Auth password

File Handling

-- Read uploaded file
SET file_contents = sqlpage.uploaded_file_path('avatar');

-- Read file from disk
SET config = sqlpage.read_file_as_text('config.json');

-- Save uploaded file
SELECT sqlpage.exec('mv', 
    sqlpage.uploaded_file_path('document'),
    '/uploads/' || :filename
);

File operations:

sqlpage.uploaded_file_path(name) - Temporary path to uploaded file
sqlpage.read_file_as_text(path) - Read file contents
sqlpage.read_file_as_data_url(path) - Read as data URL

Hashing & Encoding

-- Hash password
SET password_hash = sqlpage.hash_password(:password);

-- Generate random token
SET session_token = sqlpage.random_string(32);

-- URL encode
SET encoded = sqlpage.url_encode($user_input);

Security functions:

sqlpage.hash_password(text) - Bcrypt password hash
sqlpage.random_string(length) - Cryptographically secure random string
sqlpage.url_encode(text) - URL-safe encoding

Parameter Safety & SQL Injection

SQLPage automatically protects against SQL injection:

Safe (Parameterized)
Unsafe (String Concatenation)
Dynamic Column Names

-- ✅ SAFE: Parameters are escaped
SELECT * FROM users WHERE id = $id;

-- Even if $id = "1 OR 1=1", query becomes:
SELECT * FROM users WHERE id = '1 OR 1=1';
-- (No injection possible)

SQLPage uses prepared statements, so parameters are always escaped.

-- ❌ UNSAFE: Don't do this!
SET query = 'SELECT * FROM users WHERE id = ' || $id;

-- If $id = "1 OR 1=1", this executes:
SELECT * FROM users WHERE id = 1 OR 1=1;
-- (Returns all users!)

Never concatenate user input into SQL strings. Always use parameterized queries.

-- ❌ Cannot parameterize column names
SELECT * FROM users ORDER BY $sort_column;
-- This won't work as expected

-- ✅ Use CASE for safe dynamic sorting
SELECT * FROM users
ORDER BY 
    CASE $sort_column
        WHEN 'name' THEN name
        WHEN 'email' THEN email
        WHEN 'created_at' THEN created_at
        ELSE id
    END;

Type Casting

Parameters are strings by default. Cast them to the appropriate type:

-- Numeric comparisons
WHERE price > CAST($min_price AS DECIMAL)
  AND quantity >= CAST($min_qty AS INTEGER);

-- Date comparisons  
WHERE created_at >= CAST($start_date AS DATE)
  AND created_at <= CAST($end_date AS DATE);

-- Boolean (database-specific)
WHERE active = CAST($is_active AS BOOLEAN);  -- PostgreSQL
WHERE active = CASE WHEN $is_active = 'true' THEN 1 ELSE 0 END;  -- SQLite/MySQL

SQLPage casts parameters to TEXT/VARCHAR when binding to the database. Explicit casting ensures correct comparisons.

Array Parameters

Handle multi-value parameters from checkboxes or multi-selects:

HTML Form
Processing Arrays

SELECT 'form' as component;

SELECT 'checkbox' as type,
       'tags[]' as name,
       tag_id as value,
       tag_name as label
FROM tags;

-- :tags returns JSON array: ["1", "3", "5"]

-- Option 1: JSON parsing (PostgreSQL)
SELECT * FROM posts
WHERE tag_id = ANY(
    SELECT jsonb_array_elements_text(:tags::jsonb)::INTEGER
);

-- Option 2: String matching (works on all DBs)
SELECT * FROM posts  
WHERE ',' || :tags || ',' LIKE '%,' || tag_id || ',%';

-- Option 3: Insert multiple rows
INSERT INTO post_tags (post_id, tag_id)
SELECT $post_id, value
FROM json_each(:tags);

Advanced Patterns

Computed Function Parameters

-- SQLPage functions can take computed values
SET username = sqlpage.header('X-User-Name');
SET user_hash = sqlpage.hash_password(
    COALESCE(:password, sqlpage.random_string(16))
);

Nested Function Calls

-- Functions within functions
SET encoded_json = sqlpage.url_encode(
    json_object(
        'user', :username,
        'token', sqlpage.random_string(32)
    )
);

Conditional Parameter Usage

-- Different queries based on parameter
SELECT 'table' as component;

SELECT * FROM admin_users
WHERE $view = 'admin'

UNION ALL

SELECT * FROM regular_users  
WHERE $view != 'admin' OR $view IS NULL;

Best Practices

Always Validate Input

-- Check required parameters
SELECT 'redirect' as component,
       '/error.sql?msg=Missing user ID' as link
WHERE $user_id IS NULL;

-- Validate parameter format
SELECT 'redirect' as component,
       '/error.sql?msg=Invalid email' as link
WHERE :email NOT LIKE '%@%';

Use Appropriate Parameter Type

$param for URL/GET parameters
:param for form/POST data
?param when you specifically need GET-only
SET variables for computed values

Handle NULL Values

-- Provide defaults
SELECT COALESCE($page_size, 20) as page_size;

-- Optional filters
WHERE ($category IS NULL OR category = $category);

-- Required parameters
WHERE $user_id IS NOT NULL;

Type Safety

-- Always cast when comparing numbers
WHERE price > CAST($min AS DECIMAL);

-- Validate numeric input
WHERE $id ~ '^[0-9]+$'  -- PostgreSQL regex
   OR $id GLOB '[0-9]*' -- SQLite

Next Steps

Components

Learn about component rendering

Functions Reference

Explore all SQLPage functions

Security

Best practices for secure applications

Examples

See parameters in real applications

Get Started

Core Concepts

Configuration

Guides

Parameter Types

$param

:param

?param

Parameter Resolution Order

How Parameter Extraction Works

Parsing Example

Common Parameter Patterns

URL Query Parameters

Form Submissions

Conditional Logic

SET Variables

SET vs Parameters

SQLPage Functions

Parameter Safety & SQL Injection

Type Casting

Array Parameters

Advanced Patterns

Computed Function Parameters

Nested Function Calls

Conditional Parameter Usage

Best Practices

Next Steps

Components

Functions Reference

Security

Examples

Build docs developers (and LLMs) love

Get Started

Core Concepts

Configuration

Guides

​Parameter Types

$param

:param

?param

​Parameter Resolution Order

​How Parameter Extraction Works

​Parsing Example

​Common Parameter Patterns

​URL Query Parameters

​Form Submissions

​Conditional Logic

​SET Variables

​SET vs Parameters

​SQLPage Functions

​Parameter Safety & SQL Injection

​Type Casting

​Array Parameters

​Advanced Patterns

​Computed Function Parameters

​Nested Function Calls

​Conditional Parameter Usage

​Best Practices

​Next Steps

Components

Functions Reference

Security

Examples

Build docs developers (and LLMs) love

Parameter Types

Parameter Resolution Order

How Parameter Extraction Works

Parsing Example

Common Parameter Patterns

URL Query Parameters

Form Submissions

Conditional Logic

SET Variables

SET vs Parameters

SQLPage Functions

Parameter Safety & SQL Injection

Type Casting

Array Parameters

Advanced Patterns

Computed Function Parameters

Nested Function Calls

Conditional Parameter Usage

Best Practices

Next Steps