Post

I was playing with SQL Server and noticed the default collation is case insensitive (SQL_Latin1_General_CP1_CI_AS). admin and ADMIN are the same string to the DB. That part is well known. Less talked about: = also ignores trailing spaces. 'admin' = 'admin␣' is true (␣ is an actual space char)

I tried CS, BIN, BIN2 and a UTF8 collation. They all pad the space away. Looks like only a VARBINARY cast sees the difference.

So if an app blocks a username in app code: if (username == "admin") deny; <- checks case and spaces WHERE username = @username <- checks neither Send ADMIN, or 'admin␣'. The check doesn't fire and the DB still finds the admin row.

Now if someone fixes it like: username.ToLower() == "admin" stops ADMIN, but lets 'admin␣' through. Moving the column to a CS collation also stops ADMIN, and also lets 'admin␣' through. Two different fixes, same half of the bug left open.

Other things I tested: Only the space char. Tab, newline and CR still count Leading spaces still count = and LIKE disagree: 'admin' = 'admin␣' is true, 'admin' LIKE 'admin␣' is false A UNIQUE index under CI rejects ADMIN as a duplicate of admin, so it's a lookup bug not a signup one

MySQL is case insensitive by default too, but its 8.0+ default doesn't pad: 'admin' = 'admin␣' is false. Older ones like utf8mb4_general_ci still do. Postgres does neither. App assumes one thing, DB does another. This can lead to some weird bugs and bypasses.