Re: Question about usernames being case insensitive
On Aug 27, 2013, at 2:52 PM, Michael Spring spr...@pitt.edu 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? Operating systems are windows 7 and windows Server 2008R2 both 64 bit. web.xml includes login-config auth-methodBASIC/auth-method realm-name/realm-name /login-config context.xml includes Realm className=org.apache.catalina.realm.JDBCRealm connectionURL=jdbc:mysql://localhost/XXX?user=XXXamp;password=a4HLw3Jx digest=MD5 driverName=com.mysql.jdbc.Driver roleNameCol=role userCredCol=password userNameCol=username userRoleTable=USER_ROLES userTable=USERS/ Resource auth=Container driverClassName=com.mysql.jdbc.Driver maxActive=30 maxIdle=30 maxWait=1200 name=jdbc/wmmd_db password=test type=javax.sql.DataSource url=jdbc:mysql://localhost/WMMD_WMMD?user=testamp;password=test username=test/ Have you checked to see if your database is causing this behavior? Perhaps connect directly to the DB and issue the same queries that Tomcat would issue. Then check to see if those are case insensitive. Dan Thanks in advance for any help or guidance, I've spent a half day scouring documentation and can't find a lead. -- With best wishes, Michael Michael B. Spring Associate Professor Information Science and Telecommunications Voice: (412)-624-9429 Fax: (412)-624-2788 WWW: http://www.sis.pitt.edu/~spring http://www.sis.pitt.edu/%7Espring Pmail: 701B SIS Building, 135 North Bellefield University of Pittsburgh, PA 15260 - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
RE: Question about usernames being case insensitive
-Original Message- From: Daniel Mikusa [mailto:dmik...@gopivotal.com] Sent: Tuesday, August 27, 2013 2:22 PM To: Tomcat Users List Subject: Re: Question about usernames being case insensitive On Aug 27, 2013, at 2:52 PM, Michael Spring spr...@pitt.edu 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? Operating systems are windows 7 and windows Server 2008R2 both 64 bit. Have you checked to see if your database is causing this behavior? Perhaps connect directly to the DB and issue the same queries that Tomcat would issue. Then check to see if those are case insensitive. Dan --- This was my guess as well. Would you have some kind of procedure in the DB that forces upper or lower to the username value? - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Question about usernames being case insensitive
On 8/27/2013 3:26 PM, Propes, Barry L wrote: -Original Message- From: Daniel Mikusa [mailto:dmik...@gopivotal.com] Sent: Tuesday, August 27, 2013 2:22 PM To: Tomcat Users List Subject: Re: Question about usernames being case insensitive On Aug 27, 2013, at 2:52 PM, Michael Spring spr...@pitt.edu 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? Operating systems are windows 7 and windows Server 2008R2 both 64 bit. Have you checked to see if your database is causing this behavior? Perhaps connect directly to the DB and issue the same queries that Tomcat would issue. Then check to see if those are case insensitive. Dan --- This was my guess as well. Would you have some kind of procedure in the DB that forces upper or lower to the username value? Or the db may simply be doing case-insensitive comparisons. Mine is configurable for that. - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Question about usernames being case insensitive
All three responses are exactly right. I checked my script and assumed -- and we know what happens when you do that -- that since I had made no specification for case insensitive that it would be case sensitive. It wasn't. I will go see why MYSQL is doing that and make the change there. Thank you every so much. Teaches me to make sure I check all the possibilities before I start pointing a finger in teh wrong direction. Love those features! With best wishes, Michael Michael B. Spring Associate Professor Information Science and Telecommunications Voice: (412)-624-9429 Fax: (412)-624-2788 WWW: http://www.sis.pitt.edu/~spring http://www.sis.pitt.edu/%7Espring Pmail: 701B SIS Building, 135 North Bellefield University of Pittsburgh, PA 15260 On 8/27/2013 3:28 PM, David kerber wrote: On 8/27/2013 3:26 PM, Propes, Barry L wrote: -Original Message- From: Daniel Mikusa [mailto:dmik...@gopivotal.com] Sent: Tuesday, August 27, 2013 2:22 PM To: Tomcat Users List Subject: Re: Question about usernames being case insensitive On Aug 27, 2013, at 2:52 PM, Michael Spring spr...@pitt.edu 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? Operating systems are windows 7 and windows Server 2008R2 both 64 bit. Have you checked to see if your database is causing this behavior? Perhaps connect directly to the DB and issue the same queries that Tomcat would issue. Then check to see if those are case insensitive. Dan --- This was my guess as well. Would you have some kind of procedure in the DB that forces upper or lower to the username value? Or the db may simply be doing case-insensitive comparisons. Mine is configurable for that. - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
RE: Question about usernames being case insensitive
-Original Message- From: Michael Spring [mailto:spr...@pitt.edu] Sent: Tuesday, August 27, 2013 2:35 PM To: Tomcat Users List Subject: Re: Question about usernames being case insensitive All three responses are exactly right. I checked my script and assumed -- and we know what happens when you do that -- that since I had made no specification for case insensitive that it would be case sensitive. It wasn't. I will go see why MYSQL is doing that and make the change there. Thank you every so much. Teaches me to make sure I check all the possibilities before I start pointing a finger in teh wrong direction. Love those features! With best wishes, Michael I could be wrong here, and am not trying to digress from the topic, but I was thinking Oracle and MySQL both came with the default set to being case-sensitive. Not sure, but I thought so. Not sure about DB2, PostgreSQL or others. I thought SQL Server's default might be tweaked to being case-insensitive by default. But since you're using MYSQL that last part really isn't too applicable, unless MySQL is also configured that way. To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Question about usernames being case insensitive
-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
Re: Question about usernames being case insensitive
Christopher: Thank you for your very comprehensive and thoughtful answer. We have at this point come to all the points you so eloquently make. We need to do a little DBMS modification to allow tomcat to do what we expect. You detail will help us make those modifications in the correct way. I am so pleased to have all the advice that has been given. It is so unlike much of the misinformation on the web. Thank you. With best wishes, Michael - Michael B. Spring Associate Professor Information Science and Telecommunications Voice: (412)-624-9429 Fax: (412)-624-2788 WWW: http://www.sis.pitt.edu/~spring http://www.sis.pitt.edu/%7Espring Pmail: 701B SIS Building, 135 North Bellefield University of Pittsburgh, PA 15260 On 8/27/2013 5:22 PM, Christopher Schultz wrote: 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 - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
