Page MenuHomePhabricator
Create Task
Maniphest T315926

Provide a way to search all of the XFF addresses listed in the XFF string
Open, MediumPublicFeature
Actions

Description

I recently ran some checks on multiple accounts in a enwiki SPI case which came back with:

IP: xxx.xxx.xxx.xxx XFF: xxx.xxx.xxx.xxx, 136.226.255.25, 10.132.0.109

The intermediate address (136.226.255.25) is listed by Shodan as a webhost (see https://bullseye.toolforge.org/ip/136.226.255.25) and is blocked as a zscaler open proxy (https://en.wikipedia.org/wiki/Special:Contributions/136.226.255.25).

Using checkuser, I was able to search for the exit node (xxx.xxx.xxx.xxx), but what I really wanted to do was search to see all the traffic that was coming through the same 136.226.255.25 proxy intermediary. It is my understanding that while the intermediate XXF address is stored in the CU database, it is not stored in a way which makes it searchable, per my email conversation with Dreamy Jazz:

The reason why that check returned no results is that because CheckUser only currently stores the IP for xxx.xxx.xxx.xxx as a hex. This means while you see the three XFF IPs, you can only search by the last XFF IP which is xxx.xxx.xxx.xxx. I think this also extends to be the last trusted XFF IP.

Related Objects
Search...

StatusSubtypeAssignedTask
 OpenFeatureNoneT315926 Provide a way to search all of the XFF addresses listed in the XFF string
 OpenFeatureNoneT326379 Normalise the XFF and user agent columns
 OpenNoneT361139 Normalise the user agent column in CheckUser result tables
 OpenNoneT361208 Remove agent columns from CheckUser result tables
 OpenNoneT361206 Stop writing old for user agent schema migration on WMF wikis
 OpenNoneT361205 Stop writing old for user agent schema migration
 OpenNoneT361199 Set user agent schema migration config to read new on WMF wikis
 OpenNoneT361201 Set user agent schema migration config to read new in extension.json
 OpenNoneT361192 Read user agent strings from the cu_useragent table in Special:CheckUser
 OpenNoneT361193 Read user agent strings from the cu_useragent table in the CheckUser API
 OpenNoneT361195 Read user agent strings from the cu_useragent table in Special:Investigate
 OpenNoneT361198 Create a maintenance script to populate the cu_useragent table and agent_id columns with values from the agent columns
 OpenNoneT361196 Write to the cu_useragent table and agent_id columns on WMF wikis
 OpenNoneT361197 Write to the cu_useragent table and agent_id columns on by default in extension.json
 OpenNoneT361174 Write to agent_id columns in the CheckUserInsert service when migration configuration allows it
 OpenNoneT361172 Purge rows from cu_useragent
 OpenNoneT361173 Add schema migration config for cu_useragent table
 ResolvedDreamy_JazzT361140 Add user agent ID column to each CheckUser result table
 OpenNoneT361210 FYI: Changes to the cuc_agent column in the cu_changes table
 ResolvedDreamy_JazzT361928 Update mediawiki.org pages for schema changes made by adding the cu_useragent table
 ResolvedDreamy_JazzT359312 Create cu_useragent table
 ResolvedMarosteguiT361631 Unable to create table cu_useragent on labtestwiki
 DeclinedNoneT328289 update labtestwiki user and password
 ResolvedPapaulT369308 Decommission clouddb2002-dev.codfw.wmnet
 OpenNoneT378260 Retire labtestwiki
 ResolvedMarosteguiT361673 Filter cu_useragent on sanitarium

Event Timeline

RoySmith created this task.Aug 22 2022, 7:22 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptAug 22 2022, 7:22 PM
RoySmith updated the task description. (Show Details)Aug 22 2022, 7:26 PM
Dreamy_Jazz renamed this task from Provide a way to search by XFF addresses to Provide a way to search all of the XFF addresses listed in the XFF string.Aug 22 2022, 7:34 PM
Dreamy_Jazz triaged this task as Medium priority.Aug 22 2022, 7:47 PM
Dreamy_Jazz subscribed.

I think this is something that's needed based on the given use case. To achieve this would require a change in how the DB data is stored in the xff_hex column or a change to the DB to allow multiple XFF IPs. The solutions that I see are:

  • By some method store multiple XFF IPs in the xff_hex column. However, this makes searching by XFF a more computationally difficult task as the query will require a LIKE query. This would add SQL processing time especially as it would have to include wildcards on both sides. This could be done via a list separated by "|" characters like "|XFF hex 1|XFF hex 2|" with a LIKE query of "%|XFF hex 1|%".
  • Add extra columns to the DB to store up to certain number of XFF hexes. This would likely cover most cases of a couple of XFF hexes, but still limits and adds unnecessary columns in most cases.
  • Create a new table that stores the hexes for both IPs and XFF IPs (along with possibly other information such as the string representation) which is linked together via a many-to-many relationship to the cu_changes table. This would add two new tables but would move towards solving T305930 (as this would normalise the IP and XFF)
Dreamy_Jazz added a comment.Aug 29 2022, 9:36 PM

One issue with the third option is whether pruning the data would apply to the table with IPs and their hexes. This shouldn't contain any information unique to the client, but would have all the IPs used by all users. The many-to-many table would have to be also pruned but this would be a relatively easier task.

Dreamy_Jazz added a project: Schema-change.Sep 19 2022, 11:44 AM
Dreamy_Jazz moved this task from Unsorted to Add / Create on the Schema-change board.
Dreamy_Jazz added a subtask: T326379: Normalise the XFF and user agent columns.Jan 15 2023, 12:39 PM
Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL · Credits