3

I wish to encrypt/decrypt data in my MySQL database stored on my server. I use a salted hash for my passwords. All encryption/decryption would occur on the server. I use PHP end points which my remote clients access. The end points then access the MySQL database.

I've got the following PHP code to cipher/decipher a message:

<?php
    $key = "mySecretKey";
    $cipher = "aes-128-gcm";
    $ivlen = openssl_cipher_iv_length($cipher);

    $iv = openssl_random_pseudo_bytes($ivlen);
    $ciphertext = openssl_encrypt("message to be encrypted", $cipher, $key,     $options=0, $iv, $tag);

    $original_plaintext = openssl_decrypt($ciphertext, $cipher, $key, 0, $iv, $tag);

    echo "original message: ".$original_plaintext."\n";

    // $iv and $tag change each time we encrypt the data, so store these
?>

... which I was going to use to store encrypted data in my database. However, I will also need to store $iv and $tag with my data as these change for each openssl_encrypt executed.

On the other hand, I could just use MySQL's AES_ENCRYPT functions. This option would much simplify my code, because I can just add AES_ENCRYPT(data, key) to my SQL code.

However, I get the feeling the PHP solution is more secure than AES_ENCRYPT alone. Is this true?

On the other hand, I'm not so sure. For example, if somebody got hold of my AES_ENCRYPT key, then they could quite as easily get hold of my PHP $key in which case they would also be able to decrypt the openssl_encrypt-ed messge too, since the $iv$ and $tag information would probably be as easy to get hold of if they've got their hands on the $key somehow.

Or maybe I'm going about my PHP solution the wrong way. Maybe $iv should not be generated each time encrypt a piece of information? Maybe $iv should be final, i.e. always the same, in which case $tag is always the same...

AndrolGenhald
  • 15,436
  • 5
  • 45
  • 50

2 Answers2

3

If you would do the encryption on the MySQL-server side, you would have to send the (unencrypted) data to the MySQL-server. To do this securely, you would need to ensure an encrypted connection to the MySQL-server to protect the data in-transit. Depending on your setup, the data could (partially) end up in a log file somewhere. This all adds a layer of complexity, without added security benefit.

Also read: Is “Why Should You Avoid AES In MySQL?” true?.


Encrypt on the PHP side
If you encrypt the data on the PHP side, you can make use of user-friendly packages (SymmetricEncryption or php-encryption spring to mind), and just store the encrypted string in your DB, which is a lot less complex.
Given your question about, for example, the requirement for the $iv, I strongly suggest you use a standard library!

So, in summary: Yes, using PHP OpenSSL is safer than MySQL AES_ENCODE/DECODE.

