Skip to content

Instantly share code, notes, and snippets.

@Fusl
Last active April 25, 2019 19:42
ip2asn2sqlite3.js
#!/usr/bin/env node
// No kittens were harmed while writing this really ugly looking and badly written code, I promise.
'use strict';
/*
(
wget -O- https://iptoasn.com/data/ip2asn-v4.tsv.gz | gzip -d
wget -O- https://iptoasn.com/data/ip2asn-v6.tsv.gz | gzip -d
) | node ip2asn2sqlite3.js
ls -alFh ip2asn.db
*/
const readline = require('readline');
const fs = require('fs');
const sqlite3 = require('sqlite3').verbose();
const ipaddr = require('ipaddr.js');
const rl = readline.createInterface({
input: process.stdin,
crlfDelay: Infinity
});
const ip2long_multipliers = [0x1000000, 0x10000, 0x100, 1];
const ip2long = ip => {
// I swear, I copied this somewhere from the internet and didn't write it myself!
let longValue = 0;
ip.split('.').forEach((part, i) => {longValue += part * ip2long_multipliers[i];});
return longValue;
};
fs.unlinkSync('./ip2asn.db');
const db = new sqlite3.Database('./ip2asn.db', err => {
if (err) {
throw err;
}
db.run('create table ip2asn4 (start int unsigned not null, end int unsigned not null, asn int unsigned not null, cc char(2) not null, descr varchar(255) not null, primary key (start ASC, end ASC, asn ASC));');
db.run('create table ip2asn6 (start char(39) not null, end char(39) not null, asn int unsigned not null, cc char(2) not null, descr varchar(255) not null, primary key (start ASC, end ASC, asn ASC));');
db.run('BEGIN TRANSACTION');
rl.on('line', line => {
line = line.split('\t');
if (line[2] === '0' || isNaN(line[2]) || line.length !== 5) return;
let start = line[0];
let end = line[1];
const asn = Number(line[2]);
const cc = line[3].length === 2 ? line[3] : '--';
const descr = line[4];
let table = '';
if (start.indexOf(':') !== -1) {
// IPv6
table = 'ip2asn6';
// Dear god, please forgive me.
start = ipaddr.parse(start).toNormalizedString().split(':').map(part => ('000'+part).substr(-4)).join(':');
end = ipaddr.parse(end).toNormalizedString().split(':').map(part => ('000'+part).substr(-4)).join(':');
} else {
// IPv4
table = 'ip2asn4';
start = ip2long(start);
end = ip2long(end);
}
db.run('INSERT INTO ' + table + ' (start, end, asn, cc, descr) VALUES (?, ?, ?, ?, ?)', [start, end, asn, cc, descr]);
});
rl.on('close', () => {
console.log('COMMIT TRANSACTION');
db.run('COMMIT TRANSACTION', () => {
console.log('db.close();');
db.close();
});
});
});
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment