Databases
StackOverflow2013
Postsdbo
POGet country location by IP (IPv4,IPv6)
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POGet country location by IP (IPv4,IPv6)
    text
    copied!<p>I've been testing some code that will allow me to block ads and videos to users from specific countries. This is nothing new and certainly should not be hard to do, however I can't sort it out to work with IPv6 addresses because I don't know how to handle those. I use this database <a href="http://db-ip.com/db/" rel="nofollow noreferrer">http://db-ip.com/db/</a> which initially looks like this:</p> <p><img src="https://i.stack.imgur.com/DGIYM.png" alt="enter image description here"></p> <p>After converting the two IP column values to binary and moving everything to a new table I have this:</p> <p><img src="https://i.stack.imgur.com/q8Fkt.png" alt="enter image description here"></p> <p>Here is the test code (when I sort it out, I will use a cfc and stored procedure instead):</p> <pre><code>&lt;!--- manualy change IPs to check if the code works ---&gt; &lt;cfset ipaddress="1.0.127.255"&gt; &lt;cfset ipToArray = listToArray(ipaddress,".")&gt; &lt;cfset ipBinary= (ipToArray[1] * (256)^3) + (ipToArray[2] * (256)^2) + (ipToArray[3] * 256) + ipToArray[4]&gt; &lt;cfquery name="getCountry" datasource="mydatabase"&gt; SELECT country FROM dbip_lookup WHERE #ipBinary# &gt;= bin1 AND #ipBinary# &lt;= bin2 &lt;/cfquery&gt; &lt;cfoutput&gt;#getCountry.country#&lt;/cfoutput&gt; </code></pre> <p>As you can see, nothing too fancy just a simple testing code that works with IPv4 :) Should I somehow convert IPv6 to binary to make it work? If so, how to do it or is there some other way to achieve the same functionality? I really have no idea where to start :(</p> <p><strong>EDIT - STEP BY STEP PROCESS</strong></p> <p>create DB table with code from website (addr_type does not exist in csv file so I removed the column to be able to import csv properly)</p> <pre><code>CREATE TABLE `dbip_lookup` ( `ip_start` varbinary(16) NOT NULL, `ip_end` varbinary(16) NOT NULL, `country` char(2) NOT NULL, PRIMARY KEY (`ip_start`) ); </code></pre> <p>after the table has been successfully created an attempt to populate the data</p> <pre><code>LOAD DATA INFILE 'C:/dbip-country.csv' INTO TABLE dbip_lookup2 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' </code></pre> <p>fails throwing the following error message "10:07:52 LOAD DATA INFILE 'C:/dbip-country.csv' INTO TABLE dbip_lookup2 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' Error Code: 1406. Data too long for column 'ip_end' at row 158131 19.641 sec " 158131 line is where IPv6 addresses start.</p> <p>after modifying both varbinary columns to (50) mysql successfully created and populated the table with the above code.</p> <p><img src="https://i.stack.imgur.com/QaBG0.png" alt="enter image description here"></p> <p>your a bit simplified cfm code (it does not work either way) </p> <pre><code>&lt;cfset InetAddress = createObject("java", "java.net.InetAddress")&gt; &lt;cfset addrStart = InetAddress.getByName("2001:0db8:85a3:08d3:1319:8a2e:0370:7344").getAddress()&gt; &lt;!--- for MySQL. Use TOP 1 for SQL Server ---&gt; &lt;cfquery name="qResult"&gt; select country from dbip_lookup2 where ip_start &lt;= "#addrStart#" &lt;/cfquery&gt; &lt;cfoutput&gt;#qResult.country#&lt;/cfoutput&gt; </code></pre> <p>triggers custom error handler:</p> <p><img src="https://i.stack.imgur.com/QcabP.png" alt="enter image description here"></p> <p>line 43 is where WHERE clause starts</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload