View Issue Details

IDProjectCategoryView StatusLast Update
0001543Main CAcert Websiteaccount administrationpublic2022-07-07 16:21
Reporteregal Assigned Toegal  
PrioritynormalSeverityminorReproducibilityN/A
Status fix availableResolutionopen 
PlatformMain CAcert WebsiteOSN/AOS Versionstable
Summary0001543: email-adresses without member-id need to be deleted
DescriptionAs long as the "strict" setting was active on webdb1 there were 0000087:0000028 email-addresses, which do not belong to any account in email-table.

Normally emails of members, which are not verified, are deleted after 0000011:0000024 hours (according to our text) or 0000099:0000048 hours (according to our coding).

As the memid is empty, these email-addresses would stay in email-table forever ... blocking these email-addresses (users) to join cacert.
Steps To Reproducenone (as strict-mode was switched off for database)
Additional InformationAccording to our coding a "memid=0" should not be possible ...

... but in strict mode for mariadb it was not possible to create an entry in user-table ... resulting in "id=0". This value was added to email-table:

> select * from email where memid=0 order by id desc;
+--------+-------+--------------------------------+---------------------+---------------------+---------------------+------+----------+
| id | memid | email | created | modified | deleted | hash | attempts |
+--------+-------+--------------------------------+---------------------+---------------------+---------------------+------+----------+
| 567890 | 0 | email@domain.tld | 2022-07-04 12:34:56 | 2022-07-04 12:34:56 | 0000-00-00 00:00:00 | | 2 |
(...)

(Obviously it's not a productive record above ... ;-) )

If a valid user-record could be created, the field memid is the reference to the user-record and therefore not 0.
Tagslogin error, migration, webdb
Reviewed by
Test Instructionsdatabase needs to be modified on testserver to reproduce this issue ... and to see, if a fix works

Activities

egal

2022-07-06 20:51

administrator   ~0006121

some additional information:

there is a script named "removedead.php", which is called every hour to remove unverified accounts and their email-adresses.

this script could be adapted, to remove emails-addresses with memid=0 after some time (24 hrs? 48 hrs?), too.

another solution could be to authorize critical to remove these 0000075:0000030 entries from the database using an sql-command (one-shot only)

jandd

2022-07-07 16:20

administrator   ~0006125

fix available as https://code.cacert.org/cacert/cacert-webdb/pulls/2/files

Issue History

Date Modified Username Field Change
2022-07-06 20:47 egal New Issue
2022-07-06 20:47 egal Tag Attached: login error
2022-07-06 20:47 egal Tag Attached: migration
2022-07-06 20:47 egal Tag Attached: webdb
2022-07-06 20:51 egal Note Added: 0006121
2022-07-06 20:52 egal Assigned To => jandd
2022-07-07 16:08 jandd Status new => needs work
2022-07-07 16:20 jandd Status needs work => fix available
2022-07-07 16:20 jandd Note Added: 0006125
2022-07-07 16:21 jandd Assigned To jandd => egal