Decoding IPs stored as hex in database

[vord]

Does anyone know how to decode IP addresses stored in the database, specifically IP in the xf_ip table? The format looks like it was changed in 1.3 or 1.4 to 8 digit hex, presumably to cope with IPv6.

I'm trying to convert the hex into a long IP to use in a country lookup table as part of a custom spam tool which runs externally to xenforo and compares actual country with the country entered as a required field during registration.

 

[Mike]

They're not actually in hex; they're binary representations of the 4 or 16 byte address. You can use one of the helper functions in XenForo_Helper_Ip to decode it to a human readable version.

 

[vord]

Thanks, that's fixed it. The IPs come out of the database in binary. The file \library\XenForo\Helper\Ip.php has a function convertIpBinaryToString() which converts the binary into IP format.

 

[AndyB]

The file \library\XenForo\Helper\Ip.php has a function convertIpBinaryToString() which converts the binary into IP format.

The xf_ip table ip field has data like this "1804603c". What do we need to do with this data before using the convertIpBinaryToString() code?

 

[vord]

The database has a DEHEX function so the data comes out in binary format using an ordinary select statement.

 

[AndyB]

The database has a DEHEX function so the data comes out in binary format using an ordinary select statement.

Thank you, I got that to work.

Out of curiosity is there a way to do this in PHP, that is "data comes out in binary format". So for example I input to my script "1804603c" and it's put into the same format as I get when I do a select query with MySQL.

 

[vord]

There's another function on the same page as convertIpBinaryToString() it's hex2bin() from memory. That'll convert hex to binary. The function has other stuff inside which I removed for my own use before I realised I didn't need it.

 

[AndyB]

Got it.

<?php

$ip = '1804603c';

$ip = pack('H*', $ip);

if (strlen($ip) == 4)
{
	// IPv4
	$parts = array();
	foreach (str_split($ip) AS $char)
	{
		$parts[] = ord($char);
	}

	echo implode('.', $parts);
}
else if (strlen($ip) == 16)
{
	// IPv6
	$parts = array();
	$chunks = str_split($ip);
	for ($i = 0; $i < 16; $i += 2)
	{
		$char1 = $chunks[$i];
		$char2 = $chunks[$i + 1];

		$part = sprintf('%02x%02x', ord($char1), ord($char2));
		if ($shorten)
		{
			// reduce this to the shortest length possible, but keep 1 zero if needed
			$part = ltrim($part, '0');
			if (!strlen($part))
			{
				$part = '0';
			}
		}
		$parts[] = $part;
	}

	$output = implode(':', $parts);
	if ($shorten)
	{
		$output = preg_replace('/((^0|:0){2,})(.*)$/', ':$3', $output);
		if (substr($output, -1) === ':' && (strlen($output) == 1 || substr($output, -2, 1) !== ':'))
		{
			$output .= ':';
		}
	}

	echo strtolower($output);
}
else if (preg_match('/^[0-9]+$/', $ip))
{
	echo long2ip($ip + 0);
}
else
{
	echo 'return false';
}

?>