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
NOT NULLcolumns, nothing else specified.
- I’m inserting the data using this, whereas
keyis 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.
AES_ENCRYPT returns binary data (
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
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 🙂