2

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

Alec Weekes
  • 33
  • 5
  • 1
    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 Sep 18 '17 at 13:58
  • Hey Calimero, I can confirm that the data being put into the database, as well as what I am outputting is identical. – Alec Weekes Sep 18 '17 at 14:00
  • then we can get the database out of the way, it's a pure php issue. – Calimero Sep 18 '17 at 14:04
  • What u mean by no longer works? Are u getting any error? – Anandhu Nadesh Sep 18 '17 at 14:05
  • Hi @Anandhunadesh - As you can see in my first example, when I run it on the page itself, the functionality works fine, but when I pull it from the Database, it doesn't. I'm unsure of exactly what the cause is. Perhaps I am missing something. Edit: No error is reported. It's just blank. Nothing in the Logs or Front-end. – Alec Weekes Sep 18 '17 at 14:06
  • 1
    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 Sep 18 '17 at 14:07
  • so the data that u get before adding the fetch_array and after are same right? I mean there is no mismatch of data ryt? – Anandhu Nadesh Sep 18 '17 at 14:12
  • @Anandhunadesh - I can confirm that when I output the data without using `openssl_decrypt` and what I see in the Database are identical. – Alec Weekes Sep 18 '17 at 14:13
  • @AlecWeekes Okay, so what about the decrypted data? (Inside the loop). does it match the original data? – Anandhu Nadesh Sep 18 '17 at 14:17
  • @Anandhunadesh - I'm unable to see as the `openssl_decrypt` isn't outputting anything. – Alec Weekes Sep 18 '17 at 14:22
  • have you tried outputing the decrypted data inside the loop. Just to make sure that ur query actually returning the needed data echo $row['title'] – Anandhu Nadesh Sep 18 '17 at 14:28
  • @Anandhunadesh - Yup. That data is being returned. As mentioned, my connection to the database, and the output of the data itself is working, just not when I attempt to use `openssl_decrypt` to then make it readable again. – Alec Weekes Sep 18 '17 at 14:31
  • what does this `echo "
    " . $title . " - Decrypted Version (MySQLi)
    ";` line output?     – Anandhu Nadesh Sep 18 '17 at 14:35
  • @Anandhunadesh - The only thing output is; `- Decrypted Version (MySQLi)`. The `$title` itself is entirely blank, with no error messages in the Log or Font end. – Alec Weekes Sep 18 '17 at 14:40
  • thats weird. Try trim() before decryption. Might work – Anandhu Nadesh Sep 18 '17 at 14:41
  • @Anandhunadesh - When using both `$trimmed = trim($title)` & `var_dump($trimmed);` I get; `C:\wamp64\www\{omitted}\home.php:72:string '' (length=0)` – Alec Weekes Sep 18 '17 at 14:46
  • trim before decryption. `$trimmed= trim($row['title']); echo $trimmed` or var_dump($trimmed) – Anandhu Nadesh Sep 18 '17 at 14:51
  • then use $trimmed in openssl_decrypt() – Anandhu Nadesh Sep 18 '17 at 14:52
  • @Anandhunadesh - Sorry, I should have also said, I am using the `trim()` & `var_dump()` to check it with `$row['first_name]` and that is indeed, returning the correct output. It seems it's just with decrypt that nothing is returned. I have also tried changing the format at which the database holds the data, but neither `text`, `blob`, or `binary` is working. – Alec Weekes Sep 18 '17 at 14:54
  • @Anandhunadesh - I can confirm that performing a `trim();` & `var_dump();` such as `trim(openssl_decrypt($row['first_name'], $cipher, $key, 0, $iv));` returns `home.php:76:string '' (length=0)` – Alec Weekes Sep 18 '17 at 14:57
  • 1
    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](https://stackoverflow.com/questions/1053424/how-do-i-get-php-errors-to-display) 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)](http://www.php.net/manual/en/language.types.string.php#language.types.string.casting), and you should see warnings accordingly. – Chris Sep 18 '17 at 16:03
  • Also, there are a lot of things we can't check (what is actually inside the row, what do `$cipher`, `$key`, `$iv` contain...). See [How to create a Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve) – Chris Sep 18 '17 at 16:04
  • Hello @Chris - I appreciate you helping me. I just wanted to confirm that I am investigating as much as I can, but obviously a little hesitant to reveal the `$key`, `$cipher` and `$iv` however, you're right. Without them, we're not going to get very far. I've attached a Pastebin of them. I will change the `$key` later, for security reasons. https://pastebin.com/RxD5CHnG – Alec Weekes Sep 18 '17 at 16:12
  • Yep I don't want you to share sensitive data of course. Just try first to write `var_dump($title)` instead of echo the full string. I am sure you will see `Boolean(False)` – Chris Sep 18 '17 at 16:16
  • @Chris - You're correct. It's returning `false`, but the as I have said, I'm confused as to why when pulling from the Database it's returning `false`, but in my local version which is run on the page itself, it works fine. I've provided a Pastebin above with some of the details. I'll change them later for security reasons. – Alec Weekes Sep 18 '17 at 16:17
  • 1
    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 Sep 18 '17 at 16:30
  • **Comments are not for extended discussion or debugging sessions.** Normally, moderators would delete all of these, or move them to chat. I'm choosing to keep them because they seem relevant and useful, but please don't keep this up. [Edit] the question if you want to add more information. Post an answer if you want to recommend something to try that will solve the problem. If you just want to chat, use chat. – Cody Gray Sep 18 '17 at 17:16
  • 1
    Could you please show the code which *encrypts and inserts title* into the table, and the complete output of `SHOW CREATE TABLE `? – Binarus Sep 18 '17 at 18:16
  • @CodyGray - Sorry, but I don't have the reputation yet to make a Chat. And as I don't have an answer, I am unable to really give my responses as one. @Binarus - Firstly, I get the information after submitting it via a form with; `$first_name = mysqli_real_escape_string($connection, $_POST['first_name']);` and then I encrypt it with; `openssl_encrypt($first_name, $cipher, $key, 0, $iv);` – Alec Weekes Sep 19 '17 at 07:31
  • [Here's a room with these comments that you can chat in](http://chat.stackoverflow.com/rooms/154777/discussion-on-question-by-alec-weekes-php-database-content-encryption-decryp). Better to focus on improving the question so you can actually get an answer, though, rather than just chatting about it. That buries the information where others cannot find it. – Cody Gray Sep 19 '17 at 10:19

1 Answers1

1

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.

Scott Arciszewski
  • 30,409
  • 16
  • 85
  • 198