スパム対策の効果確認

メールのスパム（フィッシング）対策をpostfixのheader_checksでやってるのですが、どのルールがどの程度効果的なのか、このFromって何だっけ？となるのでmail.logと付け合せしてDBに保存するようにしてみました。
Pythonで書くのが一般的な気がするのですが慣れたPHPで書いてます。  

まずはデータベースのテーブルから  
```table.sql
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;

CREATE TABLE `header_check` (
  `id` int UNSIGNED NOT NULL,
  `regex` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_ja_0900_as_cs NOT NULL,
  `action` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_ja_0900_as_cs NOT NULL,
  `text` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_ja_0900_as_cs NOT NULL,
  `note` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_ja_0900_as_cs DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ja_0900_as_cs;

ALTER TABLE `header_check`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `header_check`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

CREATE TABLE `datenum` (
  `id` INT UNSIGNED NOT NULL,
  `hdate` DATE NOT NULL,
  CONSTRAINT `fk_header_check`
    FOREIGN KEY (`id`) REFERENCES `header_check`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ja_0900_as_cs;
COMMIT;
```
データーベース名は好きにしてください。後述のスクリプトではpostfixにしてます。  

次は保存部分（定数は環境に合わせて書き換えてください）
```ReadHederChecks.php
<?php
define('HEADER_CHECKS', '/etc/postfix/header_checks');
define('MAIL_LOG', '/var/log/mail.log.1');
define('DB_HOST', 'localhost');
define('DB_USER', 'dbuser');
define('DB_PASS', 'dbpass');
define('DB_NAME', 'postfix');
define('DB_CHAR', 'utf8mb4');

class ReadHeaderCheck
{
	private $pattern = [];
	private $db = null;
	
	public function readHeaderChecks()
	{
		$fp = @fopen(HEADER_CHECKS, "r");
		if ($fp) {
			while (($buffer = fgets($fp, 4096)) !== false) {
				if ( preg_match("/(!?)(\/.*)\t(DISCARD|REJECT)/", $buffer, $match) ) {
					$this->pattern[] = [
						str_replace("\t", "", $match[2]), 
						$match[3], 
						str_replace('/', '', mb_decode_mimeheader(str_replace('\\', '', $match[2]))), 
						[], 
						$match[1]
					];
				}
			}
			fclose($fp);
		}
	}
	public function readlog()
	{
		$fp = @fopen(MAIL_LOG, "r");
		if ($fp) {
			while (($buffer = fgets($fp, 4096)) !== false) {
				if ( preg_match("/(reject|discard): header (.*)/", $buffer, $match) ) {
					$date = substr($buffer, 0, 10);
					foreach ($this->pattern as $key => $pat) {
						if (preg_match($pat[0], $match[2])) {
							$this->pattern[$key][3][] = $date;
							break;
						}
					}
				}
			}
			fclose($fp);
		}
	}
	public function dbinsert()
	{
		$this->db = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
		if ($this->db->connect_error) {
			die("DB接続失敗: " . $this->db->connect_error);
		}
		$this->db->set_charset(DB_CHAR);
		foreach ($this->pattern as $pat) {
			$id = $this->getid($pat[4].$pat[0]);
			if ( $id > 0 ) {
				$this->update([$pat[4].$pat[0], $pat[1], $pat[2], $id]);
			} else {
				$id = $this->insert([$id, $pat[4].$pat[0], $pat[1], $pat[2], '']);
			}
			
			$this->delete_sub($id, array_unique($pat[3]));
			$this->insert_sub($id, $pat[3]);
		}
	}
	private function getid($reg)
	{
		$ret = 0;
		$stmt = $this->db->prepare("SELECT IFNULL(`id`,0) FROM `header_check` WHERE `regex` = ?");
		$stmt->bind_param('s', $reg);
		$stmt->execute();
		$stmt->bind_result($id);
		while ($stmt->fetch()) {
			$ret = $id;
		}
		return $ret;
	}
	private function insert($pat)
	{
		$stmt = $this->db->prepare("INSERT INTO `header_check` VALUES (?, ?, ?, ?, ?)");
		$stmt->bind_param('issss', $pat[0], $pat[1], $pat[2], $pat[3], $pat[4]);
		$stmt->execute();
		return $this->db->insert_id;
	}
	private function update($pat)
	{
		$stmt = $this->db->prepare("UPDATE `header_check` SET `regex` = ?, `action` = ?, `text` = ? WHERE `id` = ?");
		$stmt->bind_param('sssi', $pat[0], $pat[1], $pat[2], $pat[3]);
		$stmt->execute($pat);
	}
	private function delete_sub($id, $array)
	{
		$stmt = $this->db->prepare("DELETE FROM `datenum` WHERE `id` = ? AND `hdate` = ?");
		foreach($array as $parm) {
			$stmt->bind_param('is', $id, $parm);
			$stmt->execute();
		}
	}
	private function insert_sub($id, $array)
	{
		$stmt = $this->db->prepare("INSERT INTO `datenum` VALUES (?, ?)");
		foreach($array as $parm) {
			$stmt->bind_param('is', $id, $parm);
			$stmt->execute();
		}
	}
}
$c = new ReadHeaderCheck();
$c->readHeaderChecks();
$c->readlog();
$c->dbinsert();
```
``/var/log/mail.log.1``を読み込んでますのでログロテートした後に実行です。  
※どうせならPDOで書けば良かった（気が向いたら書き直そう）

表示部分は手を抜いて？ChatGPTに書いてもらいました。（若干手を入れてます）
```view_hits.php
<?php
define('DB_HOST', 'localhost');
define('DB_USER', 'dbuser');
define('DB_PASS', 'dbpass');
define('DB_NAME', 'postfix');

$db = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
$db->set_charset("utf8mb4");

$sql = "
	SELECT 
		MIN(`hdate`), MAX(`hdate`)
	FROM `datenum`
";

$result = $db->query($sql);
list($min, $max) = $result->fetch_row();

$sql = "
    SELECT 
        h.id,
        h.regex,
        h.action,
        h.text,
        COUNT(d.hdate) AS total_hits,
		MIN(d.hdate) AS min_date,
		MAX(d.hdate) AS max_date
    FROM header_check h
    LEFT JOIN datenum d ON h.id = d.id
    GROUP BY h.id
    ORDER BY total_hits DESC
";

$result = $db->query($sql);

?>
<!DOCTYPE html>
<html lang="ja">
<head>
    <meta charset="UTF-8">
    <title>Postfix Header Check Report - トータルヒット順</title>
    <style>
        body { font-family: sans-serif; padding: 20px; }
        table { border-collapse: collapse; width: 100%; margin-bottom: 40px; }
        th, td { border: 1px solid #ccc; padding: 8px; text-align: left; }
        th { background: #f0f0f0; }
        code { font-family: monospace; background: #f8f8f8; padding: 2px 4px; }
    </style>
</head>
<body>
    <h1>総ヒット件数リスト　<?php echo $min;?>　～　<?php echo $max;?></h1>
    <table>
        <thead>
            <tr>
                <th>ID</th>
                <th>説明</th>
                <th>アクション</th>
                <th>正規表現</th>
                <th>総ヒット件数</th>
				<th>～</th>
            </tr>
        </thead>
        <tbody>
            <?php while ($row = $result->fetch_assoc()): ?>
                <tr>
                    <td><?php echo htmlspecialchars($row['id']); ?></td>
                    <td><?php echo htmlspecialchars($row['text']); ?></td>
                    <td><?php echo htmlspecialchars($row['action']); ?></td>
                    <td><code><?php echo htmlspecialchars($row['regex']); ?></code></td>
                    <td><?php echo htmlspecialchars($row['total_hits']); ?></td>
					<td><?php echo $row['min_date']; ?>～<?php echo $row['max_date']; ?></td>
                </tr>
            <?php endwhile; ?>
        </tbody>
    </table>
</body>
</html>
```

まだまだ機能は足りなくて若干強引ですが、手元の環境(Ubuntu 24.04.2 LTS)では動作してます。

2025/5/13:SQLが間違っていたので修正、否定のパターンに対応