Whether Admin equals admin in a database query depends entirely on the database engine, the charset, the collation, and sometimes the query form. This is not something you can assume — it must be tested.
Collations: The Rules for String Comparison
A collation is a set of rules for comparing strings. It determines:
- Are uppercase and lowercase the same? (
_ci= case insensitive,_cs= case sensitive) - Are accented characters the same as their base? (
é=e?) - Are characters from other scripts equal to visually similar Latin characters?
Every database column has a collation, inherited from the table default, inherited from the database default, inherited from the server default — unless explicitly set.
PostgreSQL
PostgreSQL uses the C locale (byte-comparison) or locale-aware collations. The default for most installations is case-sensitive.
-- Returns nothing if stored as 'admin', because 'Admin' ≠ 'admin'
SELECT * FROM users WHERE username = 'Admin';
-- Returns the row — ILIKE is case-insensitive
SELECT * FROM users WHERE username ILIKE 'Admin';
-- Alternative: explicit LOWER()
SELECT * FROM users WHERE LOWER(username) = LOWER('Admin');
An application using = for username lookup in PostgreSQL is case-sensitive by default. If the UI does not normalize case on input, login with admin will fail for a user registered as Admin.
MySQL
MySQL's default charset is utf8mb4 and the default collation in most configurations is utf8mb4_general_ci or utf8mb4_unicode_ci — both case-insensitive.
-- In MySQL with _ci collation, this MATCHES both 'admin' and 'Admin'
SELECT * FROM users WHERE username = 'Admin';
This means MySQL behaves case-insensitively by default, while PostgreSQL behaves case-sensitively. An application tested on MySQL and deployed against PostgreSQL may have login failures that never appeared in testing.
A MySQL UNIQUE constraint on a _ci collation column treats admin and Admin as duplicates — you cannot register both. A PostgreSQL UNIQUE constraint treats them as different — both can be registered.
SQLite
SQLite's = operator is always case-sensitive. LIKE is case-insensitive for ASCII characters but case-sensitive for non-ASCII Unicode:
-- SQLite: 'admin' = 'ADMIN' → false
-- SQLite: 'admin' LIKE 'ADMIN' → true (ASCII letters only)
-- SQLite: 'аdmin' LIKE 'Аdmin' → false (Cyrillic А ≠ а in SQLite LIKE)
This creates a trap: if your application uses LIKE for searches and the strings contain non-ASCII characters, the case behavior is different for ASCII and non-ASCII input.
How to Test Database Case Behavior
The test that reveals the database behavior:
- Create a record with value
admin(lowercase) - Query or authenticate with
Admin(mixed case) - Query or authenticate with
ADMIN(uppercase) - Observe whether each matches
If they match: the database is using a case-insensitive collation or the application normalizes before querying. If they don't match: the database is case-sensitive or the application does not normalize.
Then verify consistency:
- Does registration use the same case rule as login?
- Does the "forgot password" lookup use the same case rule as login?
- Does the admin search panel use the same case rule as the API?
Any difference between code paths is a potential bug.
The Hidden Case Bug
The most common hidden case bug: the application lowercases usernames at registration but not at login. A user registers as Admin → stored as admin. They then try to log in with Admin → the login query checks username = 'Admin' → no match found (case-sensitive DB) → "Invalid credentials."
This bug is visible only when the database is case-sensitive (PostgreSQL) and the application inconsistently normalizes. On MySQL (case-insensitive DB), the bug is invisible — Admin matches admin at query level. The application is technically broken but MySQL compensates.
When the team migrates from MySQL to PostgreSQL (a common scale-up path), all these latent case bugs surface simultaneously.