CODEWITHSUNDEEP
phpmysqldatabasesecurityencryption
Alec Weekes
Alec Weekes

Reputation: 33

Php Database - Content Encryption / Decryption

Greetings Stack Overflow community,

I'm working on a website that will input data into a Database, and the data itself is rather sensitive. As a precaution I want to have some Encryption / Decryption occur, and while I have had success in some basic testing, it's not currently working when I try to pull the data from the Database itself.

Here is what I currently have working;

$plaintext      = 'Hello World';

$encrypted      = openssl_encrypt($plaintext, $cipher, $key, 0, $iv);

$decrypted      = openssl_decrypt($encrypted, $cipher, $key, 0, $iv);

echo "<pre>" . $encrypted . " - Encrypted Version </pre>";
echo "<pre>" . $decrypted . " - Decrypted Version </pre>";

This works just fine on the page itself (Image) as you can see in this image. When trying to input it into the Database, and retrieve it and Decrypt however, it stops working.

I can see the data inside the Database is encrypted, and I can echo out the results normally, however, when I attempt to use a mysqli_fetch_array() to loop through and retrieve the data, the same functionality that works just above, no longer works..

while($row = mysqli_fetch_array($result))
{

    $title = openssl_decrypt($row['title'], $cipher, $key, 0, $iv);

    echo "<pre>" . $title . " - Decrypted Version (MySQLi) </pre>";

}

Do note that the connection to the Database for the loop is working, as I can output the raw data itself. It's just through the openssl_decrypt that it no longer works.

I'm stumped for what's the cause, I have looked through a plethora of Stack Overflow questions on this, and many other online sources, but none of them are related to my problem.

I thank you for taking the time to read, and look forward to working through this issue.

Additional Information:

Php Version: 7.1.9 MySQL Version: 5.7.14 Apache Version: 2.4.23

Upvotes: 2

Views: 885

Answers (1)

Scott Arciszewski
Scott Arciszewski

Reputation: 34093

You might be interested in the supplementary material for a talk I did at Security B-Sides Orlando 2017, titled Building Defensible Solutions to Weird Problems, which covered searchable encrypted databases as one of the weird problems. It has fully-functional demo code contained within.

I've since written up my recommendations for searchable encryption in PHP.

We also implemented this design in a library called CipherSweet.

First question: What cipher mode are you using, and is it the same everywhere?

If, for example, you're encrypting with AES-256-CBC and then decrypting AES-128-ECB, you're going to get a padding error when decryption is attempted.

Recommendation: Use a library like defuse/php-encryption, which abstracts all of these details away and ensures they OpenSSL interface is being used consistently.

Second question: How are you storing this data in MySQL, and is column truncation the problem? If you're trying to store 32 characters in a varchar(30) column, you will not be able to decrypt the result successfully. I see that you're using base64, which obviates my third question (character encoding bugs).

Encryption is actually very difficult for anyone who isn't a dedicated cryptography expert to get right, which is why cryptography libraries written by experts and vetted by other experts exist. Of course, it's perfectly fine to tinker for the sake of learning.

Upvotes: 1

Related Questions