本文目录导读：
在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;
}
}
?>
对于大多数场景，推荐使用方案一（应用层加密+应用层搜索）或方案二（加密搜索引擎）：
- 数据量小：使用方案一，直接在应用层解密搜索
- 数据量大：使用专业方案或建立搜索索引
- 需要实时搜索：考虑MySQL的加密函数（如AES_DECRYPT）配合使用
根据你的实际需求选择最合适的方案,注意平衡安全性和可用性。
