Double opt-in in PHP with a secure confirmation token

Chapter 2 of 8. The subscribe endpoint, the confirmation token, the confirm click, plus the consent record that most double opt-in tutorials never write. Prerequisite: chapter 1. You should already have PHP 8.2 on a cPanel host, plus a MySQL database and a scoped DB user holding SELECT, INSERT and UPDATE.

Search "php newsletter double opt-in gdpr tutorial" and the first five results are the same article. A form posts an email. The script inserts a row with confirmed = 0 and a token from md5(uniqid()). A link in an email hits confirm.php?token=abc, which runs UPDATE ... SET confirmed = 1. The piece ends with a note that this is a starting point and not production ready.

The flow works. It is incomplete in the exact place that matters. UK GDPR asks you to demonstrate consent: a record of who consented, when, and to what. A boolean is not that record. Almost none of those tutorials write one. This chapter is the flow as it runs on captainrandom.co.uk, in PHP, on shared hosting, with the consent timestamp stamped at the one moment it is legally meaningful.

What GDPR asks a double opt-in flow to prove

Start with the rule that governs the send. The ICO's electronic mail marketing guidance sets out PECR regulation 22: you must not send marketing email to an individual unless they have specifically consented, or the soft opt-in applies. The soft opt-in needs an existing customer who bought or negotiated to buy a similar product. A content newsletter with no prior commercial relationship does not qualify, so specific consent is the only route available to you.

Then the standard that consent has to meet. The ICO's what is valid consent page is explicit that consent must be "freely given, specific, informed and unambiguous," and that it has to come from a statement or a clear affirmative action. Silence, pre-ticked boxes and inactivity do not count. The page also points at Article 7(1): you must be able to demonstrate that someone has consented.

That last requirement is the one that changes your schema. A confirmed boolean tells you a state. It says nothing about when the affirmative action happened, so it cannot demonstrate anything. The subscribers table on this site carries a separate column for exactly that:

CREATE TABLE IF NOT EXISTS subscribers (
  id                      INT UNSIGNED        NOT NULL AUTO_INCREMENT,
  email                   VARCHAR(254)        NOT NULL,
  status                  ENUM('pending','confirmed','unsubscribed')
                          NOT NULL DEFAULT 'pending',
  confirm_token_hash      CHAR(64)            NULL,
  confirm_expires_at      DATETIME            NULL,
  unsubscribe_token_hash  CHAR(64)            NOT NULL,
  consented_at            DATETIME            NULL,
  unsubscribed_at         DATETIME            NULL,
  created_at              DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP,
  source                  VARCHAR(64)         NULL,
  ip_hash                 CHAR(64)            NULL,
  ua_hash                 CHAR(64)            NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_email (email),
  KEY idx_status (status),
  KEY idx_confirm_expires (confirm_expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

created_at is when someone typed an address into a form. consented_at is when a human being who controls that mailbox clicked a link. Those are different facts and they get different columns. consented_at starts NULL. It stays NULL until the confirmation POST lands. The rest of this chapter is about that one transition.

The subscribe endpoint: cheap checks first, then mint the token

public/subscribe.php runs an eight-stage pipeline. Method check, Origin check, per-IP rate limit, honeypot plus time-trap, email validation. Only stage six touches the subscribers table, only stage seven sends mail, and stage eight redirects. The order is deliberate: the cheapest rejections come first, so nothing an attacker types gets anywhere near the subscribers table or the mailer.

Cheap is not free. Stage three rewrites a per-IP JSON file on every single call, inside a flock:

ftruncate($fp, 0);
rewind($fp);
fwrite($fp, json_encode($state));
fflush($fp);

Every rejection from stage two onwards then routes through Logger::event(), which is an INSERT INTO events (subscriber_id, kind, meta) VALUES (?, ?, ?). An origin mismatch, a rate-limit hit, a filled honeypot, a tripped time-trap and a malformed address all leave a row in MySQL behind them. What the ordering buys you is that none of them reach the expensive work: no row in subscribers, no token minted, no mail() call. That is a smaller claim than "hostile input never touches the database," and it is the true one.

Syntactic email validation is one small function, and the ordering inside it matters:

public static function email(string $raw): ?string
{
    $candidate = strtolower(trim($raw));
    if (strlen($candidate) === 0 || strlen($candidate) > 254) {
        return null;
    }
    return filter_var($candidate, FILTER_VALIDATE_EMAIL) ? $candidate : null;
}

Normalise first, then apply the RFC 5321 ceiling of 254 characters, then hand the result to filter_var. The function returns the normalised address on success and null on failure. That return shape is deliberate. The PHP manual for filter_var notes that on success the function hands back the filtered value rather than true, so a check written as if (filter_var(...) === true) is silently broken. Returning the normalised string fixes the shape of the address for everything downstream of the check. The same value is what the existence SELECT matches on, what the INSERT stores, and what the confirmation email is addressed to. One canonical form, agreed before any of the three run.

The endpoint also runs two bot checks before the email check. A hidden website field must arrive empty. A rendered_at timestamp shipped by the browser must be at least one second and at most 24 hours old. That minimum used to be two seconds and it caught a real person. Browser autocomplete pre-filled the field and the click landed about a second later. A legitimate signup was thrown away as bot-shaped. The constants in Validator.php carry the fix:

final class Validator
{
    public const MIN_RENDER_AGE_SECONDS = 1;
    public const MAX_RENDER_AGE_SECONDS = 86400;

The confirmation token: 32 bytes of CSPRNG, stored only as a hash

The whole token module is one class with three static methods, no library and no JWT behind it:

final class Tokens
{
    public static function generate(): string
    {
        return bin2hex(random_bytes(32));
    }

    public static function hash(string $plain): string
    {
        return hash('sha256', $plain);
    }

    public static function compare(string $storedHash, string $plain): bool
    {
        return hash_equals($storedHash, self::hash($plain));
    }
}

Three decisions are packed into that. random_bytes is the CSPRNG, and the PHP manual says its output is "suitable for all applications, including the generation of long-term secrets, such as encryption keys." rand and mt_rand fail that test, and md5(uniqid()) is a timestamp with a hash pulled over the top of it. Thirty-two bytes encoded with bin2hex gives a 64-character lowercase hex string. That string is the plaintext that goes into the email URL.

What lands in the database is sha256 of that plaintext, and only that. The plaintext exists in PHP request scope and in the email body, nowhere else. A leaked database dump contains no working confirmation links. OWASP's Forgot Password guidance sets the same bar for emailed tokens.

Comparison goes through hash_equals. The PHP manual describes it as checking equality "without leaking information about the contents of known_string via the execution time." The manual is also emphatic that the user-supplied string belongs in the second argument. Mismatched lengths return false immediately while leaking the known string's length. Hashing both sides to a fixed 64 characters before comparing removes that second hazard entirely.

The TTL is 60 minutes. MySQL computes it:

$stmt = $pdo->prepare(
    'INSERT INTO subscribers
         (email, status, confirm_token_hash, confirm_expires_at,
          unsubscribe_token_hash, source, ip_hash, ua_hash)
     VALUES (?, ?, ?, DATE_ADD(NOW(), INTERVAL 60 MINUTE),
             ?, ?, ?, ?)'
);

An earlier draft computed the expiry in PHP with DateTimeImmutable, which formats as UTC. MySQL wrote created_at in its own frame, an hour ahead. The offset swallowed the whole 60-minute TTL. Rows were expired the moment they were inserted, while the events table showed a correct signup then confirm_sent sequence. Keep every datetime calculation on one side of the wire. DATE_ADD(NOW(), INTERVAL 60 MINUTE) is what that looks like in the INSERT. The PDO manual's injection guarantee holds only for prepared statements with bound parameters. Every statement on the subscribe and confirm paths is one.

Confirming: GET renders, POST mutates, consent gets its timestamp

public/confirm.php splits the click into two hops. The GET that arrives from the email touches no database at all. It renders an interstitial that relays id and t into a POST form, and only the POST verifies and writes. That keeps the token out of browser history and out of the Referer header. It also means a mail scanner prefetching the link cannot confirm a subscription on the subscriber's behalf.

1. Gate the shape before you touch MySQL

   The id must pass ctype_digit and the token must match /\A[0-9a-f]{64}\z/. Anchored with \A and \z rather than ^ and $, so a trailing newline cannot slip through. Anything malformed hits badLink() and never reaches a query.

2. Read the row and let SQL decide about expiry

   The SELECT computes (confirm_expires_at IS NULL OR confirm_expires_at < NOW()) AS expired inside MySQL, and PHP reads a boolean. The comparison happens in the same reference frame the row was written in. Watch where the check sits in the branch order, though: status is tested before it, so this flag only ever rules on a row that still says pending.

3. Compare in constant time

   Tokens::compare($row['confirm_token_hash'], $token) hashes the supplied plaintext and runs hash_equals against the stored digest.

4. Write the consent record and destroy the token

   One UPDATE flips the status and stamps consented_at. The same statement nulls both confirm-token columns, so no digest survives the transaction.

That final UPDATE is the whole chapter in one statement:

$upd = $pdo->prepare(
    'UPDATE subscribers
        SET status = ?,
            consented_at = NOW(),
            confirm_token_hash = NULL,
            confirm_expires_at = NULL
      WHERE id = ?'
);
$upd->execute(['confirmed', $id]);

consented_at = NOW() fires here and nowhere else in the codebase. Article 7 asks you to keep a record of the affirmative action. This is that record, written at the instant a person who controls the mailbox performed one. Nulling confirm_token_hash in the same statement destroys the secret at rest: once that UPDATE commits, there is no stored digest left for any future comparison to match against.

Single use is enforced by the status check, not by the NULL expiry. A replayed URL never gets as far as the expiry flag. The POST re-reads the row and the first branch it meets is the status:

// Already confirmed → succeed idempotently (same UX as fresh confirm).
if ($row['status'] === 'confirmed') {
    header('Location: ' . $siteUrl . '/newsletter/confirmed/', true, 302);
    header('Cache-Control: no-store');
    exit;
}

The status transition is what spends the token. No token comparison, no write. A subscriber who double-taps the confirm button gets idempotent success instead of a frightening error page, and the two guards sitting below that branch, the pending test and the expiry flag, never see a consumed row at all.

The identical 302, whatever the row said

The upsert has three branches and only two of them write. A brand-new address triggers an INSERT with both tokens plus a confirmation email. For an existing pending row, both tokens are re-issued, the unsubscribe token included, because the previous plaintext became unrecoverable the moment only the hash was stored. Anything already confirmed or unsubscribed falls through to:

} else {
    // status is 'confirmed' or 'unsubscribed' — no oracle, no mail.
    $sendMail = false;
}

Nothing is mutated and nothing is sent. Not even an events row: a row count is itself an information channel. Each of those outcomes then lands on the identical 302 to /newsletter/check-your-inbox/. New address, already subscribed, previously unsubscribed, malformed email, filled honeypot, even a thrown database exception: the same status code and the same location header.

The endpoint does have loud failure modes. Every one of them judges the shape of the request. None of them judges the address inside it. A non-POST gets 405, a mismatched Origin gets 403, a rate-limited IP gets 429. The time-trap is the odd one out. It redirects to /newsletter/please-try-again/, because the person who tripped it is probably a person. The callout further up made that promise: a check a human can fail has to show that human a page. The property that has to hold is simple. The response must never depend on whether the address you typed is already on the list. None of these do. OWASP's Forgot Password guidance asks for exactly this, a consistent response for existent and non-existent accounts, because the alternative is an endpoint that will tell any stranger whether a given person is on your list.

The cost of that discipline is diagnostic blindness, and the answer to it is the events table. Every signup, re-issue, confirmation, rate-limit hit and bot detection writes a row with a kind, and none of them carries an email in any form. Past that the rows are not uniform, and the shape of the difference is the useful part. State changes carry the integer subscriber_id that ties them to a subscriber. Rate-limit hits and bot detections pass null for it, because the POST that triggered them never produced a row to point at. schema.sql says so out loud: subscriber_id is NULLABLE on purpose.

meta is populated just as unevenly. Logger.php writes $meta === [] ? null : json_encode($meta, JSON_UNESCAPED_SLASHES), and confirm.php calls Logger::event('confirmed', $id, []), so a confirmation row's meta is NULL. It has a subscriber_id and needs nothing else. A bot_detected row is the mirror image: no subscriber, and everything you can learn from it lives in meta. Two consecutive bot_detected rows with reason: time_trap a minute apart are what exposed the autocomplete false positive above. There was no subscriber to join them to. meta.reason was the whole diagnosis. Three newsletter hotfixes. The events table found all of them is the lived version of that debugging session.

The transferable rule

Consent is a record, not a flag. A boolean says a state exists. A timestamp says when the person did the thing. Only the timestamp survives contact with a regulator asking you to demonstrate it.

So write the record at the moment of the affirmative action, and at no other moment. That single constraint drags the rest of the design behind it. It forces the confirmation click to be a real state transition. The tutorial version's click is cosmetic. A real one has to be unforgeable, so the token has to be unguessable. Everything after that falls out of those two. The status transition spends the token, so it works once. The 60-minute TTL kills the ones nobody clicks. The UPDATE nulls the digest, so nothing is left at rest to steal. Get consented_at = NOW() into the right UPDATE and most of the security follows. Put it in the INSERT next to created_at and you have built the tutorial version, a flow that looks like double opt-in and proves nothing at all.

The next chapter takes the other two columns in that INSERT, ip_hash and ua_hash. It asks the harder question. An IP address is personal data. What are you allowed to keep, and what does hashing it actually buy you?