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 >
