Identity Management: Building the Login System

Part 1 laid out the service list, the threat model, and why roll-your-own is the right call at this scale. This part builds it: PHP sessions, MariaDB, bcrypt, CSRF tokens, file-based rate limiting, and a shared role check — the same pattern that runs this site's own sign-in.

The Schema

One table. user_name doubles as the email address — it exists as its own column mainly so a future login method (a username, a passkey label) has somewhere to diverge from the address without a schema change.

CREATE TABLE members (
  member_id      INT NOT NULL AUTO_INCREMENT,
  admin          INT NOT NULL,
  user_name      VARCHAR(256) NOT NULL,   -- the email address
  password_hash  VARCHAR(256) NOT NULL,   -- bcrypt, via password_hash()
  realname       TEXT,
  email          VARCHAR(255),
  signup_ip      VARCHAR(45),
  signup_date    DATETIME,
  blocked        TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (member_id),
  UNIQUE KEY (user_name)
);

No plaintext password column, ever — password_hash is the only credential stored, and it’s never readable back out, only checked with password_verify().

UNIQUE KEY (user_name) matters more than it looks. The Signing Up section below also checks for a duplicate email with a plain SELECT before inserting — but a check-then-insert like that has a race: two signups for the same address, submitted within milliseconds of each other, can both pass the SELECT before either finishes its INSERT. The application-level check exists only to give a fast, friendly “that account already exists” error in the common case; the database constraint is what actually guarantees uniqueness under concurrent requests, by rejecting the second INSERT outright.

The Session Cookie

Every page that touches the session sets the same three cookie flags before session_start() does anything else:

session_start([
  'cookie_httponly' => true,
  'cookie_secure'   => true,
  'cookie_samesite' => 'Lax',
]);

httponly means JavaScript can’t read the cookie, so an XSS bug can’t exfiltrate it. secure means it’s never sent over plain HTTP, so it can’t be sniffed on a network. samesite=Lax means a form on some other site can’t ride the cookie into a state-changing request here. Each flag closes exactly one row from Part 1’s threat model.

CSRF Tokens

Every page that renders a form mints a fresh token into the session and drops it into a hidden field:

$_SESSION['csrf_token'] = bin2hex(random_bytes(32));

<input type="hidden" name="csrf_token"
       value="<?= htmlspecialchars($_SESSION['csrf_token']) ?>">

The handler that receives the POST checks it with hash_equals() — a constant-time comparison, so the check itself can’t leak timing information — and immediately discards it:

$submitted = $_POST['csrf_token'] ?? '';
$session   = $_SESSION['csrf_token'] ?? '';
if (!$session || !hash_equals($session, $submitted)) {
    header('Location: /sign-in.php?error=1');
    exit;
}
unset($_SESSION['csrf_token']);

A plain string comparison (== or ===) would work functionally but exits early on the first mismatched byte — a timing side-channel an attacker could in principle use to guess the token byte-by-byte. hash_equals() always takes the same time regardless of where the strings diverge.

Rate Limiting Without a Database Table

Every submit — signup or login — is capped per IP address before anything else runs, using a small JSON file in the system temp directory instead of a database table:

function rate_limit(string $bucket, string $ip): bool {
    $file   = sys_get_temp_dir() . '/rl_' . md5($bucket . $ip) . '.json';
    $now    = time();
    $window = 900;   // 15 minutes
    $max    = 5;     // attempts per window

    $fh = fopen($file, 'c+');
    flock($fh, LOCK_EX);

    $raw  = stream_get_contents($fh);
    $data = ($raw && ($d = json_decode($raw, true))) ? $d : ['ts' => []];
    $data['ts'] = array_values(array_filter($data['ts'], fn($t) => $t > $now - $window));

    if (count($data['ts']) >= $max) {
        flock($fh, LOCK_UN); fclose($fh);
        return false;
    }
    $data['ts'][] = $now;
    ftruncate($fh, 0); rewind($fh); fwrite($fh, json_encode($data));
    flock($fh, LOCK_UN); fclose($fh);
    @chmod($file, 0600);
    return true;
}

