How to improve the workaround in PHP for large encrypted databases using AES_ENCRYPT with PDO prepared statements?

user_11634713 0
Viewed 78

Using prepared statements in MySQL you have to use a parameter only once. Coding like the example below will envoke "SQLSTATE[HY093]: 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);

To overcome this problem I found this solution:

$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);

It works but it is not a really smooth solution especially when it comes to tables containing lots of colums. Is there any other way using prepared statements in encrypted databases in MySQL?

prepared-statement pdo encryption mysql php
1 Answers

There are at least two options

First, you can enable the emulation mode for PDO (or, rather do not disable it in the connection options). In this case PDO will start to behave sensibly regarding named placeholders and will let you reuse them, thus you will need to define it only once.

$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)
)";

another option is to use an SQL variable. You can run a query (in case the same enigma is used for all tables, you can run this query once per script execution right after connect)

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

And then use this variable in your queries

$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)
)";

But to be honest, I would avoid encrypted databases at any cost. May be some selected fields in a few tables. But given you cannot use indices on the encrypted data, only a toy database of several thousand rows max could be really usable.

user_2855870
Related