Data Encryption in MariaDB

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply