PHP数据库加密字段搜索

wen PHP项目 2026-08-11 177

本文目录导读：

PHP数据库加密字段搜索

方案一：应用层加密 + 模糊搜索（推荐）
方案二：使用专门的加密搜索引擎
方案三：对称加密 + 哈希索引
方案四：使用模糊加密算法
最佳实践建议

在PHP中实现数据库加密字段的搜索，有几种常用方案,我来为你详细介绍：

应用层加密 + 模糊搜索（推荐）

基础加密存储

<?php
class EncryptService {
    private $key;
    private $cipher = 'aes-256-cbc';
    public function __construct($key) {
        $this->key = hash('sha256', $key, true);
    }
    // 加密
    public function encrypt($data) {
        $iv = openssl_random_pseudo_bytes(openssl_cipher_iv_length($this->cipher));
        $encrypted = openssl_encrypt($data, $this->cipher, $this->key, 0, $iv);
        return base64_encode($iv . '::' . $encrypted);
    }
    // 解密
    public function decrypt($data) {
        $parts = explode('::', base64_decode($data));
        $iv = $parts[0];
        $encrypted = $parts[1];
        return openssl_decrypt($encrypted, $this->cipher, $this->key, 0, $iv);
    }
}
?>

支持模糊搜索的方法

<?php
class UserModel {
    private $db;
    private $encryptor;
    public function __construct($db, $encryptor) {
        $this->db = $db;
        $this->encryptor = $encryptor;
    }
    // 精确搜索（可以解密后比较）
    public function searchExact($field, $value) {
        $encryptedValue = $this->encryptor->encrypt($value);
        $sql = "SELECT * FROM users WHERE {$field} = :value";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([':value' => $encryptedValue]);
        return $stmt->fetchAll();
    }
    // 模糊搜索方案1：解密后在应用层筛选
    public function searchFuzzy($field, $keyword) {
        $sql = "SELECT * FROM users WHERE {$field} IS NOT NULL";
        $stmt = $this->db->query($sql);
        $results = [];
        while ($row = $stmt->fetch()) {
            $decrypted = $this->encryptor->decrypt($row[$field]);
            if (strpos($decrypted, $keyword) !== false) {
                $row[$field . '_decrypted'] = $decrypted;
                $results[] = $row;
            }
        }
        return $results;
    }
}
?>

使用专门的加密搜索引擎

使用 Searchable Encryption 库

<?php
// 使用开源库 searchable-encryption
require_once 'vendor/autoload.php';
use SearchableEncryption\Database;
use SearchableEncryption\SearchableField;
class EncryptedSearchService {
    private $searchableDb;
    public function __construct() {
        $this->searchableDb = new Database([
            'host' => 'localhost',
            'database' => 'test',
            'username' => 'root',
            'password' => 'password'
        ]);
    }
    // 保存加密字段，支持模糊搜索
    public function saveUserWithSearchableField($userId, $data) {
        $field = new SearchableField('email', $data['email'], 'aes-256-cbc');
        $field->setSearchable(['email' => true]);
        $this->searchableDb->insert('users', $userId, [
            'email' => $data['email'],
            'phone' => $data['phone']
        ], [$field]);
    }
    // 模糊搜索
    public function searchUsers($keyword) {
        return $this->searchableDb->search('users', ['email' => $keyword]);
    }
}
?>

对称加密 + 哈希索引

双字段存储方案

<?php
class SecureSearchModel {
    private $db;
    private $key;
    public function saveData($table, $id, $data) {
        // 1. 加密字段
        $encryptedEmail = $this->encrypt($data['email']);
        // 2. 创建不可逆哈希（用于精确搜索）
        $emailHash = hash('sha256', $data['email']);
        // 3. 创建部分哈希（用于模糊搜索）
        // 将email分成4个字符的片段进行哈希
        $emailParts = $this->generatePartialHashes($data['email']);
        // 4. 存储
        $sql = "INSERT INTO {$table} (id, email_encrypted, email_hash, email_parts_hash) 
                VALUES (:id, :encrypted, :hash, :parts)";
        // ... 执行SQL
    }
    // 生成部分哈希，支持模糊搜索
    private function generatePartialHashes($value) {
        $parts = [];
        $length = strlen($value);
        for ($i = 0; $i < $length - 3; $i++) {
            $part = substr($value, $i, 4);
            $parts[] = hash('sha256', $part);
        }
        return json_encode($parts);
    }
    // 模糊搜索
    public function fuzzySearch($table, $keyword) {
        $keywordHash = hash('sha256', $keyword);
        // 搜索包含该关键字的记录
        $sql = "SELECT * FROM {$table} WHERE JSON_CONTAINS(email_parts_hash, :hash)";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([':hash' => json_encode($keywordHash)]);
        $results = [];
        while ($row = $stmt->fetch()) {
            // 解密确认
            $decrypted = $this->decrypt($row['email_encrypted']);
            if (strpos($decrypted, $keyword) !== false) {
                $results[] = $row;
            }
        }
        return $results;
    }
}
?>

