PHP MySQL Select varbinery Field output wrong

[imperium2335]

Hi,

 

How do I fetch the true value of a varbinary field in my database?

 

All I get when fetching the column are funny letters/symbols etc.

 

I have IP addresses coded up in binary like 56bcca32 = 86.188.202.50.

 

But mysql will not fetch them properly.

 

my script:

function encode_ip($dotquad_ip)
        {
            $ip_sep = explode('.', $dotquad_ip);
            return sprintf('%02x%02x%02x%02x', $ip_sep[0], $ip_sep[1], $ip_sep[2], $ip_sep[3]);
        }

$userIp = '81.157.3.249';//$_SERVER['REMOTE_ADDR'] ;

$piwikIp = encode_ip($userIp) ;

include("system/dbconnect.php") ;

$result = mysql_query("SELECT idvisit
				   FROM piwik_log_visit
				   WHERE location_ip = '$piwikIp'
				   ORDER BY visit_last_action_time DESC
				   LIMIT 1")or die(mysql_error()) ;

$row = mysql_fetch_assoc($result) ;

echo $row['idvisit'] ;

[dzelenika]

I don't understand why are You storing IP address  in bnary instead in varchar?

[imperium2335]

Me neither, this is a opensource analytics package called piwik, not something I made.

[dzelenika]

i believe, Your varbinary data consists of 4 bytes. Each byte has ASCII code equal to adequate part of IP address

[imperium2335]

So how do I modify my SQL statement to retrieve the IP address?

[dzelenika]

try folowing:

 

$ip = $row['idvisit'] ;

echo ord(substr($ip, 0, 1)) . "." . ord(substr($ip, 1, 1)) . "." . ord(substr($ip, 2, 1)) . . ord(substr($ip, 3, 1))  ;

[imperium2335]

No that doesn't work, because the value of $row['idvisit'] is not correct, it's just a mishmash of symbols etc. It needs to be pulled out and converted using MYSQL.

[dcro2]

What exactly should `idvisit` contain? Another IP address like `location_ip`?

[imperium2335]

idvisit is the visitors id number, ip_location is the binary encoded IP (varbinary)

[dcro2]

Piwik uses inet_pton to convert an ip string to binary and inet_ntop to convert it back.

"WHERE location_ip = '".inet_pton('81.157.3.249')."'"

 

If you have Windows or PHP < 5.1 (PHP >= 5.3 includes it with Windows), it also includes some functions you can use instead:

/**
* Converts a packed internet address to a human readable representation
*
* @link http://php.net/inet_ntop
*
* @param string $in_addr 32-bit IPv4 or 128-bit IPv6 address
* @return string|false string representation of address or false on failure
*/
function php_compat_inet_ntop($in_addr)
{
// in case mbstring overloads strlen function
$strlen = function_exists('mb_orig_strlen') ? 'mb_orig_strlen' : 'strlen';

$r = bin2hex($in_addr);

switch ($strlen($in_addr))
{
	case 4:
		// IPv4 address
		$prefix = '';
		break;

	case 16:
		// IPv4-mapped address
		if(substr_compare($r, '00000000000000000000ffff', 0, 24) === 0)
		{
			$prefix = '::ffff:';
			$r = substr($r, 24);
			break;
		}

		// IPv4-compat address
		if(substr_compare($r, '000000000000000000000000', 0, 24) === 0 &&
			substr_compare($r, '0000', 24, 4) !== 0)
		{
			$prefix = '::';
			$r = substr($r, 24);
			break;
		}

		$r = str_split($r, 4);
		$r = implode(':', $r);

		// compress leading zeros
		$r = preg_replace(
			'/(^|:)0{1,3}/',
			'$1',
			$r
		);

		// compress longest (and leftmost) consecutive groups of zeros
		if(preg_match_all('/(?:^|(0(:|$))+/', $r, $matches))
		{
			$longestMatch = 0;
			foreach($matches[0] as $aMatch)
			{
				if(strlen($aMatch) > strlen($longestMatch))
				{
					$longestMatch = $aMatch;
				}
			}
			$r = substr_replace($r, '::', strpos($r, $longestMatch), strlen($longestMatch));
		}

		return $r;

	default:
		return false;
}

$r = str_split($r, 2);
$r = array_map('hexdec', $r);
$r = implode('.', $r);
return $prefix . $r;
}

/**
* Converts a human readable IP address to its packed in_addr representation
*
* @link http://php.net/inet_pton
*
* @param string $address a human readable IPv4 or IPv6 address
* @return string in_addr representation or false on failure
*/
function php_compat_inet_pton($address)
{
// IPv4 (or IPv4-compat, or IPv4-mapped)
if(preg_match('/(^|([0-9]+)\.([0-9]+)\.([0-9]+)\.([0-9]+)$/i', $address, $matches))
{
	for($i = count($matches); $i-- > 2; )
	{
		if($matches[$i] > 255 ||
			($matches[$i][0] == '0' && strlen($matches[$i]) > 1))
		{
			return false;
		}
	}

	if(empty($matches[1]))
	{
		$r = ip2long($address);
		if($r === false)
		{
			return false;
		}

		return pack('N', $r);
	}

	$suffix = sprintf("%02x%02x:%02x%02x", $matches[2], $matches[3], $matches[4], $matches[5]);
	$address = substr_replace($address, $matches[1] . $suffix, strrpos($address, $matches[0]));
}

// IPv6
if(strpos($address, ':') === false ||
	strspn($address, '01234567890abcdefABCDEF:') !== strlen($address))
{
	return false;
}

if(substr($address, 0, 2) == '::')
{
	$address = '0'.$address;
}

if(substr($address, -2)  == '::')
{
	$address .= '0';
}

$r = explode(':', $address);
$count = count($r);

// grouped zeros
if(strpos($address, '::') !== false
	&& $count < 
{
	$zeroGroup = array_search('', $r, 1);

	// we're replacing this cell, so we splice (8 - $count + 1) cells containing '0'
	array_splice($r, $zeroGroup, 1, array_fill(0, 9 - $count, '0'));
}

// guard against excessive ':' or '::'
if($count > 8 ||
	array_search('', $r, 1) !== false)
{
	return false;
}

// leading zeros
foreach($r as $v)
{
	if(strlen(ltrim($v, '0')) > 4)
	{
		return false;
	}
}

$r = array_map('hexdec', $r);
array_unshift($r, 'n*');
$r = call_user_func_array('pack', $r);

return $r;
}

`idvisit` is an INT(10) so you shouldn't have problems with that.

[imperium2335]

Thanks, but I'm now getting:

 

Invalid utf8 character string: ''

 

My php is now:

 

$userIp = '81.157.3.249';//$_SERVER['REMOTE_ADDR'] ;

include("system/dbconnect.php") ;

$result = mysql_query("SELECT idvisit
				   FROM piwik_log_visit
				   WHERE location_ip = " . inet_pton($userIp) . "
				   ORDER BY visit_last_action_time DESC
				   LIMIT 1")or die(mysql_error()) ;

$row = mysql_fetch_assoc($result) ;

echo $row['idvisit'] ;

[dcro2]

Try putting single quotes around it.

[imperium2335]

Now I get:

 

Warning: inet_pton() [function.inet-pton]: Unrecognized address $userIp in /home/ukautoma/public_html/test.php on line 15

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY visit_last_action_time DESC LIMIT 1' at line 4

[dcro2]

Sorry, here's what I meant:

$result = mysql_query("SELECT idvisit
				   FROM piwik_log_visit
				   WHERE location_ip = '" . inet_pton($userIp) . "'
				   ORDER BY visit_last_action_time DESC
				   LIMIT 1")or die(mysql_error()) ;

Notice the single quotes around the value of location_ip

[imperium2335]

thanks! Works perfectly.