decoding IPs in DB

Use this forum if you have installed hMailServer and want to ask a question related to a production release of hMailServer. Before posting, please read the troubleshooting guide. A large part of all reported issues are already described in detail here.
Post Reply
User avatar
PeterK2003
Normal user
Normal user
Posts: 126
Joined: 2005-07-20 17:08
Location: Catawissa, PA

decoding IPs in DB

Post by PeterK2003 » 2010-05-18 17:22

i used to decode the IP in the DB like so: inet_ntoa(`hm_greylisting_triplets`.`glipaddress1`) AS `IP'

Somewhere along the line that broke...i guessing IPv6 support

how can i decode them now?

Thanks,
Peter

^DooM^
Site Admin
Posts: 13861
Joined: 2005-07-29 16:18
Location: UK

Re: decoding IPs in DB

Post by ^DooM^ » 2010-05-18 18:03

IP's were stored as LONG. I'm not sure how IPV6 IP's are stored. can you post a few IP's from your table in their current format?
If at first you don't succeed, bomb disposal probably isn't for you! ヅ

User avatar
PeterK2003
Normal user
Normal user
Posts: 126
Joined: 2005-07-20 17:08
Location: Catawissa, PA

Re: decoding IPs in DB

Post by PeterK2003 » 2010-05-18 18:06

'1652667516' decoded with above method '39382e3132392e3138302e313234'

User avatar
PeterK2003
Normal user
Normal user
Posts: 126
Joined: 2005-07-20 17:08
Location: Catawissa, PA

Re: decoding IPs in DB

Post by PeterK2003 » 2010-05-18 18:07

and it is a bigint

^DooM^
Site Admin
Posts: 13861
Joined: 2005-07-29 16:18
Location: UK

Re: decoding IPs in DB

Post by ^DooM^ » 2010-05-18 18:23

hmm i see what you mean.

First one is 98.129.180.124
Second one i get back as an error using long2ip

inet_ntop is supposed to replace ip2long in PHP but is PHP5 only.
If at first you don't succeed, bomb disposal probably isn't for you! ヅ

User avatar
PeterK2003
Normal user
Normal user
Posts: 126
Joined: 2005-07-20 17:08
Location: Catawissa, PA

Re: decoding IPs in DB

Post by PeterK2003 » 2010-05-18 18:28

Just to make sure we are on the same page

if i do:

inet_ntoa(1652667516)

it returns

'39382e3132392e3138302e313234'

i am using the inet_ntoa function in mySQL not PHP

User avatar
dzekas
Senior user
Senior user
Posts: 2486
Joined: 2005-10-13 21:28
Location: Lithuania

Re: decoding IPs in DB

Post by dzekas » 2010-05-18 18:39


User avatar
PeterK2003
Normal user
Normal user
Posts: 126
Joined: 2005-07-20 17:08
Location: Catawissa, PA

Re: decoding IPs in DB

Post by PeterK2003 » 2010-05-18 18:51

is that what is going on though? I was just speculating that it had to do with ipv6.

^DooM^
Site Admin
Posts: 13861
Joined: 2005-07-29 16:18
Location: UK

Re: decoding IPs in DB

Post by ^DooM^ » 2010-05-18 19:35

1652667516 = 98.129.180.124 using php's long2ip();

Perhaps dzekas's link will shed more light.
If at first you don't succeed, bomb disposal probably isn't for you! ヅ

User avatar
dzekas
Senior user
Senior user
Posts: 2486
Joined: 2005-10-13 21:28
Location: Lithuania

Re: decoding IPs in DB

Post by dzekas » 2010-05-18 19:39

PeterK2003 wrote:is that what is going on though? I was just speculating that it had to do with ipv6.
You speculated that it is ipv6 and I searched for inet_ntoa and ipv6 support in mysql. Result - that bug report. But '39382e3132392e3138302e313234' is not ipv6 address. It is hexadecimal representation of '98.129.180.124'

Code: Select all

<?php
$str = '39382e3132392e3138302e313234';
$length = strlen($str);
for($i=0;$i<=$length;$i=$i+2) {
   echo chr(hexdec($str[$i] . $str[$i+1]));
}
I have other link for you. http://ronaldbradford.com/blog/tag/inet_ntoa/

User avatar
PeterK2003
Normal user
Normal user
Posts: 126
Joined: 2005-07-20 17:08
Location: Catawissa, PA

Re: decoding IPs in DB

Post by PeterK2003 » 2010-05-18 19:50

ohhh that looks like what is going on...guess a bug in mysql then.


Interesting.....

User avatar
dzekas
Senior user
Senior user
Posts: 2486
Joined: 2005-10-13 21:28
Location: Lithuania

Re: decoding IPs in DB

Post by dzekas » 2010-05-18 19:54

PeterK2003 wrote:ohhh that looks like what is going on...guess a bug in mysql then.


Interesting.....
Blog talks about quirk in mysql odbc and not in mysql itself. You have whole chain of programs. Check how address is stored in mysql database and do inet_ntoa queries in mysql client console. not in some program through odbc or some other library.

User avatar
PeterK2003
Normal user
Normal user
Posts: 126
Joined: 2005-07-20 17:08
Location: Catawissa, PA

Re: decoding IPs in DB

Post by PeterK2003 » 2010-05-18 20:37

cast(inet_ntoa(`hm_greylisting_triplets`.`glipaddress1`) as char) AS `IP`

^^^^that works

Post Reply