User change the case used when entering there username can break the IdP

[trsau]

When a user logs in the an all lowercase username to a service, then logs in to another service one or more letters in upper case the IdP will fail when attempting to write a record to the StorageRecords table.

Details of this issue can be found here: http://shibboleth.1660669.n2.nabble.com/ERROR-org-opensaml-storage-impl-JPAStorageService-337-td7619097.html.

To resolve the username can be forced to lowercase in the file conf/authn/password-authn-config.xml and setting shibboleth.authn.Password.Lowercase to TRUE. This applies a simple transformation on the username before it is validated and processed.

Note: For existing installs of IdPv3 the StorageRecords table and other tables may be populated with usernames of mixed case. These need to be rewritten so as to not impact existing users.

[trsau]

Some simple SQL commands to modify all usernames within various tables to be lowercase.

Fix StorageRecords;

select id, CONCAT( LOWER(SUBSTRING_INDEX(SUBSTRING_INDEX(id, ':', 1), ':', -1)), SUBSTRING(id, LOCATE(':', id), LENGTH(id))) as new_id from StorageRecords;

update StorageRecords set id = CONCAT( LOWER(SUBSTRING_INDEX(SUBSTRING_INDEX(id, ':', 1), ':', -1)), SUBSTRING(id, LOCATE(':', id), LENGTH(id)));

select value, CONCAT( LOWER(SUBSTRING_INDEX(SUBSTRING_INDEX(value, ':', 1), ':', -1)), SUBSTRING(value, LOCATE(':', value), LENGTH(value))) as new_value from StorageRecords;

update StorageRecords set value = CONCAT( LOWER(SUBSTRING_INDEX(SUBSTRING_INDEX(value, ':', 1), ':', -1)), SUBSTRING(value, LOCATE(':', value), LENGTH(value)));

Fix tb_st;

select * from tb_st;

update tb_st set uid = LOWER(uid);

Fix shibpid;

update shibpid set principalName = LOWER(principalName);

[vladimir-mencl-eresearch]

Hi Terry,

FYI, the StorageRecords magic would also transform base64-encoded session IDs (and therefore break them) - this can be fixed by adding WHERE LOCATE(':',id)>0 to the SQL...

Cheers, Vlad

[vladimir-mencl-eresearch]

Hi Terry,

I've done a bit more digging into this - especially around what's stored in StorageRecords.

The tricky parts are: consent storage has a record that stores the list of SPs, each prefixed with the username - and ALL of the instances of the username need to be converted:

["VMe34:https://attributes.dev.tuakiri.ac.nz/shibboleth","VMe34:https://wiki.test.bestgrid.org/shibboleth","vme34:https://attributes.dev.tuakiri.ac.nz/shibboleth"]

Also, current sessions might still old the incorrectly capitalized username even across the Tomcat restarts that turns on the Lowercase setting (if session storage is also in JPAStorage). The username is stored inside json data structures stored under id _session or authn/Password.

I could not do the processing of the list in a single expression, so I ended up defining MySQL functions to help with that.

The MySQL function definitions are:

CREATE FUNCTION fixValue (value LONGTEXT)
RETURNS LONGTEXT
RETURN CONCAT( LOWER(SUBSTRING_INDEX(SUBSTRING_INDEX(value, ':', 1), ':', -1)), SUBSTRING(value, LOCATE(':', value), LENGTH(value)));

DELIMITER $$
CREATE FUNCTION fixList (list LONGTEXT)
RETURNS LONGTEXT
BEGIN
DECLARE todo LONGTEXT;
DECLARE done LONGTEXT;
SET done = '';
SET todo = list;
iter: LOOP
   IF NOT LOCATE(',',todo)>0 THEN
       LEAVE iter;
   END IF;
   SET done = CONCAT(done,fixValue(SUBSTR(todo,1,LOCATE(',',todo))));
   SET todo = SUBSTR(todo,LOCATE(',',todo)+1);
END LOOP iter;
RETURN CONCAT(done,fixValue(todo));
END$$
DELIMITER ;

DELIMITER $$
CREATE FUNCTION fixEntry (data LONGTEXT, entryName LONGTEXT)
RETURNS LONGTEXT
BEGIN
DECLARE searchFor LONGTEXT;
DECLARE foundAt INT;
DECLARE found2 INT;
SET searchFor = CONCAT('"', entryName, '":"');
SET foundAt = LOCATE(searchFor,data);
IF foundAt > 0 THEN
  SET found2 = LOCATE('"',data,foundAt + LENGTH(searchFor));
  IF found2 > 0 THEN
    RETURN CONCAT(  SUBSTR(data,1,foundAt + LENGTH(searchFor) - 1),  LOWER(SUBSTR(data,foundAt + LENGTH(searchFor), found2 - foundAt - LENGTH(searchFor))), SUBSTR(data,found2));
  ELSE
    RETURN data;
  END IF;
ELSE
  RETURN data;
END IF;
END$$
DELIMITER ;

With these, the steps to fix StorageRecords are:

UPDATE StorageRecords SET id = CONCAT( LOWER(SUBSTRING_INDEX(SUBSTRING_INDEX(id, ':', 1), ':', -1)), SUBSTRING(id, LOCATE(':', id), LENGTH(id)))  WHERE LOCATE(':',id)>0;
UPDATE StorageRecords SET value = fixEntry(value,"nam") WHERE id ='_session';
UPDATE StorageRecords SET value = fixEntry(fixEntry(value,"LDAPN"),"U") WHERE id ='authn/Password';
UPDATE StorageRecords SET value = fixList(value) WHERE id LIKE '%:_key_idx';

The steps for fixing tb_st and shibpid remain the same as yours:

UPDATE tb_st SET uid = LOWER(uid);
UPDATE shibpid SET principalName = LOWER(principalName);

but I've also come up with two sanity checks to run before I do the fix:

• test how many users use non-lowercase capitalization
• sanity check that there are no clashes across case (both numbers in 2nd SELECT should come out the same):

SELECT * FROM tb_st WHERE lower(uid) NOT LIKE BINARY uid;
SELECT COUNT(DISTINCT(uid)), COUNT(DISTINCT(lower(uid))) FROM tb_st;

I'm now close to where I could send out the communication to IdP admins.

Hope you find this helpful.

Cheers, Vlad

[trsau]

Resolved by feature/shib340