discussion convert ip address to country

[Paul Purvis]

http://www.powerbasic.com/support/pb...ad.php?t=37614

[Paul Purvis]

because there are many different formats to different sources of listing of ip address to countries
the best thing would probably be to have a standard.
so here is going to be my standard file format for a database file.
the change will be in a future program
"ip2cntry.dat" will be the database that ip2cntry looks for

ip addresses will be in whole number format format
country will be two letters abbreviation and then long format
ex.
"33996344","33996351","GB","United Kingdom"
remarks in the file will start with a # so you can actually remark out a line
the first line to start with ## will identify the supplier of the list.
so the file many look like this:

## list provide by abc company
"33996344","33996351","GB","United Kingdom"
#"5033164868257567","US","United States"

#can be use to mark a line is not formated properly or that has trash in it.
notice how i messed up the 3rd line on purpose.

[Paul Purvis]

here are a couple of links to other information concerning this

sources of listing of ip addresses for countries
1. provided by ip.pl on http://Software77.net (A Webnet77.com Company)
download here
http://software77.net/cgi-bin/ip-cou...tion=downloadZ

2. provide by MaxMind
http://www.maxmind.com/app/geoip_country
download here
http://www.maxmind.com/download/geoi...CountryCSV.zip

3. some general info
http://people.netfilter.org/
http://people.netfilter.org/~peejix/geoip/howto/geoip-HOWTO-2.html#ss2.1

i cannot confirm the accuracy of these listing.
maybe a cross check later but do not count on it

[Paul Purvis]

program to convert geoip csv database
they have another database i cannot and do not want to read.
the csv file i down loaded had many errors in the format
http://www.maxmind.com/download/geoi...CountryCSV.zip

get geoipcountrycsv.zip
this program creates a ip2cntry.csv file on purpose, not the ip2cntry.dat file which my program will use.
therefor your good file ip2cntry.dat will not be damaged until you verified ip2cntry.csv then after copied it to ip2cntry.dat

'ip2cntryconvertgeoip.bas
'compiled with pbcc 4.04
'program converts geoip csv database to standard ip2cntry.csv

#COMPILE EXE
#DIM ALL
#INCLUDE "win32api.inc"

FUNCTION ip2decimal(BYVAL sip AS STRING) AS QUAD
  LOCAL stemp AS STRING
  LOCAL qip AS QUAD
  LOCAL j AS LONG
  FUNCTION=0&&
  DIM ipbit(4) AS INTEGER
  REPLACE $DQ WITH "" IN sip
  REPLACE " " WITH "" IN sip
  sip=TRIM$(sip)
  IF LEN(sip)<7 THEN EXIT FUNCTION
  IF TALLY(sip,".")<>3 THEN EXIT FUNCTION
  IF TALLY(sip,"..")>0  THEN EXIT FUNCTION
  IF LEFT$(sip,1)="."  THEN EXIT FUNCTION
  IF RIGHT$(sip,1)="."  THEN EXIT FUNCTION
  stemp=sip
  REPLACE ANY "0123456789." WITH "" IN stemp
  stemp=TRIM$(stemp)
  IF LEN(stemp) THEN EXIT FUNCTION
  FOR j = 1& TO 4&:ipBit(j) = VAL(PARSE$(sIP, ".", j)): NEXT
  qIp= (ipBit(1&)*16777216) + (ipBit(2&)*65536) + (ipBit(3&)*256) + (ipBit(4&))
  FUNCTION=qip
END FUNCTION



FUNCTION PBMAIN() AS LONG
LOCAL i AS LONG
LOCAL itemp AS LONG
LOCAL jtemp AS LONG
LOCAL jbrokenfile AS LONG
LOCAL stemp AS STRING
LOCAL sfilename1 AS STRING
LOCAL sfilename2 AS STRING
LOCAL input1,input2,input3,input4,input5,input6 AS STRING
LOCAL qipfrom,qipto AS QUAD
LOCAL sipfrom,sipto AS STRING
sfilename1="GeoIPCountryWhois.csv"
sfilename2="ip2cntry.csv"

