Returns the password from the HTTP Basic Authentication header of the request.
Signature
sqlpage.basic_auth_password() -> TEXT
Parameters
This function takes no parameters.
Return Value
The password from the Basic Auth header
Description
The basic_auth_password() function extracts the password from the HTTP Basic Authentication header. It should always be used together with sqlpage.basic_auth_username() to verify user credentials.
Security Warning: Never log or store passwords in plaintext. Always hash them using sqlpage.hash_password().
Examples
Verify User Credentials
The recommended way to authenticate using Basic Auth:
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 authentication succeeds, show protected content
SELECT 'text' as component,
'Successfully authenticated!' as contents;
Manual Password Verification
Manually verify the password against a hash:
SET username = sqlpage.basic_auth_username();
SET password = sqlpage.basic_auth_password();
-- Get stored hash
SET stored_hash = (SELECT password_hash FROM users WHERE username = $username);
-- Verify password (using PostgreSQL's crypt)
SET is_valid = ($stored_hash = crypt($password, $stored_hash));
SELECT 'redirect' as component,
'/login-failed.sql' as link
WHERE NOT $is_valid;
-- User is authenticated
SELECT 'text' as component, 'Welcome!' as contents;
Create New User Account
Hash the password when creating a user:
-- Get credentials from Basic Auth
SET username = sqlpage.basic_auth_username();
SET password = sqlpage.basic_auth_password();
-- Check if user already exists
SET user_exists = (SELECT COUNT(*) FROM users WHERE username = $username) > 0;
SELECT 'redirect' as component,
'/error.sql?msg=user_exists' as link
WHERE $user_exists;
-- Create new user with hashed password
INSERT INTO users (username, password_hash, created_at)
VALUES (
$username,
sqlpage.hash_password($password),
CURRENT_TIMESTAMP
);
SELECT 'redirect' as component,
'/welcome.sql' as link;
Password Reset
Allow users to change their password:
SET username = sqlpage.basic_auth_username();
SET old_password = sqlpage.basic_auth_password();
SET new_password = :new_password;
-- Verify old password first
SELECT 'authentication' AS component,
(SELECT password_hash FROM users WHERE username = $username) AS password_hash,
$old_password AS password;
-- Update to new password
UPDATE users
SET password_hash = sqlpage.hash_password($new_password),
password_changed_at = CURRENT_TIMESTAMP
WHERE username = $username;
SELECT 'redirect' as component,
'/password-changed.sql' as link;
Log Failed Authentication Attempts
Track failed login attempts for security:
SET username = sqlpage.basic_auth_username();
SET password = sqlpage.basic_auth_password();
SET stored_hash = (SELECT password_hash FROM users WHERE username = $username);
SET password_valid = ($stored_hash IS NOT NULL AND $stored_hash = crypt($password, $stored_hash));
-- Log the attempt
INSERT INTO login_attempts (username, ip_address, success, attempted_at)
VALUES (
$username,
sqlpage.client_ip(),
$password_valid,
CURRENT_TIMESTAMP
);
-- Reject if invalid
SELECT 'redirect' as component,
'/login-failed.sql' as link
WHERE NOT $password_valid;
Security Best Practices
1. Always Use HTTPS
Basic Auth sends credentials with every request. Without HTTPS, passwords are easily intercepted:
-- Redirect HTTP to HTTPS
SELECT 'redirect' as component,
'https://' || sqlpage.header('host') || sqlpage.path() as link
WHERE sqlpage.protocol() != 'https';
2. Never Store Plaintext Passwords
-- WRONG - Never do this!
INSERT INTO users (username, password)
VALUES (:username, :password);
-- CORRECT - Always hash passwords
INSERT INTO users (username, password_hash)
VALUES (
:username,
sqlpage.hash_password(:password)
);
3. Never Log Passwords
-- WRONG - Logs password to database
INSERT INTO activity_log (username, password, action)
VALUES ($username, $password, 'login');
-- CORRECT - Log only non-sensitive info
INSERT INTO activity_log (username, action)
VALUES ($username, 'login');
4. Implement Rate Limiting
Prevent brute-force attacks:
SET username = sqlpage.basic_auth_username();
-- Count recent failed attempts
SET failed_attempts = (
SELECT COUNT(*) FROM login_attempts
WHERE username = $username
AND success = FALSE
AND attempted_at > datetime('now', '-30 minutes')
);
-- Block if too many failures
SELECT 'redirect' as component,
'/account-locked.sql' as link
WHERE $failed_attempts >= 5;
-- Proceed with authentication
SET password = sqlpage.basic_auth_password();
5. Use Strong Password Requirements
Enforce password complexity:
SET password = sqlpage.basic_auth_password();
-- Check password strength
SET is_strong = (
LENGTH($password) >= 8
AND $password GLOB '*[A-Z]*' -- Has uppercase
AND $password GLOB '*[a-z]*' -- Has lowercase
AND $password GLOB '*[0-9]*' -- Has number
);
SELECT 'redirect' as component,
'/weak-password.sql' as link
WHERE NOT $is_strong;
How Basic Auth Works
When you call sqlpage.basic_auth_password():
- SQLPage looks for the
Authorization header
- Extracts the base64-encoded credentials
- Decodes them to get
username:password
- Returns the password portion
Authorization: Basic dXNlcm5hbWU6cGFzc3dvcmQ=
└─────────┬─────────┘
base64("username:password")
Password Hashing
Always hash passwords before storing them:
-- Hash a password
SET hashed = sqlpage.hash_password('secret123');
-- Returns: $argon2id$v=19$m=19456,t=2,p=1$...
-- Store in database
INSERT INTO users (username, password_hash)
VALUES ('alice', $hashed);
-- Verify password later
SELECT 'authentication' AS component,
(SELECT password_hash FROM users WHERE username = 'alice') AS password_hash,
'secret123' AS password;
See Also