如何使用AES_ENCRYPT和PDO prepared语句改进大型加密数据库的变通方法？

Question

在MySQL中使用预准备语句时，一个参数只需使用一次。类似以下示例的编码将调用"SQLSTATEHY093: Invalid parameter number“

$enigma = 'ThisIsTheSecretEncryptionKey';
$data = [
    'name' => $name,
    'first_name' => $first_name,
    'gender' => $gender,
    'birthdate' => $birthdate,
    'email' => $email,
    'profession' => $profession,
    'enigma' => $enigma
];

$sql = "INSERT INTO members
(name , firstname , gender , birthdate, email, profession)
VALUES(
    AES_ENCRYPT(:name, :enigma),
    AES_ENCRYPT(:first_name, :enigma),
    AES_ENCRYPT(:gender, :enigma),
    AES_ENCRYPT(:birthdate, :enigma)
    AES_ENCRYPT(:email, :enigma)
    AES_ENCRYPT(:profession, :enigma)
)";

$pdo->prepare($sql)->execute($data);

为了克服这个问题，我找到了这个解决方案：

$enigma = 'ThisIsTheSecretEncryptionKey';
$data = [
    'name' => $name,
    'first_name' => $first_name,
    'gender' => $gender,
    'birthdate' => $birthdate,
    'email' => $email,
    'profession' => $profession,
    'enigma' => $enigma,
    'enigma2' => $enigma,
    'enigma3' => $enigma,
    'enigma4' => $enigma,
    'enigma5' => $enigma,
    'enigma6' => $enigma,
];

$sql = "INSERT INTO members
(name , firstname , gender , birthdate, email, profession)
VALUES(
    AES_ENCRYPT(:name, :enigma),
    AES_ENCRYPT(:first_name, :enigma2),
    AES_ENCRYPT(:gender, :enigma3),
    AES_ENCRYPT(:birthdate, :enigma4)
    AES_ENCRYPT(:email, :enigma5)
    AES_ENCRYPT(:profession, :enigma6)
)";

$pdo->prepare($sql)->execute($data);

它可以工作，但它不是一个真正流畅的解决方案，特别是当涉及到包含许多列的表时。在MySQL的加密数据库中使用预准备语句还有其他方法吗？

Answer 1

至少有两种选择

首先，您可以启用emulation mode for PDO (或者，不要在连接选项中禁用它)。在这种情况下，PDO将开始合理地处理命名占位符，并允许您重用它们，因此您只需要定义它一次。

$data = [
    'name' => $name,
    'first_name' => $first_name,
    'gender' => $gender,
    'birthdate' => $birthdate,
    'email' => $email,
    'profession' => $profession,
    'enigma' => $enigma,
];
$sql = "INSERT INTO members
(name , firstname , gender , birthdate, email, profession)
VALUES(
    AES_ENCRYPT(:name, :enigma),
    AES_ENCRYPT(:first_name, :enigma),
    AES_ENCRYPT(:gender, :enigma),
    AES_ENCRYPT(:birthdate, :enigma)
    AES_ENCRYPT(:email, :enigma)
    AES_ENCRYPT(:profession, :enigma)
)";

另一种选择是使用SQL变量。您可以运行查询(如果所有表都使用相同的谜语，则可以在连接之后每个脚本执行一次此查询)

$pdo->prepare("SET @aes_enigma=:enigma")->execute([$enigma]);

然后在查询中使用此变量

$data = [
    'name' => $name,
    'first_name' => $first_name,
    'gender' => $gender,
    'birthdate' => $birthdate,
    'email' => $email,
    'profession' => $profession,
];
$sql = "INSERT INTO members
(name , firstname , gender , birthdate, email, profession)
VALUES(
    AES_ENCRYPT(:name, @aes_enigma),
    AES_ENCRYPT(:first_name, @aes_enigma),
    AES_ENCRYPT(:gender, @aes_enigma),
    AES_ENCRYPT(:birthdate, @aes_enigma)
    AES_ENCRYPT(:email, @aes_enigma)
    AES_ENCRYPT(:profession, @aes_enigma)
)";

但老实说，我会不惜一切代价避免加密数据库。可能是几个表中的一些选定字段。但是，如果您不能对加密数据使用索引，那么只有最多几千行的玩具数据库才是真正可用的。