Shared IP tab does not have stable ordering resulting in unreliable matching across multiple users

X

Xon

Well-known member
Affected version
2.2.15
\XF\Repository\Ip::getSharedIpUsers has an odd failure mode where shared ip tab for two users that should show each other do not reliably show each other on the shared IP list.

This is because of a lack of order by clauses in the two queries:

PHP: 
$recentIps = $db->fetchAllColumn("
    SELECT DISTINCT ip
    FROM xf_ip
    WHERE user_id = ?
       AND log_date > ?
    LIMIT 500
", [$userId, $cutOff]);
This query has no explicit order clause, and with enough matches it is effectively a random subset of the recent-ish IPs.

Adding order by log_date desc should be enough.

Similarly, when getting IP log information, there is also a lack of order clauses allowing most recent matches to be missed.
SQL: 
$ipLogs = $db->fetchAll('
    SELECT user_id,
       ip,
       MIN(log_date) AS first_date,
       MAX(log_date) AS last_date,
       COUNT(*) AS total
    FROM xf_ip
    WHERE ip IN (' . $db->quote($recentIps) . ')
       AND user_id <> ?
       AND user_id > 0
       AND log_date > ?
    GROUP BY user_id, ip
    LIMIT 1000
', [$userId, $cutOff]);

Adding order by last_date desc, ip, user_id should be enough.

Finally, the list of users has no explicit sort clause but is likely by user-id (aka primary key). This should be explicitly sorted; either by username or by most recent match. I think most recent match would be most appropriate, and means the output can be built by looping over the $ipLogs array instead of $users
 
Top Bottom