'test to see if we can open the file
TRY
OPEN sfilename1 FOR INPUT AS #1
CLOSE #1
CATCH
PRINT "problem opening the file "+sfilename1
SLEEP 10000
EXIT FUNCTION
END TRY


TRY
OPEN sfilename1 FOR BINARY AS #1
PRINT "reading file "+sfilename1
I=LOF(1)
GET$ 1,I,STEMP
CLOSE 1
PRINT "reading done"
PRINT "scrub the data from file "+sfilename1
CATCH
PRINT "problem reading file "+sfilename1
SLEEP 10000
EXIT FUNCTION
END TRY


goback0:
IF INSTR(stemp,"  ") THEN
   REPLACE "  " WITH " " IN STEMP
   GOTO goback0
END IF
goback1:
IF INSTR(stemp,$CRLF) THEN
   REPLACE $CRLF WITH $LF IN STEMP
   GOTO goback1
END IF
goback2:
IF INSTR(stemp,$LF+$LF) THEN
    REPLACE $LF+$LF WITH $LF IN STEMP
    GOTO goback2
END IF
goback3:
IF INSTR(stemp,$DQ+" ") THEN
   REPLACE $DQ+" " WITH $DQ IN STEMP
   GOTO goback3
END IF
goback4:
IF INSTR(stemp," "+$DQ) THEN
   REPLACE " "+$DQ WITH $DQ IN STEMP
   GOTO goback4
END IF
goback5:
IF INSTR(stemp,", ") THEN
   REPLACE ", " WITH " " IN STEMP
   GOTO goback5
END IF
goback6:
IF INSTR(stemp," ,") THEN
   REPLACE " ," WITH " " IN STEMP
   GOTO goback6
END IF
goback7:
IF INSTR(stemp,"  ") THEN
   REPLACE "  " WITH " " IN STEMP
   GOTO goback7
END IF
goback8:
IF INSTR(stemp,$LF+$DQ+$DQ+$LF) THEN
   REPLACE $LF+$DQ+$DQ+$LF WITH $LF IN STEMP
   GOTO goback8
END IF
goback9:
IF INSTR(stemp,$LF+$DQ+$LF) THEN
   REPLACE $LF+$DQ+$LF WITH $LF IN STEMP
   GOTO goback9
END IF
goback10:
IF INSTR(stemp,$LF+$LF) THEN
   REPLACE $LF+$LF WITH $LF IN STEMP
   GOTO goback10
END IF
REPLACE $LF WITH $CRLF IN STEMP

TRY
OPEN sfilename1 FOR BINARY AS #1
PRINT "saving scrubbed data back to file "+sfilename1
PUT$ 1,STEMP
CLOSE 1
CATCH
STDOUT "problem opening file "+sfilename1
SLEEP 10000
EXIT FUNCTION
END TRY


TRY
OPEN sfilename1 FOR INPUT AS #1 LEN=16384
PRINT "converting data from file "+sfilename1+" to "+sfilename2
CATCH
STDOUT "problem opening file "+sfilename1
SLEEP 10000
EXIT FUNCTION
END TRY

i=0&
WHILE ISFALSE EOF(1)
LINE INPUT #1,stemp
INCR i
jtemp=0&

stemp=TRIM$(stemp)
itemp=TALLY(stemp,$DQ)
IF ITEMP<>12 THEN
     STDOUT  "problem in line"+STR$(i)+":12 equote marks needed but line has "+STR$(itemp)
     jtemp=1&
     END IF
itemp=TALLY(stemp,",")
IF ITEMP<>5 THEN
     STDOUT  "problem in line"+STR$(i)+":5 comma marks needed but line has "+STR$(itemp)
     STDOUT  "problem with line "+STR$(i)
     jtemp=1&
     END IF
IF jtemp THEN
    PRINT stemp
    jbrokenfile=1&
END IF

WEND
CLOSE 1
IF jbrokenfile THEN
    STDOUT "problems with "+sfilename1
    STDOUT "fix problems then rerun this program"
    SLEEP 10000
   EXIT FUNCTION

END IF



