ip2long、long2ipのテスト

MySQLにIPv4アドレス保存テスト用テーブルを作成し、PHPからPDOでIPv4アドレスのライトとリードのテストをします。

• テーブルの準備

プログラムテストのため、IPv4アドレスを保存するカラムを持つテーブルlogを作成します。 テーブル定義は以下になります。

CREATE TABLE log (
  id INTEGER PRIMARY KEY AUTO_INCREMENT,
  ip INTEGER UNSIGNED DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8

テストデータを１つインサートしておきます。

mysql> INSERT INTO log(ip) VALUES(INET_ATON('127.0.0.1'));
Query OK, 1 row affected (0.00 sec)

mysql> SELECT id, INET_NTOA(ip) FROM log;
+----+---------------+
| id | INET_NTOA(ip) |
+----+---------------+
|  1 | 127.0.0.1     |
+----+---------------+
1 row in set (0.00 sec)

• テストプログラム

<?php

class DbConfig {
        public $connector = 'mysql';
        public $host = 'localhost';
        public $dbname = 'test';
        public $user = 'user';
        public $password = 'password';
}

class Dao {
        protected $encoding = 'UTF8';

        protected $tableName = 'log';

        protected $config;
        protected $pdo;

        function  __construct($config = null) {
                $this->config = $config;
        }

        public function getConfig() {
                return $this->config;
        }

        public function getEncoding() {
                return $this->encoding;
        }

        public function getDsn($config) {
                $dsn = sprintf("%s:host=%s;dbname=%s;charset=utf8;",
                                $this->config->connector,
                                $this->config->host,
                                $this->config->dbname
                );
                return $dsn;
        }

        public function connect() {
                if (empty($this->pdo)) {
                        $dsn = $this->getDsn($this->config);
                        $user = $this->config->user;
                        $password = $this->config->password;

                        $this->pdo = new PDO($dsn, $user, $password);

                        // SQL実行エラーで例外を発生する
                        $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                        $this->pdo->query("SET NAMES '" .$this->getEncoding() ."';");
                }
                if (is_null($this->pdo)) {
                        throw new Exception("pdo error");
                }
                return $this->pdo;
        }

        public function close($pdo = null) {
                if (is_null($pdo)) {
                        $db = $pdo;
                } else {
                        if (empty($this->pdo)) {
                                return;
                        }
                        $db = $this->pdo;
                }
                $db = null;
        }

        public function select() {
                $sql = "SELECT ip FROM $this->tableName";
                $stmt = $this->pdo->prepare($sql);
                $ret = $stmt->execute();
                if ($ret === FALSE) {
                        throw new Exception("ERROR failed to select");
                }
                while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                        $ip = $row['ip'];
                        printf("ip: %10d (%s)\n", $ip, long2ip($ip));
                }
        }

        public function selectOther() {
                $sql = "SELECT INET_NTOA(ip) AS ip FROM $this->tableName";
                $stmt = $this->pdo->prepare($sql);
                $ret = $stmt->execute();
                if ($ret === FALSE) {
                        throw new Exception("ERROR failed to select");
                }
                while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                        $ip = $row['ip'];
                        printf("ip: %10d (%s)\n", ip2long($ip), $ip);
                }
        }

        public function insert($ip) {
                $sql = "INSERT INTO $this->tableName (ip) VALUES(?);";
                $values = array(ip2long($ip));
                $stmt = $this->pdo->prepare($sql);
                $ret = $stmt->execute($values);
                if ($ret === FALSE) {
                        throw new Exception("Error failed to insert");
                }
                printf("inserted ip: %s\n", $ip);
        }

        public function insertOther($ip) {
                $sql = "INSERT INTO $this->tableName (ip) VALUES(INET_ATON(?));";
                $values = array($ip);
                $stmt = $this->pdo->prepare($sql);
                $ret = $stmt->execute($values);
                if ($ret === FALSE) {
                        throw new Exception("Error failed to insert");
                }
                printf("inserted ip: %s\n", $ip);
        }
}

$dbConfig = new DbConfig();
$dao = new Dao($dbConfig);

$dao->connect();
$dao->select();
print("\n");

$dao->insert('192.168.10.5');
$dao->insertOther('255.255.255.254');
print("\n");

$dao->selectOther();
$dao->close();

• テスト実行

php test.php
ip: 2130706433 (127.0.0.1)

inserted ip: 192.168.10.5
inserted ip: 255.255.255.254

ip: 2130706433 (127.0.0.1)
ip: 3232238085 (192.168.10.5)
ip: 4294967294 (255.255.255.254)