Signup uses rate_limit('signup', $ip), login uses rate_limit('login', $ip) — same function, different bucket key, so a flood of signup attempts doesn’t also lock out real logins from the same address. The flock() call matters: without it, two near-simultaneous requests can both read the file before either writes back, and the limiter undercounts.

Be honest about the tradeoff: this lives on one server’s local disk. It doesn’t survive a temp-directory cleanup, doesn’t work if you ever run more than one web server behind a load balancer, and it’s per-IP only — not per-account. That's a fine trade for a single small VPS. If you outgrow one box, move the counter into a database table (or Redis) keyed the same way; the calling code above doesn’t change.

Signing Up

After the CSRF check and the rate limiter both pass, signup validates the submitted fields, hashes the password, and checks for a duplicate account — all with prepared statements, since this is user-controlled input:

if (strlen($password) < 8) { /* reject */ }
if (!hash_equals($password, $password2)) { /* reject: didn't match */ }

$check = $hDB->prepare('SELECT member_id FROM members WHERE user_name = ?');
mysqli_stmt_bind_param($check, 's', $email);
$hDB->execute($check);
$check->store_result();
if ($check->num_rows > 0) { /* reject: exists */ }

$pwhash = password_hash($password, PASSWORD_DEFAULT);

PASSWORD_DEFAULT currently means bcrypt. It’s deliberately not pinned to a specific algorithm — PHP will move the default forward as bcrypt ages out, and password_verify() keeps checking whichever algorithm a given hash was made with, so old hashes don’t break when the default changes.

Because the SELECT check above can lose its race, the insert itself still needs to handle rejection: if the UNIQUE KEY on user_name fires anyway, treat that failure exactly like the “account already exists” case, rather than surfacing it as a generic database error.

On success, the insert is followed immediately by establishing the session — no separate email-confirmation step. That’s a deliberate simplification: the rate limiter and the duplicate-email check already remove most of the incentive to mass-create throwaway accounts, and this site's admin can still delete an obviously fake signup by hand. If you're taking public signups at real scale, add a verification token column and an unverified/verified flag before the account gets any privileges — it's a small addition on top of this schema, not a redesign.

session_regenerate_id(true);
$_SESSION['signed_in']  = true;
$_SESSION['email']      = $email;
$_SESSION['member_id']  = $member_id;
$_SESSION['admin']      = 0;
$_SESSION['csrf_token'] = bin2hex(random_bytes(32));

session_regenerate_id(true) issues a brand-new session ID and destroys the old one — the direct defense against session fixation from Part 1’s threat model. Any session ID that existed before this line, planted by an attacker or not, is now worthless.

Bootstrapping the First Admin

Notice that $_SESSION['admin'] above is hardcoded to 0. Every account this form creates is a regular member — there is no “sign up as admin” checkbox, and there shouldn’t be one: a public form that lets anyone grant themselves elevated access isn’t a signup form, it’s an open door. Which leaves an honest question: how does the first admin account come to exist at all, before require_role('admin') can gate anything?

The simplest answer is also the right one: sign up through the normal public form like anyone else — that gets a real account with a properly bcrypt-hashed password through the exact same path every other account uses, with zero special-case code — then flip the one flag directly in the database, once:

mariadb -u dbuser -p -h dbhost -P 3306 dbname \
  -e "UPDATE members SET admin = 1 WHERE user_name = 'you@example.com';"

Swap in your own -u/-h/dbname and the email you signed up with, and that’s the whole bootstrap — one command, run once, right after your first signup. (Prefer typing it interactively instead? mariadb -u dbuser -p -h dbhost -P 3306, then USE dbname; followed by the same UPDATE, works identically.)

