npiesjrgbufkstj wrote:

Screenshot

Screenshot of the bug

Attached:

shogunat wrote:

It's not necessarily 69.130.114.203; it's the IP address of the person encountering the bug.

Trying to remove the user from the course fails with no error message.

MariaDB [enwiki_p]> select * from ep_students where student_user_id = 0;
+------------+-----------------+----------------------+----------------------+---------------------+---------------------+---------------------+-----------------------+

+------------+-----------------+----------------------+----------------------+---------------------+---------------------+---------------------+-----------------------+

+------------+-----------------+----------------------+----------------------+---------------------+---------------------+---------------------+-----------------------+
1 row in set (0.00 sec)

MariaDB [enwiki_p]> select * from ep_users_per_course where upc_user_id=0;
+-------------+---------------+----------+----------------+

+-------------+---------------+----------+----------------+

+-------------+---------------+----------+----------------+
1 row in set (0.00 sec)

<legoktm> oh crap. random people's IPs are being cached in varnish.
<Krenair> That's... not good.
<Krenair> It reveals to anyone that an IP has visited this exact page.

I ran delete from ep_users_per_course where upc_user_id=0 limit 1 on enwiki, which got rid of it from appearing on Special:Contributions. Still showing up on the EP course page.

Removing the user from ep_students removed them from everywhere I can see. Might still show up for people due to cache issues, I can't really do much about that.

Fixes probably need to be made in the EP extension to prevent this from happening again.

npiesjrgbufkstj wrote:

Note, going to be going somewhere where I don't have Internet, so won't be able to update this. I'm just the reporter; if it's fixed, go ahead and mark as fixed and don't wait for my input. I don't know much about it at all, nor how to fix it. Cheers.

We don't generally wait for the reporter to mark this as fixed. This bug will be left open at least until the EP extension is fixed itself (to prevent anons enrolling). I imagine we might want to purge the varnish cache for any affected anon contrib pages.

Change 139644 had a related patch set uploaded by Legoktm:
Update SpecialContributionsBeforeMainOutput hook handler

https://gerrit.wikimedia.org/r/139644

Change 139646 had a related patch set uploaded by Withoutaname:
Restrict enrollment to the "users" group

https://gerrit.wikimedia.org/r/139646

Change 139646 merged by jenkins-bot:
Restrict enrollment to the "users" group

https://gerrit.wikimedia.org/r/139646

Kunal's patch (not yet merged) should prevent this specific problem from happening again.

The underlying issue of user 0 sneaking into the database is still a mystery to me. The permissions patch makes it more explicit that anons should not be able to enroll, but there was already a check for users being logged in before enrollment.

Maybe there's some way to sneak an anon through special:enroll, or maybe there's another unusually way of enrolling an anon (such as the "add students" feature available to logged in users.)

Change 139644 merged by jenkins-bot:
Update SpecialContributionsBeforeMainOutput hook handler

https://gerrit.wikimedia.org/r/139644

andrew.green.df wrote:

Thanks again everyone for helping with this! There is one more place in the DB that needs to be fixed: a blob in the course_students column in one row of the ep_courses table.

(from Labs replica)

MariaDB [enwiki_p]> select * from ep_courses where course_id=359 \G

• 1. row ******* course_id: 359 course_org_id: 6 course_title: Simmons College/Intellectual Freedom - LIS 493 (Summer 2014) course_name: Intellectual Freedom - LIS 493

<snip/>

course_students: a:4:{i:0;s:0:"";i:1;i:6404;i:2;i:21613420;i:3;i:21613446;}

<snip/>

This is a serialized array of the user IDs of students in the course.

$ php -r "var_dump( unserialize( 'a:4:{i:0;s:0:\"\";i:1;i:6404;i:2;i:21613420;i:3;i:21613446;}' ) );"
array(4) {

[0]=>
string(0) ""
[1]=>
int(6404)
[2]=>
int(21613420)
[3]=>
int(21613446)

}

I think that string could be replaced with "a:3:{i:0;i:6404;i:1;i:21613420;i:2;i:21613446;}", which is the serialized version of the correct array of students in the course.

After that's done, the number of students shown on the course page [1] and the related institution page will be corrected the next time students are added to or removed from the course.

I tried this locally and it seemed to work. :)

P.S. A fix is in the works for the EducationProgram extension issue that caused this.

[1] At the bottom of https://en.wikipedia.org/wiki/Education_Program:Simmons_College/Intellectual_Freedom_-_LIS_493_%28Summer_2014%29

andrew.green.df wrote:

P.S. The contents of course_students in that row of ep_courses should be re-checked right before any changes are made, in case students have been added or removed recently. (I don't have the rights to fix the row myself.)

I'd rather not fiddle with the database directly if possible, is there something that can regenerate that in PHP with eval.php?

Like:

$course = new Course( $id );
$course->updateStudents();

That's probably not the API, but just something that doesn't require directly dumping serialized PHP into a database row.

andrew.green.df wrote:

Sure. I can do a little maintenance script...

Change 140137 had a related patch set uploaded by Alex Monk:
Prevent ApiAddStudents enrolling invalid users

https://gerrit.wikimedia.org/r/140137

Change 140137 merged by jenkins-bot:
Prevent ApiAddStudents enrolling invalid users

https://gerrit.wikimedia.org/r/140137

Andrew, is this fixed now?

andrew.green.df wrote:

Almost! The change that prevents it happening is merged on the cluster. The maintenance script (which will actually check all courses for this issue and fix any that have it) is coming in a little while, apologies for the delay!! :)

(No additional problems have been detected so far... ;) )

Change 143246 had a related patch set uploaded by AndyRussG:
Maintenance script to unenroll invalid users

https://gerrit.wikimedia.org/r/143246

andrew.green.df wrote:

The maintenance script has two modes:

• To repair a course specified by ID.
• To check for and repair instances of the same problem anywhere on a wiki.

Most of the DB issues were fixed by hand on enwiki. The remaining issue can be fixed with the --courseId=359 option.

To check for the problem on other wikis with the EducationProgram extension, run the script with no arguments.

Change 143246 merged by jenkins-bot:
Maintenance script to unenroll invalid users

https://gerrit.wikimedia.org/r/143246

andrew.green.df wrote:

Script was run successfully on production (thanks, Legoktm!) Enwiki course fixed, no problems detected elsewhere.