TRY
OPEN sfilename1 FOR INPUT AS #1 LEN=16384
CATCH
STDOUT "problem opening file "+sfilename1
EXIT FUNCTION
END TRY
KILL sfilename2
TRY
OPEN sfilename2 FOR OUTPUT  AS #2 LEN=16384
CLOSE 2
CATCH
STDOUT "problem opening file "+sfilename2
CLOSE
GOTO killtheoutputfile
END TRY
TRY
OPEN sfilename2 FOR OUTPUT  AS #2 LEN=16384
CATCH
STDOUT "problem opening file "+sfilename2
CLOSE
GOTO killtheoutputfile
END TRY

WRITE #2,"##This product includes data created by MaxMind, available from http://www.maxmind.com/"

WHILE ISFALSE EOF(1)
INPUT #1,input1,input2,input3,input4,input5,input6
'this code is not needed here designed for possible another converter
'  qipfrom=ip2decimal(input1)
'  qipto  =ip2decimal(input2)
'  if qipfrom=0&& or qipto=0&& then iterate
'  sipfrom=trim$(str$(qipfrom))
'  sipto   =TRIM$(STR$(qipto))
'  write #2,sipfrom,sipto,input3,input4,input5,input6
 WRITE #2,input3,input4,input5,input6

WEND

CLOSE 1
CLOSE 2
PRINT "program finished"

GOTO FINISH
killtheoutputfile:
STDOUT "program deleted the output file "+sfilename2
KILL sfilename2
SLEEP 10000
GOTO finish

displayhelp:

finish:
END FUNCTION

added:
made some fine adjustments to keep the program from creating files when the program does not run completely.

[Paul Franks]

I'm pretty sure Yahoo or Google or Amazon have a web service to do this down to the city level. Would be a whole lot simpler to use a web service vs. maintaining a csv "database".

[Paul Purvis]

i keep logs of all my router history and want to know what is going on.
today after creating some reports, i feel like going to the barn with a lot of people not in the USA.
the biggest problem comes from where people are in countries where you cannot prosecute people.
In Lousiana, USA, and probably most other states, it is a crime to just to try to access intellectual information, you do not even have to succeed.
also i will be downloading emails with a pop account with a build in program and if the ip address is there, i can handle it.
after yahoo advertised my competitors on my website with them, i will try to live without yahoo and i do not need google knowing anything about web addresses that comes to my site either.
also the country ip addesses data will most likely be shared
this kind of information should be available freely from a usa gov site and i know who to talk to.

thanks for the heads up

[Paul Purvis]

program to convert iptocountry.csv database to ip2cntry.csv
i did not see any errors the csv file like i saw in the geoip csv file.

get iptocountry.zip
this program creates a ip2cntry.csv file on purpose, not the ip2cntry.dat file which my program will use.
therefor your good file ip2cntry.dat will not be damaged until you verified ip2cntry.csv then after copied it to ip2cntry.dat

file with listing of ip address for countries provided by
http://Software77.net (A Webnet77.com Company)
http://software77.net/cgi-bin/ip-cou...tion=downloadZ

'ip2cntryconvertsoftware77net.bas
'compiled with pbcc 4.04
'program converts a software77net ip address country database in csv format to standard ip2cntry.csv

#COMPILE EXE
#DIM ALL
'#INCLUDE "win32api.inc"

'this function is not needed
'FUNCTION ip2decimal(BYVAL sip AS STRING) AS QUAD
'  LOCAL stemp AS STRING
'  LOCAL qip AS QUAD
'  LOCAL j AS LONG
'  FUNCTION=0&&
'  DIM ipbit(4) AS INTEGER
'  REPLACE $DQ WITH "" IN sip
'  REPLACE " " WITH "" IN sip
'  sip=TRIM$(sip)
'  IF LEN(sip)<7 THEN EXIT FUNCTION
'  IF TALLY(sip,".")<>3 THEN EXIT FUNCTION
'  IF TALLY(sip,"..")>0  THEN EXIT FUNCTION
'  IF LEFT$(sip,1)="."  THEN EXIT FUNCTION
'  IF RIGHT$(sip,1)="."  THEN EXIT FUNCTION
'  stemp=sip
'  REPLACE ANY "0123456789." WITH "" IN stemp
'  stemp=TRIM$(stemp)
'  IF LEN(stemp) THEN EXIT FUNCTION
'  FOR j = 1& TO 4&:ipBit(j) = VAL(PARSE$(sIP, ".", j)): NEXT
'  qIp= (ipBit(1&)*16777216) + (ipBit(2&)*65536) + (ipBit(3&)*256) + (ipBit(4&))
'  FUNCTION=qip
'END FUNCTION



