Php Database - Content Encryption / Decryption

Question

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

encoding issue maybe ? please validate that what you get when you perform a SELECT is exactly what you INSERTed into the table before, using a checksum such as MD5 if necesary. — Calimero
– Calimero, Commented Sep 18, 2017 at 13:58
Thanks @Chris - Sorry, but that was an error on my end. That's not being used in the input of data, and I've removed it from the output, and it's still not working. I was just testing at the time I wrote that, and forgot to omit it. — Alec Weekes
– Alec Weekes, Commented Sep 18, 2017 at 14:07
There is no reason the openssl_decrypt function can't decrypt something correctly encrypted with openssl_encrypt and same parameters. I would first enable errors reporting and check error logs. The openssl_decrypt function returns false when used with wrong parameters, so you probably get an empty string (Boolean FALSE is converted to "" (the empty string), and you should see warnings accordingly. — Chris
– Chris, Commented Sep 18, 2017 at 16:03
It's hard to say. The function returns false because there is an error somewhere (the encrypted value? another parameter?...). I know my comment is not helpfull but, again, without seeing a complete sample and/or error logs, It's clearly impossible to say, sorry. — Chris
– Chris, Commented Sep 18, 2017 at 16:30
Could you please show the code which encrypts and inserts title into the table, and the complete output of SHOW CREATE TABLE <your_table_name_here>? — Binarus
– Binarus, Commented Sep 18, 2017 at 18:16

Scott Arciszewski · Accepted Answer · 2018-11-30 20:36:37Z

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.

Collectives™ on Stack Overflow

Php Database - Content Encryption / Decryption

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related