Extracting IPv4 addresses from IPv6 in MySQL

Here’s a stored procedure that will let you find an IPv4 address embedded inside an IPv6 address, as a stored procedure in MySQL:


DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `IPv4_from_IPv6`(IPv6 varchar(255)) RETURNS varchar(255) CHARSET utf8
BEGIN
# Split into blocks
DECLARE Block1, Block2, Block3, Block4, Block5, Block6, Block7, Block8 varchar(4);
DECLARE IPv4 varchar(255);
SELECT SUBSTRING_INDEX(IPv6, ":", 1)                           INTO Block1;
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(IPv6, ":", 2), ":", -1) INTO Block2;
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(IPv6, ":", 3), ":", -1) INTO Block3;
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(IPv6, ":", 4), ":", -1) INTO Block4;
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(IPv6, ":", 5), ":", -1) INTO Block5;
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(IPv6, ":", 6), ":", -1) INTO Block6;
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(IPv6, ":", 7), ":", -1) INTO Block7;
SELECT SUBSTRING_INDEX(IPv6, ":", -1) INTO Block8;
SELECT INET_NTOA(CONV(concat(Block7, Block8), 16, 10)) INTO IPv4;
IF (Block1 <> 0)
THEN
RETURN NULL;
ELSEIF (Block2 <> 0)
THEN
RETURN NULL;
ELSEIF (Block3 <> 0)
THEN
RETURN NULL;
ELSEIF (Block4 <> 0)
THEN
RETURN NULL;
ELSEIF (Block5 <> 0)
THEN
RETURN NULL;
ELSEIF (Block6 <> "FFFF")
THEN
RETURN NULL;
END IF;
RETURN IPv4;
END $$

Very handy.

File::Pid to catch multiple execution

CPAN carries a module called File::Pid, that implements a PID file object that writes to a file. The documentation suggests it be used as:

  use File::Pid;
  my $pidfile = File::Pid->new({
    file => '/some/file.pid',
  });
  $pidfile->write;
  if ( my $num = $pidfile->running ) {
      die "Already running: $num\n";
  }
  $pidfile->remove;

However, if you write() before calling running(), then the PID in the PID file gets overwritten with the current script’s PID, and thus running() always returns the current scripts PID, and thus, if you run the example, it always bombs out.
Instead, you want to put the call to write() after the call to running():

  use File::Pid;
  my $pidfile = File::Pid->new({file => '/some/file.pid',  });
  die "Already running in PID $num" if ( my $num = $pidfile->running );
  $pidfile->write;
  do_seomthing_useful();
  $pidfile->remove;

Easy.

Perl Search and Replace, using variables

Perl is a reasonable scripting language (as are others, so shh!). It has always had strong regular expression support; those regular expressions can also be used to do substitutions, such as:


my $pet = "I have a dog";
$pet =~ s/dog/cat/;

Neat enough. But lets say I want to look up the parts of the “s///” that define my search text, and my replacement text. Easy enough:


my $pet = "I have a dog";
my $search = "dog";
my $replace = "cat";
$pet =~ s/$search/$replace/;

But lets make our substitution a little more complex – I want to match a URL, and have the host and port lower case, but leave the path as the case it comes in, and I don’t want to be entering expressions as the replacement text! Lets try:


my $url = 'http://www.FOo.COm/wibbLE';
my $search = '^([^:]+://[^/]+)/?(.*)?$';
my $replace = '\L$1\E/$2';
print $url if $url =~ s/$search/$replace/;

Sadly, while $search matches, the replace is the string “\L$1\E/$2“. It appears that we need to use a combination of “/e” mode (evaluate as expression) to evaluage this replacement string. Of course, when we’re doing eval, we want to ensure we don’t have malicious content in $replace, such as “unlink()” and friends who could do Bad Things. So my solution was to escape double quotes from my $replace string, wrap that all in double quotes, and pass in “/ee“:


my $search = '^([^:]+://[^/]+(:\d+)?)/?(.*)?$'; # From database
my $replace = '\L$1\E/$3'; # From database
$replace =~ s/"/\\"/g; # Protection from embedded code
$replace = '"' . $replace . '"'; # Put in a string for /ee
print $url if $url =~ s/$search/$replace/ee;

This will give us:

  • Port and host name lower case
  • Hostname (and port) will always have a slash after it
  • Bad code like unlink() won’t be run
  • The expression that we initally set/fetch/got in $replace is just a vanilla replacement term, not arbitary Perl code.

Acer Aspire Revo 3700 with Linux

Looks like Acer is releasing a Linux model of its new Revo, product code “PT.SEMEC.003“; listed as a 160 GB HDD, 2 GB Linux version. Insight UK have them for £230. See this list of sites for prices; all around £200 – £300.  Gigabit ethernet sees nice, as does 802.11n if you need to fall back to wireless. Amazon ar elisting it for £349, so a little steep there. Here’s the press release from Acer, dated September 9.

HP Dreamscreen update: 23 Sept 2010 (1.6.2.0)

Yesterday, the 10th of October 2010 (10-10-10), my HP DreamScreen dutifylly informed me of a firmware update! Wow, I wasn’t expecting any more updates out of HP, given the age of the product. Down came 1.6.2.0, with a release/build date of “9/23/10“, and on boot telling me that to use the Facebook app there were new security options in Facebook to permit this. It claims to be fron the “HP Development Company”. Is that separate from HP? Googling around shows “Hp Development Company LLC is located at 4401 N Mesa St El Paso, TX 79902″. Hm.