Are MariaDB's encryption and compression functions secure?

Question

MariaDB [(none)]> SET @key_str = SHA2('Is it secure?',512);
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SET @crypt_str = AES_ENCRYPT('cleartext',@key_str);
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> select @crypt_str from dual;
+------------------+
| @crypt_str       |
+------------------+
| ���5��!$�l           |
+------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT AES_DECRYPT(@crypt_str,@key_str) from dual;
+----------------------------------+
| AES_DECRYPT(@crypt_str,@key_str) |
+----------------------------------+
| cleartext                        |
+----------------------------------+
1 row in set (0.01 sec)

Docs in MariaDB KB Encryption, hashing and compression functions, such as ENCRYPT, DECRYPT, COMPRESS, PASSWORD. MariaDB KB doesn't write about how secure those functions are.

Do you recommend a cloud native app (12 factor) to offload Encryption to the database (in my case MariaDB)? Can my example code be improved to be more secure (but only with MariaDB functions)?

I found a (maybe) biased review of a product vendor MyDiamo (they wish to sell):

Why MySQL Internal Encryption Functions are not Sufficient

1. MySQL Internal Encryption Algorithms are not Safe

MySQL provides many algorithms such as AES, DES, SHA-1 and MD5 algorithms. Among these algorithms, MD5 and SHA-1 are proven to be not safe as they have been cracked. Also, for AES algorithms, the current MySQL 5.6 version generally available provides only the ECB operation mode which is also proven to be unsafe.

2. MySQL Internal Encryption Functions does not Provide Key Management

When encrypting data, not only is the encryption itself important, but also managing key is important. The importance of the key cannot be stressed strongly enough when encryption matters. Most encryption solutions however do not pay much attention to the key. When using MySQL internal encryption functions, the key is exposed to the source, thus no proper key management exists.

Answer 1

While MySQL currently provides other encryption modes, MariaDB appears to still only support the insecure ECB mode. In MySQL, you can set the mode to the slightly better CBC mode with:

SET @@session.block_encryption_mode = 'aes-256-cbc';

If you cannot do this on MariaDB, then it does not support any alternate mode. Even on MySQL where it is supported, CBC is not a great mode. What is really needed is authenticated encryption. Since neither MySQL nor MariaDB support that, it should be up to the application using the DB.

The password hashing is also poor. By default, you are (unfortunately) encouraged by the documentation to hash the password with SHA-1 or SHA-2, which are fast hashes but are not designed to be a KDF. Due to the speed, they can be attacked very effectively. This danger is amplified because no salt is used by default. Unless the "password" is a randomly generated string, this is inadequate for security. You must use a secure KDF, which is not natively provided.

As for the compression functions, what would make them secure or insecure? They use whatever compression library MariaDB has been compiled with and just return compressed data. Security in terms of arbitrary code execution should lie primarily with the linked library being used (e.g. zlib).

Do you recommend a cloud native app (12 factor) to offload Encryption to the database (in my case MariaDB)? Can my example code be improved to be more secure (but only with MariaDB functions)?

Unfortunately, product recommendations are off-topic for this site. You should design the application which uses the database such that it supports solid, authenticated encryption with a strong KDF. If you merely need to encrypt all of your database with the same key and only care about data-at-rest security, you can simply put the database files in an encrypted directory.