Matching IPv6 addresses against CIDR masks in MySQL

CIDR matching is a common task operating large sets of IP addresses. As more networks are getting IPv6 support, we start receiving IPv6 datasets for analysis and need to match those against CIDR masks as one of steps.

Doing so in MySQL is impossible in the most common way - by applying bitmasks. Binary representation of IPv6 addresses are 128-bit numbers, while longest integer type in MySQL is BIGINT, 8 bytes (64 bits) long. That’s why result of INET6_ATON() is VARBINARY, and you can’t have MySQL apply bitwise operators on it.

Another approach is to calculate first and last IPv6 addresses matching given CIDR mask, and match those entries falling between the two. This is discussed on stackoverflow, but the proposed implementation is buggy. Here’s the proper solution we coded for our project:

DELIMITER $$

CREATE FUNCTION `FirstIPv6MatchingCIDR` (`ip` VARCHAR(46), `mask` INT(2) UNSIGNED) RETURNS VARCHAR(39) DETERMINISTIC
    BEGIN
        RETURN INET6_NTOA(UNHEX(RPAD(
            CONCAT(
                SUBSTR(HEX(INET6_ATON(`ip`)), 1, FLOOR(`mask` / 4)),
                HEX(
                    ((POW(2, `mask` % 4) - 1) * POW(2, 4 - (`mask` % 4)))
                    & COALESCE(CONV(SUBSTR(HEX(INET6_ATON(`ip`)), FLOOR(`mask` / 4) + 1, 1), 16, 10), '')
                )
            ),
            32, 0
        )));
    END$$

CREATE FUNCTION `LastIPv6MatchingCIDR` (`ip` VARCHAR(46), `mask` INT(2) UNSIGNED) RETURNS VARCHAR(39) DETERMINISTIC
    BEGIN
        DECLARE `ipNumber` VARBINARY(16);
        DECLARE `last` VARCHAR(39) DEFAULT '';
        DECLARE `flexBits`, `counter`, `deci`, `newByte` INT UNSIGNED;
        DECLARE `hexIP` VARCHAR(32);

        SET `ipNumber` = INET6_ATON(`ip`);
        SET `hexIP`    = HEX(`ipNumber`);
        SET `flexBits` = 128 - `mask`;
        SET `counter`  = 32;

        WHILE (`flexBits` > 0) DO
            SET `deci`    = CONV(SUBSTR(`hexIP`, `counter`, 1), 16, 10);
            SET `newByte` = `deci` | (POW(2, LEAST(4, `flexBits`)) - 1);
            SET `last`    = CONCAT(CONV(`newByte`, 10, 16), `last`);

            IF `flexBits` >= 4 THEN
                SET `flexBits` = `flexBits` - 4;
            ELSE
                SET `flexBits` = 0;
            END IF;

            SET `counter`  = `counter` - 1;
        END WHILE;

        SET `last` = CONCAT(SUBSTR(`hexIP`, 1, `counter`), `last`);

        RETURN INET6_NTOA(UNHEX(`last`));
    END $$

DELIMITER ;

Example SELECT:

SELECT @firstIP := INET6_ATON(FirstIPv6MatchingCIDR('::ffff:0:0', 96));
SELECT @lastIP := INET6_ATON(LastIPv6MatchingCIDR('::ffff:0:0', 96));

SELECT `ip` FROM `ip_db` WHERE INET6_ATON(`ip`) BETWEEN @firstIP AND @lastIP;

Converting files with characters in multiple encodings to utf-8

It’s easy to convert a windows-1252 file into a utf-8 one, right? Use iconv, enca or any other tool of your choice and you’re done. Even if you have thousands of such files, you can easily automate things using Bash, OS X Automator or anything else.

But what should you do if you have characters both encoded in utf-8 and something else? Go find author and kick his ass, obviously. We adopted a snippet found on StackOverflow and got the solution:

#!/usr/bin/python
# -*- coding: utf-8 -*-

import codecs
import sys
from optparse import OptionParser

last_position = -1
source_encoding = "utf-8"


def mixed_decoder(unicode_error):
    global last_position
    global source_encoding
    string = unicode_error[1]
    position = unicode_error.start
    if position <= last_position:
        position = last_position + 1
    last_position = position
    new_char = string[position].decode(source_encoding)
    #new_char = u"_"
    return new_char, position + 1