使用模糊加密算法

可搜索加密方案（代码示例）

<?php
class FuzzyEncryption {
    private $key;
    public function encryptWithFuzzy($plainText) {
        // 分段加密
        $segments = str_split($plainText, 4); // 每4个字符一段
        $encryptedParts = [];
        foreach ($segments as $i => $segment) {
            // 每个段使用不同的IV
            $iv = hash('sha256', $this->key . $i, true);
            $encrypted = openssl_encrypt(
                $segment, 
                'aes-256-cbc', 
                $this->key, 
                0, 
                substr($iv, 0, 16)
            );
            $encryptedParts[] = $encrypted;
        }
        return implode(':', $encryptedParts);
    }
    // 模糊搜索
    public function fuzzySearch($searchTerm, $encryptedData) {
        $parts = explode(':', $encryptedData);
        $termSegments = str_split($searchTerm, 4);
        foreach ($termSegments as $i => $segment) {
            if (!isset($parts[$i])) return false;
            $iv = hash('sha256', $this->key . $i, true);
            $decrypted = openssl_decrypt(
                $parts[$i], 
                'aes-256-cbc', 
                $this->key, 
                0, 
                substr($iv, 0, 16)
            );
            // 前缀匹配
            if (strpos($decrypted, $segment) === 0) {
                return true;
            }
        }
        return false;
    }
}
?>

最佳实践建议

混合搜索方案

<?php
class HybridSearchService {
    private $db;
    // 高级搜索流程
    public function search($table, $field, $keyword, $mode = 'exact') {
        switch ($mode) {
            case 'exact':
                return $this->exactSearchByHash($table, $field, $keyword);
            case 'fuzzy':
                return $this->fuzzySearchMixed($table, $field, $keyword);
            case 'like':
                return $this->likeSearchByDecryption($table, $field, $keyword);
        }
    }
    // 组合搜索策略
    private function fuzzySearchMixed($table, $field, $keyword) {
        // 1. 先通过哈希索引快速定位
        $candidates = $this->findCandidatesByHash($table, $field, $keyword);
        // 2. 对候选集解密过滤
        $results = [];
        foreach ($candidates as $row) {
            $decrypted = $this->decryptField($row[$field . '_encrypted']);
            if (strpos($decrypted, $keyword) !== false) {
                $results[] = $row;
            }
        }
        return $results;
    }
}
?>

性能优化建议

<?php
// 添加搜索索引表
CREATE TABLE search_index (
    id INT PRIMARY KEY,
    field_name VARCHAR(50),
    keyword VARCHAR(255),
    record_id INT,
    INDEX idx_keyword (keyword)
);
// 建立搜索索引
class SearchIndexManager {
    public function buildIndex($table, $field, $data) {
        // 提取关键词
        $keywords = $this->extractKeywords($data);
        // 为每个关键词建立索引
        foreach ($keywords as $keyword) {
            $this->db->insert('search_index', [
                'field_name' => $field,
                'keyword' => hash('sha256', strtolower($keyword)),
                'record_id' => $data['id']
            ]);
        }
    }
    private function extractKeywords($text) {
        // 分词、去除停用词
        $words = preg_split('/[\s,.;!?]+/', $text);
        $keywords = array_filter($words, function($word) {
            return strlen($word) > 2;
        });
        return $keywords;
    }
}
?>

对于大多数场景，推荐使用方案一（应用层加密+应用层搜索）或方案二（加密搜索引擎）：