Skip to main content
Persists an uploaded file to the local filesystem and returns its path.

​
Signature

sqlpage.persist_uploaded_file(
    field_name TEXT,
    destination_folder TEXT DEFAULT 'uploads',
    allowed_extensions TEXT DEFAULT 'jpg,jpeg,png,gif,bmp,webp,pdf,txt,doc,docx,xls,xlsx,csv,mp3,mp4,wav,avi,mov'
) -> TEXT

​
Parameters

​
field_name
TEXT
required
Name of the file input field in the form
​
destination_folder
TEXT
Optional. Folder where the file will be saved, relative to web root. Defaults to 'uploads'
​
allowed_extensions
TEXT
Optional. Comma-separated list of allowed file extensions. Defaults to common safe file types

​
Return Value

​
return
TEXT
The web-accessible path to the saved file (e.g., /uploads/2024-03-02_15h30m45s_a8f3k2x9.jpg), or NULL if no file was uploaded

​
Description

The persist_uploaded_file() function saves an uploaded file to the filesystem with a unique filename and returns its web-accessible path. The file is automatically renamed using a timestamp and random string to prevent conflicts and directory traversal attacks.

​
Examples

​
Basic File Upload

Simple file upload form: 
-- upload_form.sql
SELECT 'form' as component,
    'handle_upload.sql' as action;
SELECT 'file' as type,
    'document' as name,
    'Upload Document' as label;
Handle the upload: 
-- handle_upload.sql
SET file_path = sqlpage.persist_uploaded_file('document');

SELECT 'redirect' as component,
    '/success.sql?file=' || sqlpage.url_encode($file_path) as link;

​
Store File Path in Database

Save file metadata to database: 
SET file_path = sqlpage.persist_uploaded_file('profile_picture', 'avatars', 'jpg,jpeg,png,webp');

UPDATE users
SET profile_picture = $file_path
WHERE id = :user_id;

SELECT 'redirect' as component, '/profile.sql' as link;

​
Custom Upload Directory

Organize uploads by type: 
-- Documents go to 'documents' folder
SET doc_path = sqlpage.persist_uploaded_file('document', 'documents', 'pdf,doc,docx,txt');

-- Images go to 'images' folder
SET img_path = sqlpage.persist_uploaded_file('image', 'images', 'jpg,jpeg,png,gif,webp');

INSERT INTO posts (title, document_url, image_url)
VALUES (:title, $doc_path, $img_path);

​
Restrict File Types

Only allow specific extensions: 
-- Only accept images
SET image_path = sqlpage.persist_uploaded_file(
    'profile_photo',
    'profile_pictures',
    'jpg,jpeg,png,webp'
);

-- Only accept spreadsheets
SET spreadsheet_path = sqlpage.persist_uploaded_file(
    'data_file',
    'data',
    'csv,xlsx,xls'
);

​
Complete Upload Example

Full example with validation and feedback: 
-- Check if file was uploaded
SET uploaded = sqlpage.uploaded_file_path('document') IS NOT NULL;

SELECT 'redirect' as component,
    '/upload_form.sql?error=no_file' as link
WHERE NOT $uploaded;

-- Check file size (example check)
SET file_name = sqlpage.uploaded_file_name('document');

-- Persist the file
SET file_path = sqlpage.persist_uploaded_file(
    'document',
    'uploads',
    'pdf,doc,docx,txt'
);

-- Save to database
INSERT INTO documents (filename, file_path, uploaded_by, uploaded_at)
VALUES (
    $file_name,
    $file_path,
    :user_id,
    CURRENT_TIMESTAMP
);

SELECT 'alert' as component,
    'Success' as title,
    'File uploaded successfully!' as description,
    'green' as color;

​
User Profile Picture

Update user avatar: 
-- Check user is authenticated
SET user_id = (SELECT id FROM sessions WHERE token = sqlpage.cookie('session'));

SELECT 'redirect' as component, '/login.sql' as link
WHERE $user_id IS NULL;

-- Delete old profile picture if exists
SET old_picture = (SELECT profile_picture FROM users WHERE id = $user_id);
-- Note: You'd need to implement file deletion separately

-- Save new profile picture
UPDATE users
SET profile_picture = sqlpage.persist_uploaded_file(
    'profile_picture',
    'profile_pictures',
    'jpg,jpeg,png,gif,webp'
),
    updated_at = CURRENT_TIMESTAMP
WHERE id = $user_id;

SELECT 'redirect' as component, '/profile.sql' as link;

​
Filename Generation

Files are automatically renamed to prevent conflicts: 
Original: "My Document.pdf"
Saved as: "2024-03-02_15h30m45s_a8f3k2x9.pdf"
           │                   │        │
           │                   │        └─ Original extension
           │                   └───────── Random 8-char string
           └───────────────────── Timestamp
Benefits:
  • No filename conflicts
  • Prevents directory traversal attacks
  • Preserves original file extension
  • Sortable by upload time

​
File Path Resolution

​
Folder Creation