Jacco
  • 7,402
  • 4
  • 32
  • 53
  • Thanks. I will be using SSL / TLS secure connection for data transfer, so in theory the transfer of data is not an issue. I'm not sure sending encrypted data to the client (a desktop application in my case) is a good thing, because then the client will need the decryption keys. And then this opens my database up to hackers if that was compromised. If I keep everything on the server then the keys are hidden behind the server. Then again if the hackers got the db, then they can probably get the keys. Seems to me this security stuff can never be fool proof. Its just a case of making it difficult. –  Apr 24 '18 at 10:49
  • Are you writing a desktop application in PHP? – Jacco Apr 24 '18 at 13:23
  • No my desktop application is in Java/JavaFX, and my server end points are in PHP. I access the end points via SSL / TLS. The end points access my MySQL database on the server then return JSON as necessary via SSL / TLS to the client Java app. I also use JWT for auth. I'm using HTTPS POST for sending data. –  Apr 24 '18 at 13:35
  • 1
    In that case, do your encryption on the PHP side, use the [SymmetricEncryption](https://github.com/Pixelfck/SymmetricEncryption), a plug-and-play package, for an easy and secure implementation, that has all the details preconfigured. – Jacco Apr 24 '18 at 13:40
  • Just checking... does this give the same security as using `AES_ENCRYPT` in the SQL code ? Either way I'll check out SymmetricEncryption. Thanks. –  Apr 24 '18 at 13:46
  • SymmetricEncryption looks very nice. Looks like SE takes your password and stretches it out into a longer password, which is (maybe?) as good as a key. I guess I'd be ok to use the user's salted and hashed password as the encryption/decryption key because I'm doing everything on the server side. –  Apr 24 '18 at 13:53
  • 1
    Yes, the SymmetricEncryption provides *better* security than MySQL's AES_ENCRYPT. Apart from the issues with MySQL's encryption functions, it adds a well known 'extra layer' (Encrypt-then-mac) that adds to the overall security. But most importantly: it is completely plug-and-play, no configuration needed. – Jacco Apr 24 '18 at 14:11
  • I just integrated SymmetricEncryption into my code, and the encryption seems way too slow. The encryption process is adding 26 seconds for my update record button to complete... does this seem right? –  Apr 25 '18 at 20:10
  • ... follow up... judging from this article, the slow speed is to do with the key stretching from the password. I am encrypting a bunch of things one after the other so I should optimise for this situation. https://github.com/defuse/php-encryption/issues/318 –  Apr 25 '18 at 20:27
  • @Antinous 'slow' is quite relative. If you keep the number of PBKDF2 iterations low (the default is 12), the slowdown will hardly be noticeable on any half-decent machine. This slowdown only starts to add up if you would attempt to brute-force crack the key, in which case you would try as many attempts per second as possible. – Jacco Apr 26 '18 at 10:47
  • But I'm only encrypting 5 small strings, say 100 characters long each, one after the other. 26 seconds is way too long. It's the library's `_PBKDF2` function that's slowing it down, even at default 12. I'm running the library on my commercial server provider's computers, so I'm guessing they are fast... could this library not be updated to use PHP's `password_hash` to replace `PBKDF2`... it could be much faster. –  Apr 26 '18 at 11:01
1

Do not use AES_ENCRYPT unless you fully understand the issues and how to use it properly!

There are several significant issues with AES_ENCRYPT:

It has poor defaults

The default block_encryption_mode is aes-128-ecb. Anyone who understands what ecb is and sees it here as a default is probably cringing right now. Suffice it to say that it can leak a significant amount of information.

It recommends insecure key derivation

From the documentation:

A passphrase can be used to generate an AES key by hashing the passphrase. For example:

INSERT INTO t
VALUES (1,AES_ENCRYPT('text', UNHEX(SHA2('My secret passphrase',512))));

Do not pass a password or passphrase directly to crypt_str, hash it first. Previous versions of this documentation suggested the former approach, but it is no longer recommended as the examples shown here are more secure.

Apparently they previously recommended using a password directly as the key! Now they've improved it to a single iteration of a fast hash function with no salt. For why this is still pretty terrible see here. The correct method would be to use a password based key derivation function such as PBKDF2.

It doesn't support authenticated encryption

The supported modes are ECB, CBC, CFB1, CFB8, CFB128, OFB. Most of these modes are malleable to a certain extent, and some of them allow you to flip a specific bit in the ciphertext to flip the same bit in the plaintext. Your question suggests you'd be using GCM which prevents this by checking the authenticity.

Even if I didn't want authenticated encryption I would personally prefer to avoid using something with such insecure defaults and recommendations, and as Jacco points out, encrypting in PHP is better if you can't guarantee that the connection between PHP and MySQL uses TLS.

AndrolGenhald
  • 15,436
  • 5
  • 45
  • 50
  • Thanks. My PHP code are the end points so work with my MySQL database on the server. Nothing sensitive occurs on the client side. It's all done on the server. –  Apr 24 '18 at 14:34
  • @Antinous I think you misunderstand what Jacco is saying about using TLS with MySQL. Often the database is hosted on its own server. Using TLS between the web app server and the database server is a completely separate issue from using TLS between the client and the web app server. – AndrolGenhald Apr 24 '18 at 14:38
  • Ah yes, I remember reading something about that. So what you're saying is that, ok I get my data to **my** server vis SSL / TLS (HTTPS), but then when I make my calls from my server (through my PHP) to the server's SQL server, there is another connection made to another server I don't know about. So it is best to encrypt in the PHP before sending to the SQL server, because I may not be able to guarantee security between my server and my server's SQL server. Wow, don't assume anything in security !!! –  Apr 24 '18 at 14:41