Privilege escalation should never be reachable through a code path the application itself serves — no matter how well-gated that path looks today, a bug in the gate is a bug in the escalation. There is deliberately no “promote to admin” button, because a feature like that would itself need to be admin-gated to be safe, which is the exact same chicken-and-egg problem one level down. The database sits outside that boundary entirely — nothing this app serves can grant admin; only someone with direct database credentials can.

A more hands-off alternative some systems use: check SELECT COUNT(*) FROM members inside the signup handler, and set admin = 1 automatically when that count is zero — the first account ever created becomes the first admin, no manual step required. It works, but it has a sharp edge: if the table is ever fully emptied later (a test wipe, a botched migration), the next signup after that silently becomes an admin too, with nobody deciding that on purpose. Given this only ever matters once, the one-time manual UPDATE above is the safer default — it trades a single command, run once, for never having a code path that can auto-grant admin under the wrong conditions.

Signing In

Login looks up the account by email, then verifies with password_verify(), which handles the hash comparison (constant-time, algorithm-aware) so nothing here ever compares password strings directly. DUMMY_HASH is a bcrypt hash with no matching password, defined alongside the DB config as a hardcoded literal — generated once, offline (php -r "echo password_hash('x', PASSWORD_DEFAULT);") and pasted into a define(). It has to be a literal, not a password_hash() call sitting in that config file: that file loads on every page, and computing a real bcrypt hash on every page view would be the exact cost this fix is trying to avoid paying unnecessarily.

$q = 'SELECT member_id, password_hash, admin, blocked FROM members WHERE user_name = ?';
// ... prepare, bind, execute, fetch into $member_id, $pwhash, $admin, $blocked ...

// always run password_verify(), even for an email that doesn't exist —
// DUMMY_HASH is any precomputed bcrypt hash; its plaintext never matters
$pwhash = $pwhash ?: DUMMY_HASH;
$password_ok = password_verify($password, $pwhash);

$auth_ok = $found && !$blocked && $password_ok;

if ($auth_ok) {
    session_regenerate_id(true);
    // ... set $_SESSION['signed_in'], 'email', 'member_id', 'admin', 'csrf_token' ...
} else {
    sleep(1);
    header('Location: /sign-in.php?error=1');
}

Three details are doing real security work here, and the middle one is easy to miss. The obvious two: the failure path always returns the same generic error — “email or password did not match” — whether the account doesn’t exist, the password is wrong, or the account is blocked, which is the account-enumeration defense from Part 1; and sleep(1) on failure makes online brute-forcing slower without slowing a real user's successful login at all.

The one that’s easy to miss: && short-circuits, so a naive $found && password_verify(...) never calls password_verify() at all when the email doesn’t exist. bcrypt is deliberately slow — tens of milliseconds — so a request for a real, registered email measurably takes longer than one for an email that was never in the table, even though both show the identical error text. That’s a timing side-channel on top of the enumeration defense the response text is supposed to provide, and it’s exactly the kind of gap that survives a code review focused on what the response says rather than how long it took to say it. Falling back to a dummy hash and always calling password_verify() closes it: the bcrypt cost is paid on every failed attempt, found or not, so the two cases take the same time.

Password Reset

A lost password needs a way back in that doesn’t depend on the password itself. The standard shape is a time-limited, single-use token emailed to the address on file — proving the requester controls that inbox, which is the same trust level signup already relied on. It gets its own small table rather than columns bolted onto members, mainly so old tokens are easy to prune and a member can have more than one outstanding request without conflict:

CREATE TABLE password_resets (
  reset_id    INT NOT NULL AUTO_INCREMENT,
  member_id   INT NOT NULL,
  token_hash  VARCHAR(64) NOT NULL,   -- sha256 hex, never the raw token
  expires_at  DATETIME NOT NULL,
  used_at     DATETIME,                -- NULL until redeemed
  PRIMARY KEY (reset_id),
  UNIQUE KEY (token_hash)
);

