-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA256
Michael,
On 8/27/13 2:52 PM, Michael Spring wrote:
> I have observed using tomcat 7.027 and 6.026 an issue with BASIC
> authentication. My intent was to have both user names and passwords
> be case sensitive. I know of nothing I did that would change that.
> The database table is plain vanilla. Passwords are case sensitive,
> but upper or lower case usernames work. Is there any way to
> prevent this?
MySQL does string-matching in a case-insensitive way by default. The
solution is to give the db a hint when doing your SELECT, like this:
Old: SELECT * FROM user WHERE username='CHRIS';
New: SELECT * FROM user WHERE BINARY username='CHRIS';
The "new" query will only select users whose usernames are 'CHRIS'
exactly -- case-sensitively.
Note that if you have an INDEX on user.username, it can't be used in
its current form -- which is expected to be case-insensitive. If you
do an EXPLAIN on the above queries, you'll see that both of them use
the INDEX you have on the table, but in one case it will be a quick
lookup (likely a hash-based lookup) and in the other (BINARY) case,
you'll have to perform an index traversal in order to do the match.
I haven't tried it, but you might be able to add another INDEX for
"BINARY username" that will give you better performance.
As for using Tomcat's built-in authentication, you won't be able to
modify the queries as I have shown above. You have to tell the server
some other way.
One way is to make the column a BINARY column:
ALTER TABLE user
MODIFY COLUMN username VARCHAR(255)
CHARACTER SET utf8
COLLATE utf8_bin
;
Obviously, you'll have to match the data type and length to meet your
needs.
Once you do this, username will act like a case-sensitive column for
even queries without a BINARY hint:
SELECT * FROM user WHERE username='CHRIS';
I think that's what you're going to want to do: it will basically
magically make everything work the way you expected.
Honestly, I would caution against case-sensitive usernames. Way too
many users like to re-invent their own capitalization every time they
log in.
- -chris
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.14 (Darwin)
Comment: GPGTools - http://gpgtools.org
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/
iQIcBAEBCAAGBQJSHRiKAAoJEBzwKT+lPKRY+U8P/idSGfhj6LusUWtH7FeuM95H
aSRR+zuLTghzvc0rwh7yLN8D3t7vZOZxDWVVXoMGmwDWT211GPn/Ddv51YHBh0CF
fQAeVEczSYHPXKptVPRcYxqmgFt0BNeVFTix9qFNcwI6eaKAhrmT2DhTMpgB8CBR
dzMuT64r6xtKHmKIb7hUyFHraLiV6zKiILUVi29SFy0JxRAozgjsKdLwXoj7nrhK
EHnr827VwKyhMAgBru83wb4bczEGxO1YgaV0c1uVJMC/KLNixSNL8s5sHw5Hz3NZ
SnYCdxnULE7wJwSXeljtwiMtuLrJnpf6KIsHASxd4gpp2N3FkWtHX/JCRSDfpU3w
C6NSLPDljUrgjXty3ixnjBdJ14cqQ/bb6DKWODAEY2CMwT//DvojSal7HWmSrj/T
meam+l9L/jiQUhO6KZwD6g8gmFhprvh4JzaNTHXc6Fu7m0NpoYulfy7ZesfflFwD
nX9Pat/djyIpvuyNlOpULvOfkyieDnLvQ090cnhkJ5cDAqlAWcqfT+kDsupos3WB
rUVblyYBMG4pnryia7LOJJ9sOtf+63UVEAyKyKpYRUoyUEbhuNDZSsjwT7FRohuj
4VmTPjwnhuWLFeCY5JdAQQQw6vLjipWpiQx7Z2u+t9gY14L7hg7EBH4fvWy7Qi3d
MW4TgCngeR09EcpsA5Bp
=uq02
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
