Data Encryption in MariaDB

Question

It's the first time I'm using encryption in MariaDB, so I need to assure that I'm getting it right. I need to simply store some identifiers in an encrypted way, and am wondering if I'm doing it correctly. As I'm getting some unexpected behaviours upon data retrieval (sometimes only), I'm suspecting there's something wrong (and I've been checking with the docs etc, it all seems right..?).

• I'm storing the data in BLOB and NOT NULL columns, nothing else specified.
• I'm inserting the data using this, whereas key is generated via openssl rand -base64 32

INSERT INTO data_table ( encr_data ) VALUES( AES_ENCRYPT( "secret_string", "key" ) );

• I'm retrieving the data using this:

SELECT CAST( AES_DECRYPT( encr_data, "key" ) AS CHAR ) as encr_data FROM data_table;

Is that the proper way of doing it in MariaDB? The secret string consists of about 35 - 40 characters.

It's really fundamental for me as the application I'm coding is storing some secret data to be used with another API. In other words, the encryption should not bring any risks concerning the data integrity with itself. Just the thought of needing to regenerate all of the encrypted data due to whatever encryption error (making the encrypted + stored data unuseable)...

So I need to assure that no data is cropped of the encrypted string and that the encryption is done properly, hence the reason for this question.

Answer 1

AES_ENCRYPT returns binary data (BLOB or BINARY(...))

CASTing a BLOB to a CHAR makes a mess. Do not do that.

You can convert it to hex via HEX(AES_ENCRYPT(...)). That can be put into a VARCHAR or TEXT.

The secret string consists of about 35 - 40 characters.

If that is 40 emoji, that will take a lot of bytes. I suggest declaring the column one of this ways:

VARCHAR(200) COLLATE ascii_bin -- if storing the hex
BINARY(100) -- if storing the binary version

If the use is for password validation, AES is the wrong approach. Instead, use a one-way hash (MD5, SHA%, etc).

NULL vs NOT NULL -- That is up to the business logic. If you need "not set yet" (etc), then NULL is reasonable.

• DevelJoe: Thx a lot but some questions. You say that aes_encrypt() returns a hex, but the [docs] (mariadb.com/kb/en/aes_encrypt) say it returns a binary string?? That's actually also the reason why I felt the need of using blob columns. Otherwise I'd need to encrypt-cast-store and cast-decrypt-select, which seems to be more complicated to me, no?
• DevelJoe: Then, all I need to do in this case is use the secret string as an identifier which is used to call an external API, and that identifier has like 40 characters. What would u do in this case? Thx again, and sry for the follow-up precisions, but the approach I'll use has to be rock solid, to avoid any trouble with these API call identifiers in the future. So no, hashes would be inappropriate in this context, as I need to reuse the raw unencrypted data :))
• DevelJoe: Oh and btw, does aes_encrypt() actually need a key of a fix length? Like I can't use a random length encryption key??
• Rick James: Oops, mysql returned 0x...'; I mistook that as hex. I'll fix my answer.
• Rick James: @DevelJoe - Fixed.