Storing the verification phone as a keyed HMAC fixed OTP lookups but broke
every query that joined those tables back to the account, because
phone_verification*.phone_number no longer holds the same value as
driver.phone / passengers.phone. Six joins were affected, and four of them
feed the `verified` flag that the rider and driver apps check at sign-in — so
this was already failing under the current CBC mode, not only after a switch
to GCM.
Accounts now carry phone_key, computed exactly as otpPhoneKey() does, and the
joins match on it. It is written at registration for both apps and populated
for existing rows by the backfill.
The backfill also covers the columns added for the remaining lookups:
users.email_bidx/phone_bidx and driver.national_bidx, which were migrated but
never populated, and honours a per-field prefix so phone_key reproduces
otpPhoneKey's exact output.
Insert column/value counts verified with a paren-aware parser after editing.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
Completes the set of queries that matched a freshly encrypted value against a
stored one, which only works while encryption is deterministic. Each keeps its
original comparison and adds an index comparison in the same WHERE, so nothing
changes today.
- passenger sign-in by email, service-staff sign-in, Firebase token lookup
- driver lookup by phone and by national number
- admin ride lookup and ride monitor (both tables)
- nabeh: driver status, user resolution, ride history, complaint submission
transit_org_admins lives in the transit database and has no index column, so
login there falls back to decrypting the small set of active admins and
comparing normalised numbers.
Schema: adds users.email_bidx/phone_bidx and driver.national_bidx with their
indexes.
Verified that every :*_bidx placeholder introduced is actually bound — an
unbound one is a fatal error at request time, not a silent miss.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
The verification tables (token_verification*, phone_verification*) use the
phone number as a lookup key: written when the code is sent, read when it is
checked. Storing it encrypted worked only because encryptData() is
deterministic — under AES-GCM the two sides would produce different
ciphertexts and no code would ever verify, locking every user out of
registration and OTP sign-in.
otpPhoneKey() stores a keyed HMAC of the normalised number instead. No schema
change is needed since the column is textual, local and international formats
now resolve to the same key, and the value cannot be reversed without the
pepper. It falls back to the previous behaviour when no pepper is configured.
Applied to both sides of every affected flow — request/verify, and the driver
and passenger send/verify pairs — including the OTP value itself where it is
compared by equality rather than decrypted. auth/otp/verify.php already
decrypts the token before comparing, so it needed no change there.
Also adds ENCRYPTION_MODE to EncryptionHelper: encryptData() writes GCM when
set to 'gcm', CBC otherwise. Verified in both directions — rows written under
CBC stay readable after switching, and rows written under GCM stay readable
after rolling back — so the switch is reversible by an environment variable.
The admin console's own OTP is unaffected: it keys the table by the stored
ciphertext read from adminUser, identical on both sides.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
These are the paths that must stop depending on deterministic encryption
before storage can move to AES-GCM. Each keeps its original ciphertext
comparison in the same statement, so behaviour is unchanged today and no
account becomes unreachable during the transition.
Lookups:
- auth/login.php — passenger sign-in matched the raw value against the
encrypted column, which only works because encryptData() is CBC with a
fixed IV.
- auth/passenger/register.php and auth/driver/register.php — duplicate
detection. Without the index these would stop detecting existing accounts
under GCM and allow the same phone to register twice.
Writes now populate the index in the same statement as the value:
- both registration paths write phone/email/name indexes with the row;
driver indexes are computed before the encryption pass, since the raw
values are unavailable afterwards.
- passenger profile update and admin driver update refresh the index when
the underlying field changes. For the composite name index the untouched
half is read back from the row.
Adds --audit to the backfill script: recomputes every index from its
encrypted value and reports missing or stale entries. Drift here is silent
by nature — it surfaces only when a real search fails.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
Hamza-Ayed