codecs.register_error("mixed", mixed_decoder)

parser = OptionParser()
parser.add_option("-s", "--source-encoding", dest="source_encoding", default="utf-8")
(options, args) = parser.parse_args()

source_encoding = options.source_encoding
target_file = args[0]

if not args:
    print 'Script should be called with valid file name as an argument. The file will be converted to utf-8.'
    sys.exit(1)

try:
    f = open(target_file, 'r')
except IOError:
    print target_file + " is not a valid file"
    sys.exit(1)

s = f.read()
f.close()

s = s.decode("utf-8", "mixed");

f = open(target_file, 'w')
f.write(s.encode("utf-8"))

No one of us is good enough in Python, we feel that code could be better, but it works like a charm.

Enabling Single Sign On for daemons with PAM and Jasig CAS

Jasig CAS is a great service for single sign on. Basically it is a Java application that, once deployed, provides a single authentication mechanism for all your web services. Thus, if you’re developing a multicomponent portal, which consists of several sites — CAS is a great solution.

With CAS, users can authenticate only once and get access to all sites. That’s absolutely the same thing that Google does — you sign into your GMail account, and get the access to YouTube, Blogspot, Calendar, Play Market and other awesome services hosted on different domains.

That’s completely different from what OAuth and OpenID do. Last two provide user’s credentials upon authorization request. Your app still has to manage user accounts, it just gets another way of verifying credentials. With CAS, it all will be done behind the scenes. User just comes to the site, gets transparently redirected several times, and he’s already in.

The only problem is — CAS provides only web based auth. But what if you want to authenticate a daemon? That’s also possible — let’s take Dovecot as an example. We will use PAM authentication mechanism. Several modules for doing that are suggested on official site, but the only problem with those is — they don’t work.

That’s why our developer had to pick the most recent module, fork it and update for recent CAS versions. This version works fine on 64 bit Ubuntu 12.04 LTS with CAS 3.5.1.

And here’s the configuration for PAM (/etc/pam.d/dovecot in our case):

#%PAM-1.0

auth       sufficient   pam_unix.so nullok_secure
auth       sufficient   pam_cas.so use_first_pass
auth       required     pam_deny.so

account    sufficient   pam_cas.so
account    required     pam_deny.so

And pam_cas config (/etc/pam_cas.conf):

[General]

; This is the target service URL
; Could be a website to redirect to, or a service such as
; ssh/imap/etc
; SERVICE_URL = ssh
SERVICE_URL = imap://my.dovecot.installation/

; This is the callback for a proxy ticket
SERVICE_CALLBACK_URL = http://my.dovecot.installation/?_action=pgtcallback

; CAS BASE URL
; (No need for trailing slash)
CAS_BASE_URL = https://my.cas.installation/cas

; Enable serviceTicket validation
;
; This option is there to allow disabling/enabling of user+serviceTicket logins
ENABLE_ST = 0


; Enable proxyTicket validation
;
; Same as above, except for proxy tickets
ENABLE_PT = 1

; Enable user+pass validation
;
; Enable user+pass login against CAS
ENABLE_UP = 0

After enabling the module, once user has logged into any of your web sites, he gets an access to his mailbox in your system without additional prompt. Hope you find it useful!

Shaking blocks and scrolling of resizing content

Just solved a tricky thing: imagine you have a page of two blocks. First block can grow its height on click, but you wish the second block’s top line to stay at its place. Hence, all the page should scroll down to keep the block on its place.

Sounds tricky? Just check the block below and click “expand”. Use “reset” to reset the example.

You see this ugly shaking blue box when it’s scrolling? That’s the issue.

To make it smooth, let’s hack it. Remember, we want the position of the blue box to be fixed during the whole scrolling action? So let’s make it fixed!

FixableBlock = function (o) {
    var t = $(o),
        params = {};

    t.extend({
        fix: function () {
            // store parameters
            params = {
                top: t.offset().top,
                left: t.offset().left,
                position: t.css('position')
            };
            // apply fixed position
            t.css({
                top: t.offset().top - $(window).scrollTop(),
                left: t.offset().left,
                position: 'fixed'
            });
        },
        release: function () {
            t.css(params);
            params = {};
        }
    });

    return t;
};

Let’s apply FixableBlock class on the blue box and see what happens.