Only the token’s hash is stored — the same reasoning as password_hash above. A database leak shouldn’t hand out working reset links; the raw token exists only in memory for the length of this request, and in the one email it’s sent in.

$raw_token  = bin2hex(random_bytes(32));
$token_hash = hash('sha256', $raw_token);
$expires_at = date('Y-m-d H:i:s', time() + 1800);   // 30 minutes

// ... look up $member_id by email, same as sign-in ...
if ($member_id) {
    $hDB->insert_prepared(
        'INSERT INTO password_resets (member_id, token_hash, expires_at) VALUES (?, ?, ?)',
        'iss', $member_id, $token_hash, $expires_at
    );
    send_reset_email($email, $raw_token);
}

// same redirect whether $member_id was found or not:
header('Location: /forgot-password.php?sent=1');

That last line matters as much as the token logic. If the confirmation page only appeared for known accounts, the forgot-password form would become another account-enumeration oracle — exactly the Part 1 threat the sign-in error message already avoids. The form is guarded by the same rate_limit('reset', $ip) bucket from earlier, though that caps requests per IP, not per email address — a determined attacker could still spam one victim’s inbox from many IPs. Worth knowing; not worth solving here.

The emailed link points at a page that only checks the token — it never trusts anything else about the request:

$raw_token  = $_GET['token'] ?? '';
$token_hash = hash('sha256', $raw_token);

$q = 'SELECT member_id, reset_id FROM password_resets
      WHERE token_hash = ? AND expires_at > NOW() AND used_at IS NULL';
// ... prepare, bind, execute, fetch ...

if (!$found) {
    // generic "this link has expired or was already used" page
}

Submitting a new password re-runs that exact check — a token isn’t trusted just because it was valid a moment ago on the GET request — then replaces the hash and burns the token in the same request:

$pwhash = password_hash($password, PASSWORD_DEFAULT);

// UPDATE members SET password_hash = ? WHERE member_id = ?
// UPDATE password_resets SET used_at = NOW() WHERE reset_id = ?

header('Location: /sign-in.php?reset=1');   // sign in again, with the new password

Signing in again afterward, rather than auto-establishing a session, keeps this handler from needing its own copy of the session-fixation defense — one fewer place that mints a session, one fewer place to get it wrong.

One thing this reset does not do: invalidate any session that was already open under the old password. If the reason for the reset is that the old password leaked, whoever already has an active session — including the person who leaked it — keeps that session exactly as it was. The fix is the same one Signing Out flags below: a way to invalidate sessions server-side, rather than trusting only PHP's own opaque session store.

send_reset_email() is the one piece intentionally left as a named function rather than code: this article covers the token logic, not mail delivery. That’s worth calling out rather than glossing over — PHP’s built-in mail() often does nothing on a Lightsail or EC2 instance, since AWS throttles outbound port 25 by default on new accounts specifically to fight spam from compromised instances. The practical fix on this stack is Amazon SES, used either over SMTP or via its API; that setup is enough of its own topic that it isn’t built out here.

Gating Pages by Role

Part 1 argued for a shared function over copy-pasted $_SESSION checks. Here it is:

function require_role(string $role): void {
    if ($role === 'member' && empty($_SESSION['signed_in'])) {
        header('Location: /sign-in.php');
        exit;
    }
    if ($role === 'admin' &&
        (empty($_SESSION['signed_in']) || empty($_SESSION['admin']))) {
        header('Location: /sign-in.php');
        exit;
    }
}

session_start([/* httponly, secure, samesite */]);
require_once __DIR__ . '/includes/require-role.php';
require_role('member');   // or 'admin'

The admin check tests signed_in and admin rather than just admin alone, on purpose — belt-and-suspenders against a future refactor that sets one flag without the other.

Signing Out

The simplest handler in the system, and worth staying that simple:

session_start([/* httponly, secure, samesite */]);
session_unset();
session_destroy();
header('Location: /');

