Search for Partial IP Address

Alteran Ancient

Well-known member
This is a rather tricky one.

I have some partial IP addresses from the IRC server our channel resides on. To protect the users, IP addresses and hostnames are partially masked.

One IRC user in particular spiked our attention when they started garnering an unhealthy interest in a rather vulnerable member - I won't bore you with the details.

In a nutshell, I need to search my XenForo User Database for an IP in the form: "*.*.X.Y". (e.g. "*.*.111.255") This is a challenge, because it's completely the other way around from what would be expected.

Thanks for the assistance. It's a rather serious matter that I need to get sorted out if at all possible, and it could potentially lead to a restraining order on someone if I find the information I expect to find...


XenForo stores them in IP v4 long, you will have to convert it with long2ip() first to make them human readable dotted address, and then match the last 2 parts against the one you want to check against.

XenForo_Model_Ip (library/XenForo/Model/Ip.php)

Alteran Ancient

Well-known member
I eventually stumbled across a rather hacky way to do this.

Kudos go to Jake for this query:

SELECT DISTINCT user.username, INET_NTOA( ip.ip )
FROM xf_ip AS ip
LEFT JOIN xf_user AS user ON ( user.user_id = ip.user_id )
LIMIT 0 , 10000
Then just Ctrl-F (or Cmd-F) and start typing away - your browser will do the rest.

This SQL query could be a bit intensive for larger boards, though, so there might be a better way to search in future.


Hopefully this helps:

Screen Shot 2012-04-26 at 2.52.31 AM.png

The .zip file, unpack it, and put findIp.php inside your xenforo's directory (not inside library!) and then load findIp.php from the browser. Enter the partial IP and press submit, hopefully it spits out the matching users.

Anyway, I hope what I made is correct, I am not a good coder by far, and if someone knows how to improve on this feel free to edit it.