MariaDB Encryption & Security Functions

The default block_encryption_mode is aes-128-ecb which is deterministic — identical plaintexts produce identical ciphertexts, leaking patterns and enabling frequency analysis.

✓ Instead: Set block_encryption_mode to 'aes-256-cbc' and always generate a fresh IV with RANDOM_BYTES(16). Store IV in a separate column alongside the ciphertext.

AES_DECRYPT returns NULL both for NULL input and for decryption failures (wrong key, wrong mode, corrupt data). Treating NULL as 'empty' hides decryption errors silently.

✓ Instead: Check that the decrypted value is NOT NULL before using it, and ensure block_encryption_mode matches what was used during encryption.

MD5 is broken — collision attacks and preimage attacks are well-documented. Rainbow tables for MD5 cover most common passwords.

✓ Instead: Never use MD5 for passwords. Use application-level bcrypt/argon2, or at minimum SHA2(CONCAT(password, salt), 256) in the database.

SHA-1 collision attacks have been publicly demonstrated (SHAttered, 2017). It must not be used for digital certificates, code signing, or password storage.

✓ Instead: Use SHA2(str, 256) for general hashing and a proper KDF (bcrypt/argon2) for passwords.

Developers unfamiliar with MariaDB may assume SHA() refers to a modern SHA-2 variant. It is purely SHA-1.

✓ Instead: Be explicit: use SHA2(str, 256) for SHA-256 and SHA2(str, 512) for SHA-512.

Even SHA-512 is a fast hash, making it feasible to brute-force with GPU clusters. Passwords stored as raw SHA2 hashes can be cracked quickly.

✓ Instead: Hash passwords with bcrypt or argon2 in the application layer. If you must hash in SQL, add a random per-user salt and consider stretching with multiple iterations.

PASSWORD() uses a double-SHA1 scheme that is fast and not designed as a KDF. It is only suitable for MariaDB's own auth tables, not for storing user passwords in application tables.

✓ Instead: Store application passwords as bcrypt or argon2 hashes generated in application code, not in SQL.

OLD_PASSWORD uses a trivially broken custom hash algorithm with no salt. It offers essentially no security.

✓ Instead: Use PASSWORD() for MariaDB native auth, or application-level bcrypt/argon2 for application user tables.

ENCRYPT() returns NULL on Windows and may behave differently across Linux distributions depending on the glibc crypt() implementation available.

✓ Instead: Use AES_ENCRYPT() with a strong mode (aes-256-cbc) for portable, standards-based encryption.

ENCODE's cipher is easily reversible by an attacker who knows the algorithm. It does not provide cryptographic security.

✓ Instead: Replace all ENCODE usage with AES_ENCRYPT() using aes-256-cbc mode.

DECODE always returns a non-NULL result (when input is non-NULL) even if the wrong password is given, because it is XOR-based — there is no authentication tag to detect failure.

✓ Instead: Use AES_DECRYPT() in CBC mode with an HMAC or authenticated encryption (AES-GCM via application layer) so decryption failures are detectable.

COMPRESS() reduces size but does not hide data. Anyone with SELECT access to the column can call UNCOMPRESS() and read the plaintext.

✓ Instead: Apply AES_ENCRYPT() after COMPRESS() if you need both compression and confidentiality: AES_ENCRYPT(COMPRESS(data), key).

Calling UNCOMPRESS on data not produced by COMPRESS() returns NULL and generates a warning that is easy to miss in production.

✓ Instead: Track which columns contain COMPRESS()-compressed data with a naming convention (e.g., suffix _compressed) and validate before decompressing.

UNCOMPRESSED_LENGTH reads the first 4 bytes of any binary input and interprets them as a little-endian integer. Non-COMPRESS() input will return a random, meaningless number with no error.

✓ Instead: Only call UNCOMPRESSED_LENGTH on columns that were explicitly stored using COMPRESS().

RAND() is a pseudo-random number generator seeded with the current time; it is predictable. UUID() also uses time-based components. Neither is suitable for security-sensitive random values.

✓ Instead: Use RANDOM_BYTES(n) for all cryptographic randomness requirements (IVs, salts, tokens, nonces).

DES has a 56-bit key space that was exhaustively searched in 1998. Triple-DES (3DES) is also deprecated. There is no valid reason to use DES_ENCRYPT in any system built after 2000.

✓ Instead: Use AES_ENCRYPT() with block_encryption_mode set to 'aes-256-cbc' and RANDOM_BYTES(16) for the IV.

Data encrypted with DES is effectively unencrypted by any attacker with moderate compute resources.

✓ Instead: Schedule an immediate migration: decrypt with DES_DECRYPT(), re-encrypt with AES_ENCRYPT() using aes-256-cbc, and update the column type to VARBINARY.