The destination folder is created automatically if it doesn’t exist: 
-- If 'uploads/documents' doesn't exist, it will be created
SET path = sqlpage.persist_uploaded_file('doc', 'uploads/documents', 'pdf');

​
Relative to Web Root

Paths are relative to the web root directory: 
Web root: /var/www/myapp
Destination: "uploads"
Full path: /var/www/myapp/uploads/2024-03-02_15h30m45s_a8f3k2x9.pdf
Returned: /uploads/2024-03-02_15h30m45s_a8f3k2x9.pdf

​
Web-Accessible Paths

The returned path is web-accessible: 
SET file_path = sqlpage.persist_uploaded_file('image', 'images', 'jpg,png');
-- Returns: /images/2024-03-02_15h30m45s_a8f3k2x9.jpg

-- Use directly in img tag
SELECT 'text' as component,
    '<img src="' || $file_path || '">' as html;

-- Or in card component
SELECT 'card' as component;
SELECT 'My Image' as title, $file_path as top_image;

​
Default Allowed Extensions

By default, these extensions are allowed: Images: jpg, jpeg, png, gif, bmp, webp Documents: pdf, txt, doc, docx, xls, xlsx, csv Media: mp3, mp4, wav, avi, mov

​
Security Considerations

​
Never Allow Dangerous Extensions

CRITICAL: Never allow these extensions: 
-- DANGEROUS - Never do this!
SET path = sqlpage.persist_uploaded_file('file', 'uploads', 'sql,html,svg,js,php,exe');
Dangerous extensions:
  • sql - Can execute SQL queries
  • html, svg - Can execute JavaScript (XSS)
  • js, php, py - Executable code
  • exe, bat, sh - Executable programs

​
Validate File Contents

Extension checking is not enough - validate file contents: 
-- Check MIME type
SET mime_type = sqlpage.uploaded_file_mime_type('image');

SELECT 'redirect' as component,
    '/error.sql?msg=invalid_type' as link
WHERE $mime_type NOT LIKE 'image/%';

SET file_path = sqlpage.persist_uploaded_file('image', 'images', 'jpg,png');

​
Enforce Size Limits

Prevent large file uploads: 
-- Check file size before persisting
-- (Implementation depends on your setup)
SELECT 'redirect' as component,
    '/error.sql?msg=file_too_large' as link
WHERE LENGTH(sqlpage.uploaded_file_path('document')) > 10485760;  -- 10 MB

SET file_path = sqlpage.persist_uploaded_file('document');

​
Rate Limiting

Limit uploads per user: 
SET recent_uploads = (
    SELECT COUNT(*) FROM uploads
    WHERE user_id = :user_id
      AND uploaded_at > datetime('now', '-1 hour')
);

SELECT 'redirect' as component,
    '/error.sql?msg=rate_limit' as link
WHERE $recent_uploads >= 10;

SET file_path = sqlpage.persist_uploaded_file('file');

​
Error Handling

​
No File Uploaded

Returns NULL when field is empty: 
SET file_path = sqlpage.persist_uploaded_file('document');

SELECT 'redirect' as component,
    '/upload_form.sql?error=no_file' as link
WHERE $file_path IS NULL;

​
Invalid Extension

Throws an error for disallowed extensions: 
-- This will error if user uploads a .exe file
SET path = sqlpage.persist_uploaded_file('file', 'uploads', 'jpg,png,pdf');

​
Directory Permissions

Ensure the web server has write permissions: 
# On Linux
sudo chown -R www-data:www-data /var/www/myapp/uploads
sudo chmod -R 755 /var/www/myapp/uploads

​
Displaying Uploaded Files

​
Show Uploaded Image

SELECT 'card' as component;
SELECT 
    name as title,
    image_path as top_image,
    description
FROM products
WHERE image_path IS NOT NULL;

SELECT 'button' as component;
SELECT 
    'Download ' || filename as title,
    file_path as link,
    'download' as icon
FROM documents;

SELECT 'card' as component,
    3 as columns;

SELECT 
    filename as title,
    image_path as top_image,
    uploaded_at as description
FROM images
ORDER BY uploaded_at DESC;

​
File Management

​
Track Uploads in Database

CREATE TABLE uploaded_files (
    id INTEGER PRIMARY KEY,
    filename TEXT NOT NULL,
    file_path TEXT NOT NULL,
    file_size INTEGER,
    mime_type TEXT,
    uploaded_by INTEGER,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Store metadata
INSERT INTO uploaded_files (filename, file_path, mime_type, uploaded_by)
VALUES (
    sqlpage.uploaded_file_name('document'),
    sqlpage.persist_uploaded_file('document'),
    sqlpage.uploaded_file_mime_type('document'),
    :user_id
);

​
Clean Up Old Files

Regularly remove old uploads: 
-- Mark old files for deletion
UPDATE uploaded_files
SET deleted = TRUE
WHERE uploaded_at < datetime('now', '-90 days');

-- Use a cron job to actually delete files from disk

​
See Also

Build docs developers (and LLMs) love

Get started for freeTalk to us