MySQL joins on IP addresses

If you are doing this:

  1. using MySQL
  2. storing IPv4 addresses as varchar
  3. attempting to join two tables on IPv4 varchar columns

You need to stop and go learn about INET_ATON. It converts those IP addresses to BIGINTS and speeds up your queries like you wouldn’t believe. I had an existing project that did not require any joins against my IPs, so I never bothered to do the conversion. I’m currently neck-deep in some new code that requires me to join against those same IPs, and INET_ATON took my queries from 120 seconds to under half a second to complete.

Those are results you can believe in.

Posted in News | Leave a comment

WordPress Widget: Bill’s ArtistData Widget

I have podged together my first WordPress Widget: Bill’s ArtistData Widget

Not suprisingly, it displays your band’s Artistdata.com upcoming shows RSS feed in your sidebar.


I will update this post with the WordPress.org link as soon as it’s approved.

Download “Bill’s ArtistData Widget” here –> artistdata

Posted in News | 1 Comment

You have requested an outdated version of PayPal. This error often results from the use of bookmarks.

Do you have a cart that uses PayPal? Been working for years without any trouble? Then, suddenly, all your users see this:

“You have requested an outdated version of PayPal. This error often results from the use of bookmarks.”

Have no fear, it’s an easy fix. Essentially, you remove the enctype=”multipart/form-data” from your form tag. Done.

Posted in News | 2 Comments

Installing OpenSUSE 11.2 With A 2Wire USB Adapter

Here’s your “pro-tip” for the day:

IF you’re installing OpenSUSE 11.2 AND you’re worried about driver support for your USB wireless adapter (specifically, for example, the 2Wire US-G-AT-02), just plug the thing in when you boot from the install disc. Amazingly enough, it installs all drivers and wrappers correctly and is ready to use on first boot.

Amazing. People make a face when I say I use OpenSUSE, but it very rarely disappoints.

Posted in News | 1 Comment

IP Matching With CIDR Notation In PHP

Sometimes you can’t find an answer, no matter how hard you Google. I consider myself lucky to have found this answer!

The Problem: You have a list of IP ranges in CIDR notation. You need to take visiting IPs and evaluate if they are within this list of allowed addresses. And, you need to do it in PHP.

The Answer: Load your CIDR list into an array and evaluate each visiting IP with the following function (found here)…

function ipfilter($ip) {
    $source = array("10.0.0.0/8",
        "192.168.1.1/32",
        "127.0.0.0/8");
    foreach ($source as $line) {
    

        // Get the base and the bits from the CIDR
        list($base, $bits) = explode('/', $line);
       
        // Now split it up into it's classes
        list($a, $b, $c, $d) = explode('.', $base);
       
        // Now do some bit shifting/switching to convert to ints
        $i    = ($a << 24) + ($b << 16) + ( $c << 8 ) + $d;
        $mask = $bits == 0 ? 0: (~0 << (32 - $bits));
       
        // Here's our lowest int
        $low = $i & $mask;
       
        // Here's our highest int
        $high = $i | (~$mask & 0xFFFFFFFF);
       
        // Now split the ip we're checking against up into classes
        list($a, $b, $c, $d) = explode('.', $ip);
       
        // Now convert the ip we're checking against to an int
        $check = ($a << 24) + ($b << 16) + ( $c << 8 ) + $d;
       
        // If the ip is within the range, including highest/lowest values,
        // then it's witin the CIDR range
        if ($check >= $low && $check <= $high) {
            return 1;
        }
    }
    return 0;

}

Is it efficient? I don’t know. Does it work? Yes it does. And thats more than I can say about other solutions to this problem that I found (here and elsewhere).

Posted in News | 2 Comments