Before: Arbitrator EvaStöwe (A), Respondent: CAcert (R), Claimant: Support (represented by Marcus M) (C), Case: a20140130.1

History Log

Legend:

Original Dispute, Discovery (Private Part) (optional)

EOT Private Part

original Dispute

Parts in {} were present in the original dispute and are anonymized here. 

> Hi arbitration,
> 
> we have a ticket {ticket number} in support
> 
> {email-header}
> 
> Message:
> Dear CAcert folks,
> 
> I logged in after a long time to see that my firstname is incompletely 
> shown as {broken name}. This
> looks like an UTF8/ISO-char encoding issue...
> 
> It has to be correctly written like {correct name}. (it's an "o" with two dots 
> on it and worked as
> of 2007)"
> 
> I will handle the ticket similar to PrecedentCases a20110330.1[1].
> But we should check in a first step the data record of the user to see 
> the actual data prior to changing the account data.
> Select id, fname, email from users where id={user id} (The users id) Check 
> the current data
> Select * from adminlog where uid={user id} Check if there has been recorded
> 
> any change of the data. Should be empty.
> 
> In a second step we should check the database about similar data 
> problems. The software team should find a solution with the outcome of 
> the check.
> 
> The account of {name of member} shows {some account information, that may have been usfull for the discovery if it would have been otherwise}.
> 
> [1] https://wiki.cacert.org/Support/Handbook/PrecedentCases/a20110330.1

Discovery

Look up the first name and potential changes in the database

The following sql-queries were provided by support in the original dispute: 

   1     SELECT `id`, `fname`, `email` FROM `users` WHERE `id` = [xxx];


   1     SELECT * FROM `adminlog` WHERE `uid` = [xxx];

Where [xxx] is the id of the user in question.

Both queries were reviewed by two software assessors (Benny B, Michael T). One of them tested the query on the testserver.

One of the software assessors added: 

Query OK for xxx (specific) user when proper arbitration case justifies retrieval of the above data. For retrieving the changes to the account the 2nd query is fine, while potentially yielding private information affecting the support staff who worked at the account (UID) as well as old data of the account.

The reservations of software assessor have to be taken seriously (and should have been considered anyway).

The first query should only provide data already known to A, CM and the according support member. The only person who will see the data beside of them will be one critical team member. Critical team members have an ABC and are by this specialy tested and bound to SP and should be sensitised for privacy issues. The privacy breach is also comparably small since only email and first name will be shown.

The according member is also informed about the case and the proposal to look into his account.

Since there is no other way to verify the entry of the according field and to check the consistency of the database, the first query should be executed.

The question what changes were done to the account can not be answered without this kind of query. This check is needed to verify if there were changes to the name field and by whom in what context.

Support team members should know that their actions can be part of an arbitration case, since arbitration is the only way to review support. While this is not a case to review support actions, the privacy considerations regarding support team members can be based on this. They should also be aware that arbitration probably has to take a look at their actions if arbitration cannot solve a case otherwise. Also the only data of support members by this query is their id.

The query may also provide some other details of the user. But again the member is informed that we have to take a look into his data to solve the issue he brought up himself.

Again only two arbitrators, a critical team member and the representant of support will see the results of the query.

Even if the first query results in the name the user probably entered, it has to be ensured that there was not changed by someone else inbetween - and maybe set back again.

For the second part of the dispute, some more information if (or what) was changed is needed.

Since there is no other way to provide this informations and all affected people should be aware of the need to execute such a query, this query should be executed.

Result of Queries

sql 1: provided the same result as visible to the member and support.

sql 2: there was no change to the account of the member in the adminlog.

Contact previous assurers

The database contained the broken name entrie.

[Edit 2014-03-02: was fixed by support with another assurance.]

The member claims that he originally entered his correct name.

There were three assurances on his account, all from 2007. The assurers should not have entered the assurance if the first name was broken back then.

Nontheless they should be asked about what they have seen in the name field when they entered their assurance, to be sure that back then the entrie was not broken.

Sadly this will not narrow down the timeframe for the break of the field, much.

All three assureres were asked about what they assured.

Locate what broke the entry

It looks like the name was entered, saved and displayed correctly at least until end of 2007.

At the beginning of 2014 the name is broken in the database and gets displayed like this.

There was no adminstrative change on the name field in between. So it probably was no intentual change and is the result of an error. Since the first character that is displayed wrong is not a plain latin character (and our database works with UTF8), some kind UTF8-error should be a first candidate.

Software team and critical team should be asked if they can remember anything that may be responsible for the change or error.

Since the same error may affect more accounts, the teams should also be asked about ideas how to lokate likewise errors in other accounts.

Results from correspondences with software team

When software team was asked if they have any idea what could have caused the problem, they agreed that it looks like an encoding problem. One software assessor also remembered a comparable issue in a name that he detected some time ago, when he tried to assure said account.

This second account was assured once, not long after it was created and only days before the software assessor encountered the broken name when he tried to do his assurance. This occured in late summer of 2008.

Until now, SA4 (the assurer of this account) did not response to the question what name was present.

By the dates of this account we may have some idea about when the problem may have occured, but we cannot be sure. Software team tried to remember any special incident or patch around that dates, but they could not come up with anything that matched the times.

