birmingham@ic.fbi.gov , phoenix@ic.fbi.gov , fbise@leo.gov
NickSoft Linux Cookbook Index NickSoft Linux Cookbook
Quick howto's, Real live examples.
 
 FAQFAQ   SearchSearch   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 


Storing ips in database as hexadecimal strings

 
Post new topic   Reply to topic    NickSoft Linux Cookbook Index -> MySQL
View previous topic :: View next topic  

Vote for this article
1 - useless
0%
 0%  [ 0 ]
2 - bad
0%
 0%  [ 0 ]
3 - not so bad
0%
 0%  [ 0 ]
4 - good
0%
 0%  [ 0 ]
5 - excelent
0%
 0%  [ 0 ]
Total Votes : 0

Author Message
NickSoft
Site Admin


Joined: 13 Nov 2006
Posts: 22

PostPosted: Wed May 09, 2007 5:40 pm    Post subject: Storing ips in database as hexadecimal strings Reply with quote

PHPBB2 stores ips as hexadecimal strings. php function that could convert ip to phpbb2 ip string is this:
Code:
echo sprintf('%08X',ip2long('192.168.0.1'));//result: C0A80001

converting back hexadecimal IP to dot style with php can be done with base_convert function:
Code:
echo long2ip(base_convert("C0A80001",16,10)); //result: 192.168.0.1

This way IPs can be indexed better and you can search for class A, B and C networks. For example to see all ips from 192.168.0.0/24 network you could use this query:
Code:
select * from users where ip like 'C0A800%';


if you need to convert hexadecimal to dot style ips within mysql client or phpMyAdmin you can define a function hex2ip (for mysql 5.x):

Code:
DELIMITER ;;

DROP FUNCTION IF EXISTS hex2ip;;
CREATE FUNCTION hex2ip (x VARCHAR(8))
  RETURNS varchar(15)
  DETERMINISTIC NO SQL
  BEGIN
    DECLARE x1 varchar(2);
    DECLARE x2 varchar(2);
    DECLARE x3 varchar(2);
    DECLARE x4 varchar(2);
    set x1=substring(x,1,2);
    set x2=substring(x,3,2);
    set x3=substring(x,5,2);
    set x4=substring(x,7,2);
    RETURN concat(conv(x1,16,10),'.',conv(x2,16,10),'.',conv(x3,16,10),'.',conv(x4,16,10));
  END;;

DELIMITER ;


Then select ip from users table:
Code:
select ID,hex2ip(IP) from users;
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    NickSoft Linux Cookbook Index -> MySQL All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum




MLDb.org Magic Eight Ball Croler Web Search Croler Web Directory Bianca Ryan MyBestMatch.net Microlab.info Digger Services Sofia

Powered by 220V

AbuseIPDB Contributor Badge