| Goal | This query identifies SQL and PL/pgSQL authentication routines that are vulnerable to user enumeration via timing attacks. When verifying user credentials, if a routine returns FALSE immediately upon determining that an account does not exist, an attacker can measure execution latency to confirm valid usernames. Because cryptographic password hashing (such as crypt() using bcrypt) is intentionally slow, hashing only for existing users creates an observable timing discrepancy. The query inspects boolean routines handling credentials (accepting 2 or 3 parameters) and flags those that fail to implement a dummy hash fallback—such as pairing a static bcrypt hash with COALESCE—to ensure uniform, constant-time execution. |
|---|---|
| Type | Problem detection Each row in the result could represent a flaw in the design |
| Reliability | Medium Medium number of false-positive results |
| License | MIT (opens in new tab) |
| Data Source | INFORMATION_SCHEMA+system catalog |
| SQL Query |
|
Fixing Suggestion
To eliminate the timing discrepancy and prevent user enumeration, ensure that the hashing function runs in near-constant time regardless of whether the user exists:
- Define a dummy hash: Store a static, pre-computed bcrypt dummy hash within the routine (e.g., '$2a$10$abcdefghijklmnopqrstuu...').
- Apply COALESCE: When querying the stored password, use COALESCE(stored_hash, dummy_hash) so that a valid hash string is always passed to the hashing function.
- Hash unconditionally: Always execute crypt(input_password, resolved_hash), ensuring that the expensive cryptographic operation executes on every authentication attempt.
- Return combined result: Return TRUE only if the user actually exists AND the computed hash matches (e.g., RETURN user_exists AND (stored_hash = crypt(input_password, stored_hash));).
Collections
This query belongs to the following collections:
Find problems automatically
Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .
| Name | Description |
|---|---|
| Find problems automatically | Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not . |
Categories
This query is classified under the following categories:
Result quality depends on names
Queries of this category use names (for instance, column names) to try to guess the meaning of a database object. Thus, the goodness of names determines the number of false positive and false negative results.
Security
Queries of this category provide information about the security measures.
User-defined routines
Queries of this category provide information about the user-defined routines
| Name | Description |
|---|---|
| Result quality depends on names | Queries of this category use names (for instance, column names) to try to guess the meaning of a database object. Thus, the goodness of names determines the number of false positive and false negative results. |
| Security | Queries of this category provide information about the security measures. |
| User-defined routines | Queries of this category provide information about the user-defined routines |
Further reading and related materials: