MySQL 防篡改實戰：觸發器鎖定關鍵字段實現數據不可變性

核心思路

利用 BEFORE UPDATE 觸發器，在更新發生前將敏感字段強制恢復為舊值，實現「可讀、可增、可刪、不可改」的字段級防篡改。

方案一：會員表核心字段鎖定

DROP TRIGGER IF EXISTS `trg_members_anti_tamper`;
DELIMITER //
CREATE TRIGGER `trg_members_anti_tamper`
BEFORE UPDATE ON `members`
FOR EACH ROW
BEGIN
    -- 強制保持原值，忽略 UPDATE 語句中傳入的新值
    SET NEW.name = OLD.name;
    SET NEW.cellphone = OLD.cellphone;
    SET NEW.email = OLD.email;
    SET NEW.password = OLD.password;
END//
DELIMITER ;

效果：

UPDATE members SET name = 'hacker', email = 'evil@x.com' WHERE id = 1;
-- 執行成功 (0 rows affected 警告)，但實際數據未變

方案二：賬戶流水不可變 (僅增不改)

CREATE TABLE `account` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user` VARCHAR(50) NOT NULL DEFAULT '0',
    `cash` DECIMAL(12,2) NOT NULL DEFAULT '0.00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 業務邏輯：每次變動插入新行 (正數收入、負數支出)
INSERT INTO account (user, cash) VALUES ('neo', -10), ('neo', -5), ('neo', 30), ('neo', -20);
-- 餘額 = SUM(cash) WHERE user='neo' = -5

觸發器保護餘額字段

DROP TRIGGER IF EXISTS `trg_account_anti_tamper`;
DELIMITER //
CREATE TRIGGER `trg_account_anti_tamper`
BEFORE UPDATE ON `account`
FOR EACH ROW
BEGIN
    SET NEW.cash = OLD.cash;  -- 鎖定金額字段
END//
DELIMITER ;

進階擴展：審計日誌聯動

CREATE TABLE `audit_log` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `table_name` VARCHAR(64) NOT NULL,
    `pk_value` VARCHAR(64) NOT NULL,
    `field_name` VARCHAR(64) NOT NULL,
    `old_value` TEXT,
    `new_value` TEXT,
    `attempt_user` VARCHAR(64),
    `attempt_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

DELIMITER //
CREATE TRIGGER `trg_members_audit`
BEFORE UPDATE ON `members`
FOR EACH ROW
BEGIN
    IF NEW.name <> OLD.name THEN
        INSERT INTO audit_log (table_name, pk_value, field_name, old_value, new_value, attempt_user)
        VALUES ('members', OLD.id, 'name', OLD.name, NEW.name, CURRENT_USER());
    END IF;
    -- 同樣處理 email、phone 等
    SET NEW.name = OLD.name;  -- 最後仍強制恢復
END//
DELIMITER ;

方案對比與選型

注意事項

• 觸發器不攔截 REPLACE INTO、LOAD DATA、ALTER TABLE、TRUNCATE 等 DDL/DML
• 需配合 REVOKE ALTER, DROP, TRUNCATE ON db.* FROM app_user 限制結構變更權限
• 大批量更新時觸發器逐行執行，可接受 (μs 級)
• 5.7+ 支援 SHOW TRIGGERS、information_schema.TRIGGERS 管理審計