FUNCTION PBMAIN() AS LONG
LOCAL i AS LONG
LOCAL itemp AS LONG
LOCAL jtemp AS LONG
LOCAL jbrokenfile AS LONG
LOCAL stemp AS STRING
LOCAL sfilename1 AS STRING
LOCAL sfilename2 AS STRING
LOCAL qipfrom,qipto AS QUAD
LOCAL sipfrom,sipto AS STRING
DIM b(6) AS STRING

sfilename1="IpToCountry.csv"
sfilename2="ip2cntry.csv"

'test to see if we can open the file
TRY
OPEN sfilename1 FOR INPUT AS #1
CLOSE #1
CATCH
PRINT "problem opening the file "+sfilename1
SLEEP 10000
EXIT FUNCTION
END TRY


TRY
OPEN sfilename1 FOR BINARY AS #1
PRINT "reading file "+sfilename1
I=LOF(1)
GET$ 1,I,STEMP
CLOSE 1
PRINT "reading done"
PRINT "scrubing the data from file "+sfilename1
CATCH
PRINT "problem reading file "+sfilename1
SLEEP 10000
EXIT FUNCTION
END TRY


goback0:
IF INSTR(stemp,"  ") THEN
   REPLACE "  " WITH " " IN STEMP
   GOTO goback0
END IF
goback1:
IF INSTR(stemp,$CRLF) THEN
   REPLACE $CRLF WITH $LF IN STEMP
   GOTO goback1
END IF
goback2:
IF INSTR(stemp,$LF+$LF) THEN
    REPLACE $LF+$LF WITH $LF IN STEMP
    GOTO goback2
END IF
goback3:
IF INSTR(stemp,$DQ+" ") THEN
   REPLACE $DQ+" " WITH $DQ IN STEMP
   GOTO goback3
END IF
goback4:
IF INSTR(stemp," "+$DQ) THEN
   REPLACE " "+$DQ WITH $DQ IN STEMP
   GOTO goback4
END IF
goback5:
IF INSTR(stemp,", ") THEN
   REPLACE ", " WITH " " IN STEMP
   GOTO goback5
END IF
goback6:
IF INSTR(stemp," ,") THEN
   REPLACE " ," WITH " " IN STEMP
   GOTO goback6
END IF
goback7:
IF INSTR(stemp,"  ") THEN
   REPLACE "  " WITH " " IN STEMP
   GOTO goback7
END IF
goback8:
IF INSTR(stemp,$LF+$DQ+$DQ+$LF) THEN
   REPLACE $LF+$DQ+$DQ+$LF WITH $LF IN STEMP
   GOTO goback8
END IF
goback9:
IF INSTR(stemp,$LF+$DQ+$LF) THEN
   REPLACE $LF+$DQ+$LF WITH $LF IN STEMP
   GOTO goback9
END IF
goback10:
IF INSTR(stemp,$LF+$LF) THEN
   REPLACE $LF+$LF WITH $LF IN STEMP
   GOTO goback10
END IF
REPLACE $LF WITH $CRLF IN STEMP

TRY
OPEN sfilename1 FOR BINARY AS #1
PRINT "saving scrubbed data back to file "+sfilename1
PUT$ 1,STEMP
CLOSE 1
stemp=""
CATCH
STDOUT "problem opening file "+sfilename1
SLEEP 10000
EXIT FUNCTION
END TRY



i=0&
WHILE ISFALSE EOF(1)
LINE INPUT #1,stemp
INCR i
jtemp=0&
stemp=TRIM$(stemp)
IF LEN(stemp)=0 THEN ITERATE
IF LEFT$(stemp,1)="#" THEN ITERATE

itemp=TALLY(stemp,$DQ)
IF ITEMP<>14 THEN
     STDOUT  "problem in line"+STR$(i)+":14 equote marks needed but line has "+STR$(itemp)
     jtemp=1&
     END IF
