Introduction
While creating a website, there will be information that should not be shown publicly, such as personal information. This would be the same in databases. This information is stored primarily through encryption. Although there may be differences in each type of DBMS, we analyze how the AES_ENCRYPT function works in MariaDB.
AES Encryption
The Advanced Encryption Standard (AES) replaces the DES used in the past and is a symmetric key encryption algorithm that uses the same key during encryption and decryption. Depending on the length of the key, it is divided into 128 bits, 192 bits, and 256 bits.
Based on Rijndael, this algorithm was selected for the AES contest of NIST in the United States, taking into account safety, efficiency, and implementation comprehensively. Although the eliminated algorithm was not insufficient, it is said that Rijndael’s algorithm was judged to meet the evaluation criteria compared to other algorithms.
Encryption in MariaDB
MariaDB uses the AES-128-ECB method. The ECB (Electronic Code Book) mode is the simplest mode and is a structure that encrypts sequentially in block units. However, as simple as it is, if only one block is decoded, the rest of the blocks are decoded as well.
The maximum length of the AES key is 256 bits, but it works without any problem even if you enter a key that exceeds 256 bits. If the key is shorter than 128 bits, the missing part is filled with bit 0 (Zero-Padding). If the key is longer than 128 bits, the key is split into 128 bits and an XOR operation is taken. Even if you enter a key of 256 bits or more, it actually works at 128 bits.
Conclusion
AES is a commonly used encryption algorithm, and there are various block management methods. In my case, I use the AES-128-CBC method as an encryption algorithm used for communication between the web (PHP) and DB. Of course, the SQL query in DB uses the AES_ENCRYPT function mentioned above. The CBC (Cipher Block Chaining) method additionally requires an Initialization Vector (IV). That is, the cipher-text may vary depending on the key value and the IV value.
MariaDB version 11.2 and later supports the default mode AES-128-ECB, as well as the 192-bit, 256-bit key ECB, CBC, and CTR modes. However, MariaDB version 11.2 is not Long Term Support (LTS), so care should be taken when using it. I also upgraded the version of MariaDB from 10.6 LTS to 10.11 LTS, but users prefer the LTS version, so it seems necessary to consider using the latest version that supports various encryption modes.