Make your logons case sensitive

In contrast to an Internal Authentication Profile (which is case sensitive), if you use an Authentication Profile which stores its usernames and passwords in a MySQL database, by default both the username and password are case insensitive e.g. if the user’s logon name is ‘Joe Bloggs’, they could enter it as ‘jOe BlOgGs’ and it would still work.

This doesn’t really matter except when you use the [System]Client/User/Username tag to display the user’s name on a system screen, where you will find it is displayed as ‘jOe BlOgGs’.

To get around this problem, you enable the ‘Show advanced properties’ checkbox at the foot of the Edit Authentication Profile screen on the gateway and enter your own SQL queries to interrogate your MySQL database. This sounds daunting, but if you’ve been using the automatically generated tables up till now, just copy in the example query for each of the 3 boxes i.e. SELECT Username FROM USERS WHERE Username = '$username$' AND Password = MD5($password$)SELECT Rolename FROM ROLESandSELECT Rolename FROM USER_ROLE_MAPPING WHERE Username = '$username$'
Note that if you haven’t been using the MD5 hash function to obscure your passwords, the first query will have to be changed toSELECT Username FROM USERS WHERE Username = '$username$' AND Password = '$password$'
To make the Username part of the query case sensitive, you will have to use the MySQL COLLATE function with a case sensitive collation. Again this is easier to do than to say. All you have to do is update the first query as follows:SELECT Username FROM USERS WHERE Username = '$username$' COLLATE latin1_general_cs AND Password = '$password$'If you wanted your password case sensitive as well the query would become SELECT Username FROM USERS WHERE Username = '$username$' COLLATE latin1_general_cs AND Password = '$password$' COLLATE latin1_general_cs

Thanks for adding this - great tip!