Hmmm… Oh yes, it’s fixed. We should explicitely preserve dimensions of the blue box, or it collapses.

FixableBlock = function (o) {
    var t = $(o),
        params = {};

    t.extend({
        fix: function () {
            // store parameters
            params = {
                top: t.offset().top,
                left: t.offset().left,
                position: t.css('position')
            };
            // apply fixed position and preserve the dimensions
            t.css({
                top: t.offset().top - $(window).scrollTop(),
                left: t.offset().left,
                width: t.css('width'),
                height: t.css('height'),
                position: 'fixed'
            });
        },
        release: function () {
            t.css(params);
            params = {};
        }
    });

    return t;
};

Ops.

When we make the blue block’s position fixed, it does not affect the whole document’s height and width anymore. Moreover, other blocks (like yellow one) can use its place now.

Let’s fix that by creating a transparent block with exactly the same dimensions as blue one has, and have it hold the place for our main hero. That should solve the problem.

So here’s our final version:

FixableBlock = function (o) {
    var t = $(o),
        params = {},
        placeholder;

    t.extend({
        fix: function () {
            // store parameters
            params = {
                top: t.offset().top,
                left: t.offset().left,
                position: t.css('position')
            };
            // create placeholder
            placeholder = $('<div>');
            placeholder.css(t.css(['width', 'height']));
            // apply fixed position and preserve the dimensions
            t.css({
                top: t.offset().top - $(window).scrollTop(),
                left: t.offset().left,
                width: t.css('width'),
                height: t.css('height'),
                position: 'fixed'
            });
            placeholder.insertBefore(t);
        },
        release: function () {
            placeholder.remove();
            delete placeholder;

            t.css(params);
            params = {};
        }
    });

    return t;
};

That’s it. Of course, the solution can (and should) be tweaked: for example, this simple thing will be buggy if initially window is higher than two blocks. Just tune it for your page.

Multi-domain nginx config

Okay, so we use nginx all the way here.

Also we often get some code to “take a look” at and “play around” with. Normally it goes like that:

  1. Deploy the code.
  2. lnspect its structure and find out if it’s similar to one of our beloved framework.
  3. If so, use the known config for this framework.
  4. If not, try to find out which framework the site is based on (docs? never got a documented code for maintenance yet, huh) and google for a config for this framework.
  5. If no framework is used at all (hello, spaghetti code!) use some custom config.

At some point we were tired by configuring the web server for each new site and wanted a single config, which could probably serve most PHP-based sites and frameworks.

Why not apache?

Well, mod_vhost_alias + mod_rewrite could solve most of the things described below in default configuration.

However, we use nginx in all the development projects — it’s simple, fast, secure and scalable. Yet we believe that development environment should be always the same as production environment. This policy helps us to avoid tons of related bugs.

That’s why we use nginx since the initial deployment.

So, nginx

The config of our dream will be:

  • multidomain (set up once, use for any site);
  • able to serve static content;
  • able to serve most PHP applications (including those with single front controller as well as with multiple per-page front controllers);
  • able to trim /app.php/ (Symfony) and /index.php/ (most of other frameworks);
  • as a bonus, will be able to ignore www. prefix (easy done).

Voilà:

server {
    listen  80;

    server_name ~^(www\.)?(?<domain>.+).base.domain$;

    root    www/$domain/;

    rewrite ^/app\.php/?(.*)$ /$1   redirect;
    rewrite ^/index\.php/?(.*)$ /$1 redirect;

    location / {
        try_files   $uri $uri/index.html @site;
    }

    location @site {
        if (-f $document_root/index.php) {
            rewrite ^(.*)$ /index.php/$1 last;
        }
        if (-f $document_root/app.php) {
            rewrite ^(.*)$ /app.php/$1 last;
        }
        return  404;
    }

    location ~ \.php {
        include fastcgi_params;

        fastcgi_split_path_info ^(.+\.php)(.*)$;
        fastcgi_pass            127.0.0.1:9000;
        fastcgi_param           SCRIPT_FILENAME $document_root/$fastcgi_script_name;
    }
}

Usage:

  • deploy the config;
  • replace base.domain by any base domain that you use;
  • update root;
  • symlink your sites webroots into the root.

QA note

This config is not meant be anyhow secure by design. This config is not meant to be fast and scalable.

Please use this config only for development and don’t use it on production machines as is.