One limitation worth naming: this destroys this session only. It does not give you “sign out everywhere” — PHP's default session store has no index of a member's other active sessions to destroy.

That gap is bigger than it sounds, because require_role() and the login check both read state that was frozen into the session at login time, not the row as it stands right now. Concretely: an admin sets blocked = 1 on an abusive member through admin-users.php while that member has an active session open. Nothing happens. The blocked column is only ever checked inside the login POST handler — require_role() never queries the database at all, it just reads $_SESSION['signed_in']. The member keeps full access until that PHP session naturally expires or they sign out voluntarily, which an abusive member has no reason to do. The same is true in reverse for the admin flag: promoting or demoting it doesn’t take effect for someone already signed in.

What This Build Doesn’t Cover Yet

Email Verification

Skipped deliberately, as noted in Signing Up — but the reason to add it eventually is concrete, not just tidiness. Without it, anyone can create an account using an email address they don’t control: a typo of their own address, or someone else’s entirely. The second case is the one that matters — it means this signup form can be used to send a stranger unwanted account-related mail (today just this article's future password-reset emails; potentially more, on a site that ever grows notification email of its own), with no proof the recipient ever asked for an account here. Verification closes that by holding the account unprivileged — no session, no login — until a token emailed to the address is clicked, using the exact same hashed-token pattern password reset already builds.

Sign Out Everywhere / Server-Side Session Invalidation

This is the one gap that actually undermines other parts of this build, not just a missing convenience. Password reset above and account blocking both assume that changing something in the members row takes effect immediately — and neither one does, for a session that’s already open. The fix is the same for both: a sessions table keyed by member_id, storing at minimum a session ID and a created/last-seen timestamp per row. “Sign out everywhere” becomes deleting every row for that member. Making blocked checks synchronous is the same idea taken one step further — checking the database (or a fast cache in front of it) on every gated request instead of only at login — which trades a small amount of per-request cost for closing the gap immediately rather than at next login. Whether that trade is worth it depends on your site’s size; it’s a reasonable thing to add later without touching anything else built here.

Mail Delivery

Password reset above builds the token logic; actually sending the email needs SES or a similar service, as noted there.

Passwordless Sign-In

Part 3 adds WebAuthn on top of exactly this system.

None of these are missing by accident — each is a deliberate line drawn to keep this build small enough to actually secure end to end, per Part 1's recommendation. Add them if and when your site's actual usage demands it.

Checklist

Schema and session:
  [ ] One members table, password_hash only, never plaintext
  [ ] UNIQUE KEY on user_name — the app-level check alone can race
  [ ] session_start() with httponly + secure + samesite=Lax

Every state-changing POST:
  [ ] CSRF token minted on GET, checked with hash_equals on POST
  [ ] Rate limited per IP, keyed by bucket (signup vs. login)
  [ ] session_regenerate_id(true) on every successful login/signup

First admin:
  [ ] Sign up through the normal form, then UPDATE admin = 1 by hand, once
  [ ] No in-app "promote to admin" button — that just moves the chicken-and-egg problem

Signing in:
  [ ] password_verify() against password_hash, never a direct compare
  [ ] Always call password_verify(), even on unknown email (DUMMY_HASH) — closes the timing side-channel
  [ ] One generic error message for wrong password, no account, or blocked
  [ ] sleep(1) on failure

Password reset:
  [ ] Own table, only the token's sha256 hash stored, never the raw token
  [ ] Time-limited (30 min) and single-use (used_at)
  [ ] Same response whether the email was found or not
  [ ] Mail delivery is a separate concern (SES, not mail())

Gating pages:
  [ ] One shared require_role() function, not per-page $_SESSION checks
  [ ] Three tiers: guest, member, admin

Known gaps, addressed on purpose:
  [ ] No email verification — unverified addresses can create accounts
  [ ] No session invalidation — blocked/promoted/reset accounts stay live until next login