One idea to get some more insight into the matter is to search the database for other accounts with the same problem. If they only (or mostly) would occure for accounts created before a specific date the problem may be located before that day.

Since some query to search for according accounts is needed to be able to verify how wide the problem is spread, such a query is needed, anyway.

Software created and tested the following sql-queries: 

   1 SELECT `created` FROM `users` WHERE `id` = XXXX;


   1 SELECT `fname`, `mname`, `lname`, `suffix` FROM `users` WHERE `id` = XXXX;


   1 SELECT YEAR(`created`), MONTH(`created`), COUNT(*) FROM `users`
   2 WHERE `fname` LIKE '%?%' OR `mname` LIKE '%?%' OR `lname` LIKE '%?%' OR `suffix` LIKE '%?%'
   3 GROUP BY YEAR(`created`), MONTH(`created`);


   1 SELECT `id`, `email`, `fname`, `mname`, `lname`, `suffix`
   2 FROM `users`
   3 WHERE `fname` LIKE '%?%' OR `mname` LIKE '%?%' OR `lname` LIKE '%?%' OR `suffix` LIKE '%?%';

The first two queries are designed to look up the date when the two accounts were created and to get likewise information for the second account that already was looked up for the first account, to check that the problem is the same.

Software team and A agreed that the last query should be postponed until the others were used to learn if the last one would really be needed or if something else would be more appropriate, because it would yield a lot of personal information.

considerations of sql 3-6

The general privacy considerations of above concerning who will see the data.

sql 4 is of the same nature as sql 1, so the same considerations have to apply.

sql 3 only looks up one more date of the accounts. Since support and arbitration already can guess the area of this date because of the information provided by a20131210.1 it does not violate the privacy of the members a lot (because we already have an idea of this) while narrowing down the range when the problem may have occured.

However, the member of the second account - who was not involved in this case, so far - should be informed about the procedure.

sql 5 does not provide any personal data of any member. It only provides the number of how many according accounts where created in which month that (probably) have the same issue. However there may be a "?" in their account because of other reasons.

Since no personal data is provided and at least some kind of query is needed to get a glimps of how many accounts are affected, this query should be executed.

sql 6 Provides a lot of personal information for every member that is affected by the problem. However to fix their accounts this information may be needed. But this query should not be used before we did not check how many accounts will be affected through sqp 5.

Results of second set of sql-queries

The sql queries that were executed to learn more about the affected accounts revealed that the issue for the second account seams to be the same. Both account was created quite shortly before the (first) assurances were made.

The first approach to learn about the range of the issue with sql5 gave:

YEAR(created)

MONTH(created)

COUNT(*)

2004

7

1

2005

8

1

2007

7

7

2007

8

5

2007

9

4

2007

10

11

2007

11

12

2007

12

7

2008

1

4

2008

2

8

2008

3

8

2008

4

8

2008

5

11

2008

6

10

2008

7

9

2008

8

6

2008

9

8

2008

11

1

2009

3

1

There are 122 accounts with names containing "?" in our database. All but 4 of them are between July of 2007 and September of 2008. The other four may be related or be there for other reasons.

As this is a handable number of accounts, there is little reason to have a "?" in the name fields it makes sense to also execute sql6. The resulting information is needed to a) be sure which accounts are affected by the issue and b) get the accounts fixed.

Ruling

Partial Ruling I

I ask the critical team to execute the following sql-queries: 

SELECT `id`, `fname`, `email` FROM `users` WHERE `id` = [xxx];

SELECT * FROM `adminlog` WHERE `uid` = [xxx];

The results of those queries should be send encrypted to [CM] as CM, [Marcus M] as representant of the claimant and me as A.

-- Cologne, 2014-02-05

[xxx] replaced with acc-id of account in question

Partial Ruling II

Critical team should execute the following five sql-queries. The results should be send encrypted to [A] as arbitrator, [CM] as case manager and [Marcus M] as representantive of the claimant of this case. 

SELECT `created` FROM `users` WHERE `id` = [id1];

SELECT `created` FROM `users` WHERE `id` = [id2];

SELECT `fname`, `mname`, `lname`, `suffix` FROM `users` WHERE `id` = [id2];

SELECT * FROM `adminlog` WHERE `uid` = [id2];

SELECT YEAR(`created`), MONTH(`created`), COUNT(*) FROM `users`WHERE
`fname` LIKE '%?%' OR `mname` LIKE '%?%' OR `lname` LIKE '%?%' OR
`suffix` LIKE '%?%'GROUP BY YEAR(`created`), MONTH(`created`);

-- Cologne, 2014-03-02

[id1], [id2] replaced with acc-id of accounts in question

Partial Ruling III

Critical team should execute the following sql-query. The result should be send encrypted to me as arbitrator and Martin Gummi as case manager of this case. 

SELECT `id`, `email`, `fname`, `mname`, `lname`, `suffix`
FROM `users`
WHERE `fname` LIKE '%?%' OR `mname` LIKE '%?%' OR `lname` LIKE '%?%' OR
`suffix` LIKE '%?%';

-- Cologne, 2014-05-11

Execution

Similiar Cases

a20110330.1

Name Change after Marriage w/ Assurance

a20131210.1

Find out some information about when accounts where created

Arbitrations/a20140130.1 (last edited 2015-08-01 17:27:09 by EvaStöwe)