* Case Number: a20140130.1 * Status: running * Claimants: Support (represented by Marcus M) * Respondents: CAcert * initial Case Manager: EvaStöwe * Case Manager: MartinGummi * Arbitrator: EvaStöwe * Date of arbitration start: 2014-01-31 * Date of ruling: 201Y-MM-DD * Case closed: 201Y-MM-DD * Complaint: Dispute due to wrong data because of UTF8/ISO-char * Relief: TBD Before: Arbitrator EvaStöwe (A), Respondent: CAcert (R), Claimant: Support (represented by Marcus M) (C), Case: a20140130.1 == History Log == . 2014-01-31 (issue.c.o) case [s20140130.88] . 2014-01-31 (iCM): added to wiki, request for CM / A . 2014-01-31 (A): I'll take care about this case and MartinGummi will be (CM) . 2014-02-01 (A): send init mail to representant of C and (to inform him about the case) the member with the problem with his account . 2014-02-01 anonymized version of queries proposed by representant of C in original dispute got approved by two software assessors (SA1, SA2, life, on paper) . 2014-02-04 (SA1): tested queries on testserver (with an id he picked) . 2014-02-04 (A): send transcript of CARS of software assessors to CM and the software assessors . 2014-02-04 (A): partial ruling I & execution order to critical team . 2014-02-05 (SA2): syntactic comments on transcript . 2014-02-05 (A): ensures SA2, that correct queries were used . 2014-02-05 (critical team): send results of queries . 2014-02-06 (A): asks support for contact information of assurers . 2014-02-06 (Support): provides contact information of assurers . 2014-02-07 (A): had a RL-chat with software assessor AS3 about what could have happend. SA3 told A about one or two assurances he could not enter because of some comparable name fileds some time ago . 2014-02-08 (A): asks assureres about what name they have assured . 2014-02-08 (AS1): there was the correct first name . 2014-02-09 (A): qestion to SA3 for more informatios to mentioned assurance . 2014-02-11 (SA3): gives details about said assurance . 2014-02-11 (A): asks support for more details on 2nd account to be able to question owner and possible other assurers . 2014-02-11 (Support): gives details about 2nd account and assurance . 2014-02-11 (A): asks AS4 about name of 2nd account at time of assurance . 2014-02-13 (SA3): assurance was at Froscon 2008, tried to enter assurance and detected wrong name at 2008-08-27 (via telephone) . 2014-02-18 (A/CM): discussed with software team what could have caused the problem. Some things were considered but discarced. (at SAP-team meeting) . 2014-02-18 (SA2, SA3): created and tested sql queries that may be needed by arbitration for analysis. The last one should only be executed if needed even with the result of the others. (at SAP-team meeting) . 2014-02-22 (AS3): there was the correct first name . 2014-02-26 (A): send reminder to AS4 . 2014-02-28 (Support): we corrected the name of the first account (with the help of another assurance) . 2014-03-02 (A): askes critical team about ideas what may have caused the issue . 2014-03-02 (A): informed owner of 2nd acc about procedure . 2014-03-02 (A): send partial ruling II to representant of C and critical team . 2014-03-04 (Critical team): answers question: do not have any idea - maybe it was caused by recent move of DB . 2014-03-04 (Critical team): executed parial ruling II and send results encrypted to A, CM, representant of C . 2014-04-02 (A): last warning to react to AS4 - else CCA violation has to be considered . 2014-04-03 (AS4): will look it up . 2014-05-11 (A): partial ruling III send to critical team, CM, forwarded to representant of C . 2014-05-11 (A): asked AS4 again . 2014-05-11 (critical team): executed partial ruling III and send results encrypted to indicated persons . 2014-05-14 (A): askes C as support member to give some specified information about a potential affected member who created the account recently . 2014-05-14 (C as Support): nothing special found in account of member (a hint that account may contain fake data) . 2014-06-09 (A): asks software team for script to address affected members per scripted mail . 2014-06-07 (A): remaids software team . 2014-06-28 (A of [[Arbitrations/a20140322.1|a20140322.1]]) removed block to change names in the DB . 2014-07-06 (A): remainds software team to proved A with the needed information for the scripted mail . 2014-07-06 (SA4): asks if it was already checked that the offending character is actually part of the name fields . 2014-07-06 (A): gives a short recap to SA4 about what was already done, again askes for the script . 2014-07-18 (A): decides to not follow the trail with AS4 any longer, as the result could be guessed after the result after partial ruling II. Informs AS4 and CM about this. . 2014-07-18 (A): reminder to software to provide needed script . 2014-08-09 (A): next reminder to software, suggests to use script from heartbleed mailing . 2014-08-10 (SA1): explains how the mail script works . [multiple vocal reminders from A to provide the script, during the software telcos, not individually documented] . 2015-07-15 (A): again asks software team to provide the necessary script tested and reviewed as necessary - sets deadline to 2015-08-05 Legend: * SA1-4: software assesssor 1-4 * AS1-3: assurer 1-3 * AS4: assurer4 (of 2nd account) == Original Dispute, Discovery (Private Part) (optional) == * '''Link to Arbitration case [[Arbitrations/priv/a20140130.1|a20140130.1 (Private Part)]], Access for (CM) + (A) only''' ## ==> INCLUDE SECTION BOT <> ## <== INCLUDE SECTION EOT ==== 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 == * A member contacted support because he created an account and got assured some time ago. * When he returned to his account recently, he discovered that his name is wrong (or at least not displayed correctly). * He contacted Support about this. Support got the same results on the name as the member. * While working on the support case, support filed a dispute to clarify what happend to the name (or it was entered wrongly to begin with). * An UTF8-error is suspected but since there also could be a problem with the database (or an actual change of the name) this has to be verified. * There is no other way to do this, than to ask for a direct look into the database as proposed by support. === Look up the first name and potential changes in the database === The following sql-queries were provided by support in the original dispute: . sql1 - to check the current data of the according field {{{#!highlight sql SELECT `id`, `fname`, `email` FROM `users` WHERE `id` = [xxx]; }}} . sql2 - to check if there has been recorded any change of the data. Should be empty. {{{#!highlight sql 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). * considerations for sql1 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. * considerations for sql2 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. * AS1 assured the correct name. The field was not broken. * AS3 assured the correct name. The field was not broken. * AS2 did not answer the mail, but since an answer from an assurer who assured the account before AS2 and an answer from an assurer who assured the account after AS2, an answer from AS2 is not really needed. === 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: * sql 3 {{{#!highlight sql SELECT `created` FROM `users` WHERE `id` = XXXX; }}} * sql 4 {{{#!highlight sql SELECT `fname`, `mname`, `lname`, `suffix` FROM `users` WHERE `id` = XXXX; }}} * sql 5 {{{#!highlight sql 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`); }}} * sql 6 {{{#!highlight sql SELECT `id`, `email`, `fname`, `mname`, `lname`, `suffix` FROM `users` 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 [[Arbitrations/a20131210.1|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 == . 2014-02-04 (A): partial ruling I & execution order to critical team . 2014-02-05 (critical team): send results of queries . 2014-03-02 (A): partial ruling II send to critical team . 2014-03-04 (critical team): executed and send results of queries as requested . 2014-05-11 (A): partial ruling III send to critical team, CM, forwarded to representant of C . 2014-05-11 (critical team): executed partial ruling III and send results encrypted to indicated persons == Similiar Cases == || [[Arbitrations/a20110330.1|a20110330.1]] || [[Arbitrations/a20110330.1|Name Change after Marriage w/ Assurance ]] || || [[Arbitrations/a20131210.1|a20131210.1]] || [[Arbitrations/a20131210.1|Find out some information about when accounts where created]] || ---- . CategoryArbitration . CategoryArbCaseAccountDataNameMismatch . CategoryArbCaseSystemTasks . CategoryArbCaseOthers