Returns the username from the HTTP Basic Authentication header of the request.
Signature
sqlpage.basic_auth_username() -> TEXT
Parameters
This function takes no parameters.
Return Value
The username from the Basic Auth header
Description
The basic_auth_username() function extracts the username from the HTTP Basic Authentication header. This is a simple authentication mechanism built into HTTP where credentials are sent with each request.
When called without a valid Basic Auth header, it triggers the browser’s built-in authentication dialog.
Examples
Simple Authentication
Authenticate users with Basic Auth:
-- This will prompt for username/password if not already provided
SET username = sqlpage.basic_auth_username();
SET password = sqlpage.basic_auth_password();
-- Verify credentials against database
SET user_id = (
SELECT id
FROM users
WHERE username = $username
AND password_hash = crypt($password, password_hash)
);
SELECT 'redirect' as component,
'/unauthorized.sql' as link
WHERE $user_id IS NULL;
-- Show protected content
SELECT 'text' as component,
'Welcome, ' || $username || '!' as contents;
Using the Authentication Component
The recommended way to verify Basic Auth credentials:
SELECT 'authentication' AS component,
(SELECT password_hash FROM users WHERE username = sqlpage.basic_auth_username()) AS password_hash,
sqlpage.basic_auth_password() AS password;
-- If we reach here, authentication succeeded
SELECT 'text' as component,
'Welcome, ' || sqlpage.basic_auth_username() || '!' as contents;
Role-Based Access Control
Check user permissions after authentication:
SET username = sqlpage.basic_auth_username();
-- Get user role
SET user_role = (
SELECT role
FROM users
WHERE username = $username
);
-- Restrict to admins only
SELECT 'redirect' as component,
'/forbidden.sql' as link
WHERE $user_role != 'admin';
-- Admin-only content here
SELECT 'text' as component, 'Admin Dashboard' as title;
Audit Logging
Log authenticated user actions:
SET username = sqlpage.basic_auth_username();
INSERT INTO audit_log (username, action, ip_address, timestamp)
VALUES (
$username,
'viewed_sensitive_data',
sqlpage.client_ip(),
CURRENT_TIMESTAMP
);
SELECT 'table' as component;
SELECT * FROM sensitive_data;
Per-User Content
Show different content based on authenticated user:
SET username = sqlpage.basic_auth_username();
SELECT 'list' as component,
'Your Documents' as title;
SELECT
filename as title,
created_at as description,
'/download.sql?file=' || id as link
FROM documents
WHERE owner = $username
ORDER BY created_at DESC;
How Basic Auth Works
- Client makes a request without credentials
- Server responds with
401 Unauthorized and WWW-Authenticate: Basic header
- Browser shows login dialog
- User enters username and password
- Browser sends credentials in
Authorization header (base64-encoded)
- Server validates and grants access
Authentication Flow
-- First request (no auth)
sqlpage.basic_auth_username()
→ Returns 401 Unauthorized
→ Browser shows login dialog
-- Second request (with credentials)
Authorization: Basic dXNlcm5hbWU6cGFzc3dvcmQ=
→ sqlpage.basic_auth_username() returns "username"
→ sqlpage.basic_auth_password() returns "password"
Security Considerations
Always Use HTTPS
Basic Auth sends credentials with every request. Without HTTPS, credentials are transmitted in plaintext (just base64-encoded).
-- Check if connection is secure
SELECT 'redirect' as component,
'https://' || sqlpage.header('host') || sqlpage.path() as link
WHERE sqlpage.protocol() != 'https';
SET username = sqlpage.basic_auth_username();
Hash Passwords
Never store passwords in plaintext:
-- When creating a user
INSERT INTO users (username, password_hash)
VALUES (
:username,
sqlpage.hash_password(:password)
);
-- When authenticating
SELECT 'authentication' AS component,
(SELECT password_hash FROM users WHERE username = sqlpage.basic_auth_username()) AS password_hash,
sqlpage.basic_auth_password() AS password;
Rate Limiting
Protect against brute-force attacks:
SET username = sqlpage.basic_auth_username();
SET ip = sqlpage.client_ip();
-- Check failed login attempts
SET recent_failures = (
SELECT COUNT(*)
FROM login_attempts
WHERE username = $username
AND ip_address = $ip
AND success = FALSE
AND attempted_at > datetime('now', '-15 minutes')
);
SELECT 'redirect' as component,
'/rate-limited.sql' as link
WHERE $recent_failures > 5;
Limitations of Basic Auth
- No logout - Browsers cache credentials until closed
- Sent with every request - Performance and security overhead
- No expiration - Credentials valid indefinitely
- Limited customization - Browser’s default login UI only
- Vulnerable without HTTPS - Credentials easily intercepted
Alternatives to Basic Auth
For production applications, consider:
- Session-based auth with cookies
- Token-based auth (JWT)
- OAuth 2.0 for third-party integration
- OIDC Single Sign-On with sqlpage.user_info()
See Also