How to Securely Store and Validate API Tokens in a Database

A startup had its entire customer database exfiltrated through a SQL injection vulnerability. The attacker didn't crack any passwords — bcrypt held up fine. But every API token was stored in plaintext, so they walked out with full programmatic access to every account. The breach notifications went out, the tokens were rotated, but the damage was done. This is not a hypothetical.

Store a SHA-256 hash of the token, never the token itself. Present the raw token to the user exactly once, validate future requests by hashing the incoming value and comparing it to what's in the database, and you've just made a stolen database dump worthless for token-based access.

Why Tokens Aren't Passwords (But Should Be Treated Like Them)

The standard advice is "hash passwords with bcrypt/argon2." Developers internalize that and then store API tokens as VARCHAR(64) with no second thought. The reasoning is usually: "tokens aren't user-chosen, so they're already high entropy — no need to hash."

That logic is backwards. High entropy means you don't need a slow hashing algorithm (bcrypt's cost factor exists to slow down brute-force on weak passwords). But you absolutely still need to hash, because the threat model isn't brute force — it's database exposure. A plaintext token in a leaked row is immediately usable. A SHA-256 hash of a 256-bit random token is not reversible in any practical sense.

Use SHA-256 (or SHA-3) rather than bcrypt for tokens. Fast is fine here because the token itself is the entropy — an attacker can't brute-force a 32-byte random value. Using bcrypt adds latency to every API request with zero security benefit for high-entropy tokens.

The Token Lifecycle

Generation: Use a cryptographically secure random source. 32 bytes (256 bits) of randomness, base64url-encoded, gives you a token like v3rY-l0ng-r4nd0m-str1ng. Never use Math.random(), rand(), or anything seeded by time.

Storage: Hash the raw token with SHA-256. Store the hash, plus a prefix or identifier so you can look it up without scanning the whole table. A common pattern is to store the first 8 characters of the token as a token_prefix column (indexed) alongside the token_hash column.

Delivery: Show the raw token to the user once — in your API response or a UI modal. Make clear it won't be shown again. If they lose it, they generate a new one.

Validation: On each request, take the incoming token, split off the prefix to find the right row, hash the full token, and do a constant-time comparison against the stored hash.

Rotation/Revocation: Deleting or deactivating a row immediately invalidates the token. No cryptographic ceremony required.

Schema Design

CREATE TABLE api_tokens (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  name        TEXT NOT NULL,                  -- "Production key", "CI token", etc.
  token_prefix VARCHAR(8) NOT NULL,           -- first 8 chars, for lookup
  token_hash  VARCHAR(64) NOT NULL,           -- SHA-256 hex digest
  last_used_at TIMESTAMPTZ,
  expires_at  TIMESTAMPTZ,
  created_at  TIMESTAMPTZ DEFAULT NOW()
);
 
CREATE INDEX idx_api_tokens_prefix ON api_tokens(token_prefix);

The token_prefix index means you're doing a cheap indexed lookup to narrow candidates before the hash comparison, rather than a full-table scan or needing to hash and compare every row.

Implementation

import crypto from 'crypto';

// Generation
function generateApiToken() {
const raw = crypto.randomBytes(32).toString('base64url'); // ~43 chars
const prefix = raw.slice(0, 8);
const hash = crypto.createHash('sha256').update(raw).digest('hex');
return { raw, prefix, hash };
}

// Validation
function validateApiToken(incomingToken, storedHash) {
const incomingHash = crypto.createHash('sha256').update(incomingToken).digest('hex');
// timingSafeEqual prevents timing attacks
return crypto.timingSafeEqual(
  Buffer.from(incomingHash, 'hex'),
  Buffer.from(storedHash, 'hex')
);
}

// Usage
const { raw, prefix, hash } = generateApiToken();
// Store prefix + hash in DB, return raw to user once

// On each request:
// 1. Extract prefix from incoming token (first 8 chars)
// 2. SELECT * FROM api_tokens WHERE token_prefix = prefix AND expires_at > NOW()
// 3. Call validateApiToken(incomingToken, row.token_hash)

The Gotcha Most Developers Miss

The token_prefix lookup narrows candidates, but your validation query must still compare the hash. If you accidentally write:

SELECT * FROM api_tokens WHERE token_prefix = $1 AND token_hash = $2

...you've moved the comparison into SQL, which is fine — but you're now doing string equality in the database rather than a constant-time comparison in your application. For most threat models this is acceptable, but if your database logs slow queries or has query logging enabled, the hash value could appear in logs. Keep the hash comparison in application code where you control the logging.

Also: the prefix must not be the entire secret. Eight characters from a base64url string is only ~48 bits, which is reversible if an attacker knows the prefix format and can enumerate. The prefix is only for database lookup — the full 256-bit hash is what provides security.

A subtler issue: if you return the token hash (not the raw token) in any API response for debugging purposes, you've just made prefix lookups trivial for an attacker who can read your responses. The hash is internal data. Treat it like a password hash.

Expiry and Rotation

Always store expires_at. Default to something sensible — 90 days for user-generated tokens, no expiry for machine-to-machine tokens that have a rotation mechanism. Filter expired tokens in your lookup query, not after fetching:

SELECT * FROM api_tokens
WHERE token_prefix = $1
  AND (expires_at IS NULL OR expires_at > NOW())
LIMIT 1;

This prevents timing-based enumeration where an attacker can tell the difference between "token not found" and "token expired."

Audit Logging

Update last_used_at on every successful validation. This costs one extra write per request, but it's the only reliable way to identify stale tokens you can safely revoke. A token that hasn't been used in 180 days is almost certainly safe to expire. Without last_used_at, you're guessing.

Generate a cryptographically secure token right now using the EncryptCodec Random Secret Generator, then implement the storage pattern above — your next database dump won't give an attacker anything usable.