itemp=TALLY(stemp,",")
IF ITEMP<>6 THEN
     STDOUT  "problem in line"+STR$(i)+":6 comma marks needed but line has "+STR$(itemp)
     STDOUT  "problem with line "+STR$(i)
     jtemp=1&
     END IF
IF jtemp THEN
    PRINT stemp
    jbrokenfile=1&
END IF

WEND
CLOSE 1
IF jbrokenfile THEN
    STDOUT "problems with "+sfilename1
    STDOUT "fix problems then rerun this program"
    SLEEP 10000
   EXIT FUNCTION
END IF

TRY
OPEN sfilename1 FOR INPUT AS #1 LEN=16384
CATCH
STDOUT "problem opening file "+sfilename1
EXIT FUNCTION
END TRY
KILL sfilename2
TRY
OPEN sfilename2 FOR OUTPUT  AS #2 LEN=16384
CLOSE 2
CATCH
STDOUT "problem opening file "+sfilename2
CLOSE
GOTO killtheoutputfile
END TRY
TRY
OPEN sfilename2 FOR OUTPUT  AS #2 LEN=16384
CATCH
STDOUT "problem opening file "+sfilename2
CLOSE
GOTO killtheoutputfile
END TRY

PRINT #2,"##this product includes data created by http://Software77.net (A Webnet77.com Company)
WHILE ISFALSE EOF(1)
   LINE INPUT #1,stemp
   stemp=TRIM$(stemp)
   IF LEN(stemp)=0              THEN ITERATE
   IF LEFT$(stemp,1)<>CHR$(34)  THEN ITERATE
   IF TALLY(stemp,",")<>6&      THEN ITERATE
   PARSE stemp, b()
   FOR i=0 TO 5
     REPLACE $DQ WITH "" IN B(i)
     b(i)=TRIM$(b(i))
   NEXT i
   WRITE #2,b(0),b(1),b(4),b(6)
WEND



CLOSE 1
CLOSE 2
PRINT "program finished"

GOTO FINISH
killtheoutputfile:
STDOUT "program deleted the output file "+sfilename2
KILL sfilename2
SLEEP 10000
GOTO finish

displayhelp:

finish:
END FUNCTION

[Paul Purvis]

a newer version of the ip2cntry program in the source code section has been updated.
the program now reads the standard ip country database i mentioned above.
also the program has been speed up tremendously
i sorted the country database in memory and installed a loosely written binary search routine to locate the records in the database stored in an array.

i had a little trouble in searching the ip country database array because of the ranges, so i just had the search routine get close to where the range might be then i read through the array sequentially for a match until either a match was made in the range or the ip address searched for was higher than the last range read. it is not very clean but it is very fast.

[Paul Purvis]

i posted a version of the ip2cntry program in DLL executable format in the source code section.
the program adds much more functionality, you can also specific different databases as long as they are in the standard format the program is using.
this newer version should be much more useful in many different ways.

a though occurred to me in another way of using this program.
if you have any program where you want to limit the access to that program, you can screen for specific ip address or ranges of address easily by having a custom list of ip addresses.
also if you wanted a program to direct a ip address or certain of ip addresses to a particular website or some other kind of direction, you can use the text that would normally be used for the 2 character country and the longer country name for other purposes of your creation.
but i would length the descriptions of at least the full name of the country. it should be very easy to maintain the database or simply add more fields to the database.

[Paul Purvis]

i did a simple cross check of ip listing from maxmind and webnet77.
the results are in 4 files
compare maxmind to webnet77 listing
maxminda.lst
maxmindb.lst
compare webnet77 to maxmind listing
webnet77a.lst
webnet77b.lst

files at
pdptemp.dyndns.org/zipcode

i have not taken much time to analyze the reports
but just a quick look at it and a lot of the differences are countries near one another.
there is two list for each run
one of the listing is where there was a match
the other one is where there was no match on a ip address

in both listing i just picked up the first and last numbers in the range in the list and compared to see if the ip address was in the the other file and with what country
paul
without much review, i would think maxmind(geoip) should be more accruate.
although country names maybe different, i just compared the two character abbreviation of country names.
paul