🔐

MariaDB Encryption & Security Functions

MariaDB

Complete reference for MariaDB encryption and security functions covering AES_ENCRYPT, AES_DECRYPT, MD5, SHA1, SHA2, PASSWORD, COMPRESS, RANDOM_BYTES, and KDF. Every function includes syntax, security notes, and deprecation warnings. Updated for MariaDB 11.x.

17 functions

Jump to function

AES_ENCRYPT AES_DECRYPT MD5 SHA1 SHA SHA2 PASSWORD OLD_PASSWORD ENCRYPT ENCODE DECODE COMPRESS UNCOMPRESS UNCOMPRESSED_LENGTH RANDOM_BYTES DES_ENCRYPT DES_DECRYPT

What are MariaDB Encryption & Security Functions?

MariaDB encryption functions provide hashing and symmetric encryption directly in SQL. AES_ENCRYPT() and AES_DECRYPT() implement AES-128/256 encryption, SHA2() supports SHA-224 through SHA-512, and RANDOM_BYTES() generates cryptographically secure random bytes. Note that MD5() and SHA1() are considered cryptographically broken for security purposes and should only be used for checksums or non-security hashing. The PASSWORD() function used for MariaDB authentication is deprecated in favour of the native password plugin.

AES_ENCRYPT

The init_vector parameter and block_encryption_mode support were added in MariaDB 10.0.15.→ VARBINARY

Encrypts a string using the AES (Advanced Encryption Standard) algorithm. The key and optional initialization vector determine the ciphertext. The encryption mode is controlled by the @@block_encryption_mode session variable (default: aes-128-ecb).

Signature

AES_ENCRYPT(str, key_str[, init_vector])

Parameters

Parameter	Type	Description
str	VARCHAR	The plaintext string to encrypt
key_str	VARCHAR	The encryption key; padded or truncated to the required key length
init_vector	VARCHAR	Optional initialization vector required for CBC/CFB/OFB modes; must be 16 bytes

Examples

Basic encryption, result shown as hex

sql

SELECT HEX(AES_ENCRYPT('secret', 'mykey'));

→-- hex-encoded ciphertext (ECB mode)

Round-trip encrypt/decrypt

sql

SELECT AES_DECRYPT(AES_ENCRYPT('data', 'mykey'), 'mykey');

→-- data

CBC mode with random IV

sql

SET @@session.block_encryption_mode = 'aes-256-cbc';
SELECT HEX(AES_ENCRYPT('msg', 'key', RANDOM_BYTES(16)));

→-- hex ciphertext

Store encrypted value

sql

INSERT INTO secure_data (payload) VALUES (AES_ENCRYPT('password123', 'secret_key'));

→-- 1 row inserted

NULL input returns NULL

sql

SELECT AES_ENCRYPT(NULL, 'key');

→-- NULL

⚠Anti-Pattern— Using default ECB mode

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.

The default block_encryption_mode (aes-128-ecb) is deterministic: identical plaintexts produce identical ciphertexts, leaking patterns. Set 'aes-256-cbc' and generate a fresh RANDOM_BYTES(16) IV per row. Store the IV in a separate column — it is not secret.

example

SET @@session.block_encryption_mode='aes-256-cbc'; INSERT INTO docs (data, iv) VALUES (AES_ENCRYPT('secret', 'key32chars', @iv:=RANDOM_BYTES(16)), @iv);

→1 row inserted with ciphertext and IV stored separately

◆MariaDB Note

Available in all MariaDB versions.

⇄ vs MySQL: Identical in MySQL 5.6+. The block_encryption_mode variable was added in MySQL 5.6.17.

⇄ vs PostgreSQL: PostgreSQL provides pgp_sym_encrypt() via the pgcrypto extension; no native AES_ENCRYPT equivalent.

AES_DECRYPT

The init_vector parameter was added in MariaDB 10.0.15.→ VARCHAR

Decrypts a ciphertext that was encrypted with AES_ENCRYPT() using the same key and mode. Returns the original plaintext string, or NULL if decryption fails (wrong key or corrupt data).

Signature

AES_DECRYPT(crypt_str, key_str[, init_vector])

Parameters

Parameter	Type	Description
crypt_str	VARBINARY	The encrypted binary string produced by AES_ENCRYPT()
key_str	VARCHAR	The decryption key; must match the key used for encryption
init_vector	VARCHAR	Initialization vector required when CBC/CFB/OFB mode was used for encryption; must match the IV used during encryption

Examples

Basic round-trip

sql

SELECT AES_DECRYPT(AES_ENCRYPT('hello', 'k'), 'k');

→-- hello

Decrypt stored ciphertext

sql

SELECT AES_DECRYPT(payload, 'secret_key') FROM secure_data WHERE id = 1;

→-- plaintext value

CBC mode requiring stored IV

sql

SET @@session.block_encryption_mode = 'aes-256-cbc';
SELECT AES_DECRYPT(payload, 'key', iv_col) FROM secure_data;

→-- decrypted plaintext

Wrong key returns NULL or junk

sql

SELECT AES_DECRYPT(AES_ENCRYPT('msg', 'key'), 'wrong_key');

→-- NULL or garbage

Cast result to character string

sql

SELECT CONVERT(AES_DECRYPT(payload, 'k') USING utf8mb4) FROM t;

→-- 'my text'

⚠Anti-Pattern— Assuming NULL means 'no data'

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.

AES_DECRYPT returns a VARBINARY result. Without CONVERT(... USING utf8mb4), client tools and string comparisons may misinterpret the bytes. Always cast the result to the expected character set immediately after decryption.

example

SELECT CONVERT(AES_DECRYPT(data, 'key32chars', iv) USING utf8mb4) AS plaintext FROM docs WHERE id = 1;

→plaintext: 'secret'

◆MariaDB Note

Available in all MariaDB versions.

⇄ vs MySQL: Identical in MySQL 5.6+.

⇄ vs PostgreSQL: PostgreSQL pgcrypto provides pgp_sym_decrypt(); no native AES_DECRYPT equivalent.

MD5

MariaDB 5.5+→ VARCHAR(32)

Computes the MD5 128-bit checksum of a string and returns a 32-character lowercase hexadecimal string. MD5 is a one-way hash function — it cannot be reversed. WARNING: MD5 is cryptographically broken and must NOT be used for password hashing or security-sensitive integrity checks.

Signature

MD5(str)

Parameters

Parameter	Type	Description
str	VARCHAR	The input string to hash; treated as a binary string for the computation

Examples

Basic MD5 hash

sql

SELECT MD5('hello');

→-- '5d41402abc4b2a76b9719d911017c592'

Case-sensitive hashing

sql

SELECT MD5('Hello') = MD5('hello');

→-- 0 (false)

NULL input returns NULL

sql

SELECT MD5(NULL);

→-- NULL

Hash with a salt (still use SHA2 in practice)

sql

SELECT MD5(CONCAT(user_id, salt)) FROM users;

→-- per-row hash strings

Integrity check (non-security use only)

sql

SELECT * FROM files WHERE MD5(content) = '5d41402abc4b2a76b9719d911017c592';

→-- matching rows

⚠Anti-Pattern— Storing MD5 password hashes

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.

MD5 is cryptographically broken and vulnerable to collision attacks. Use it only for non-security purposes like cache-busting keys or detecting accidental data corruption. For any security-relevant hash, use SHA2(str, 256) as the minimum.

example

SELECT MD5(CONCAT(content, updated_at)) AS cache_key FROM articles WHERE id = 7;

→cache_key: '5d41402abc4b2a76b9719d911017c592'

◆MariaDB Note

Available in all MariaDB versions.

⇄ vs MySQL: Identical in MySQL.

⇄ vs PostgreSQL: PostgreSQL provides md5() natively; pgcrypto extends with digest().

SHA1

MariaDB 5.5+→ VARCHAR(40)

Computes the SHA-1 160-bit hash of a string and returns a 40-character lowercase hexadecimal string. SHA1 is a one-way hash — it cannot be reversed. WARNING: SHA-1 is cryptographically broken (collision attacks demonstrated) and should not be used for security purposes.

Signatures

SHA1(str)

SHA(str)

Parameters

Parameter	Type	Description
str	VARCHAR	The input string to hash

Examples

Basic SHA-1 hash

sql

SELECT SHA1('hello');

→-- 'aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d'

SHA is a synonym for SHA1

sql

SELECT SHA('hello');

→-- same as SHA1('hello')

NULL input returns NULL

sql

SELECT SHA1(NULL);

→-- NULL

Row fingerprinting

sql

SELECT SHA1(CONCAT(email, created_at)) AS row_fingerprint FROM orders;

→-- 40-char hex per row

Hash of empty string

sql

SELECT SHA1('') ;

→-- 'da39a3ee5e6b4b0d3255bfef95601890afd80709'

⚠Anti-Pattern— Using SHA1 for certificate or password hashing

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.

SHA-1 collision attacks have been publicly demonstrated (SHAttered, 2017). It is no longer safe for integrity checks or digital signatures. The only acceptable reason to use SHA1 is backward compatibility with existing stored checksums.

example

SELECT SHA2(email, 256) AS email_hash FROM users;  -- replacing SHA1(email)

→email_hash: 64-character SHA-256 hex string

◆MariaDB Note

Available in all MariaDB versions.

⇄ vs MySQL: Identical in MySQL.

⇄ vs PostgreSQL: PostgreSQL does not have a native SHA1() function; use pgcrypto's digest(str, 'sha1').

SHA

MariaDB 5.5+→ VARCHAR(40)

Synonym for SHA1(). Computes the SHA-1 160-bit hash of a string and returns a 40-character lowercase hexadecimal string. Maintained for compatibility; prefer SHA1() or SHA2() in new code.

Signature

SHA(str)

Parameters

Parameter	Type	Description
str	VARCHAR	The input string to hash

Examples

SHA is identical to SHA1

sql

SELECT SHA('test');

→-- 'a94a8fe5ccb19ba61c4c0873d391e987982fbbd3'

Confirms SHA is a synonym

sql

SELECT SHA('test') = SHA1('test');

→-- 1 (true)

NULL returns NULL

sql

SELECT SHA(NULL);

→-- NULL

Column fingerprinting

sql

SELECT SHA(CONCAT(col1, col2)) FROM t;

→-- per-row SHA-1 hashes

Always 40 hex characters

sql

SELECT LENGTH(SHA('x'));

→-- 40

⚠Anti-Pattern— Assuming SHA means something newer

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.

SHA() is a direct alias for SHA1(). Someone reading the code may not immediately recognize it as SHA-1. Using SHA1() explicitly makes the algorithm obvious and also serves as a reminder to migrate to SHA2 in the future.

example

SELECT SHA1(data_col) AS sha1_check FROM files LIMIT 1;

→sha1_check: 'aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d'

◆MariaDB Note

Available in all MariaDB versions.

⇄ vs MySQL: Identical in MySQL.

⇄ vs PostgreSQL: No native SHA() in PostgreSQL; use pgcrypto's digest().

SHA2

Available since MariaDB 5.5.→ VARCHAR

Computes an SHA-2 family hash (SHA-224, SHA-256, SHA-384, or SHA-512) of a string and returns it as a lowercase hexadecimal string. The hash_length parameter selects the algorithm. SHA-256 and SHA-512 are currently considered cryptographically secure for hashing purposes.

Signature

SHA2(str, hash_length)

Parameters

Parameter	Type	Description
str	VARCHAR	The input string to hash
hash_length	INT	Bit length of the desired hash: 224, 256, 384, or 512. A value of 0 is treated as 256.

Examples

SHA-256 hash

sql

SELECT SHA2('hello', 256);

→-- '2cf24dba5fb0a30e26e83b2ac5b9e29e1b161e5c1fa7425e73043362938b9824'

SHA-512 hash

sql

SELECT SHA2('hello', 512);

→-- 128-char hex string

0 defaults to SHA-256

sql

SELECT SHA2('hello', 0);

→-- same as SHA2('hello', 256)

NULL input returns NULL

sql

SELECT SHA2(NULL, 256);

→-- NULL

Invalid hash_length returns NULL

sql

SELECT SHA2('hello', 999);

→-- NULL

Salted hash (prefer app-level KDF for passwords)

sql

SELECT SHA2(CONCAT(password, salt), 256) AS pwd_hash FROM users;

→-- 64-char hex strings

⚠Anti-Pattern— Using SHA2 alone for password storage

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.

SHA-256 is the practical minimum for new applications. SHA-512 provides a larger output space suitable for digital signatures and long-term data integrity. For password storage, SHA2 alone is too fast — always combine with a per-user salt and use application-level bcrypt or argon2.

example

SELECT SHA2(CONCAT(user_id, ':', RANDOM_BYTES(32)), 256) AS token FROM users WHERE id = 1;

→token: 64-character SHA-256 hex string (unique per call due to random bytes)

◆MariaDB Note

Available in all MariaDB versions.

⇄ vs MySQL: Identical in MySQL 5.5.5+.

⇄ vs PostgreSQL: PostgreSQL pgcrypto provides digest(str, 'sha256') and digest(str, 'sha512').

PASSWORD

MariaDB 5.5+→ VARCHAR(41)

Hashes a plaintext password string using the MySQL/MariaDB native password hashing scheme (a double-SHA1 variant prefixed with '*'). Used internally by MariaDB for the mysql_native_password authentication plugin. Returns a 41-character string starting with '*'.

Signature

PASSWORD(str)

Parameters

Parameter	Type	Description
str	VARCHAR	The plaintext password to hash

Examples

Hashed password for native auth

sql

SELECT PASSWORD('mypassword');

→-- '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4'

Change a user's password

sql

SET PASSWORD FOR 'user'@'localhost' = PASSWORD('newpass');

→-- password updated

Empty password hash

sql

SELECT PASSWORD('');

→-- '*' (empty string hash)

NULL returns NULL

sql

SELECT PASSWORD(NULL);

→-- NULL

Always 41 characters

sql

SELECT LENGTH(PASSWORD('x'));

→-- 41

⚠Anti-Pattern— Using PASSWORD() for application user passwords

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.

PASSWORD() uses a double-SHA1 scheme designed solely for MariaDB's mysql_native_password authentication. It has no salt and is not a key-derivation function. Application passwords must be hashed with bcrypt or argon2 in the application layer.

example

SET PASSWORD FOR 'app_user'@'%' = PASSWORD('new_secure_pass');

→MariaDB internal auth entry updated — do not use in application tables

◆MariaDB Note

MariaDB still supports PASSWORD() for the legacy mysql_native_password plugin. When using ed25519 or other modern auth plugins the function is not used.

⇄ vs MySQL: PASSWORD() was deprecated in MySQL 5.7 and removed in MySQL 8.0. MariaDB retains it for mysql_native_password compatibility.

⇄ vs PostgreSQL: PostgreSQL has no PASSWORD() function; user authentication uses pg_hba.conf and system-level password management.

OLD_PASSWORD

MariaDB 5.5+→ VARCHAR(16)

Returns the pre-MySQL-4.1 16-character password hash. Used only for compatibility with very old MySQL 3.x/4.0 clients that cannot handle the new 41-character hash format. This algorithm is extremely weak and must not be used in any new code.

Signature

OLD_PASSWORD(str)

Parameters

Parameter	Type	Description
str	VARCHAR	The plaintext password to hash using the old algorithm

Examples

16-char old-style hash

sql

SELECT OLD_PASSWORD('test');

→-- '378b243e220ca493'

Always 16 characters

sql

SELECT LENGTH(OLD_PASSWORD('x'));

→-- 16

NULL returns NULL

sql

SELECT OLD_PASSWORD(NULL);

→-- NULL

Empty password hash

sql

SELECT OLD_PASSWORD('') ;

→-- '0000000000000000'

Deterministic (no salt)

sql

SELECT OLD_PASSWORD('test') = OLD_PASSWORD('test');

→-- 1

⚠Anti-Pattern— Using OLD_PASSWORD for any new functionality

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.

OLD_PASSWORD() generates a 16-character hash with a trivially broken algorithm that has no salt and no resistance to brute force. The only valid use is extracting credentials from an ancient MySQL 3.x database during migration.

example

SELECT OLD_PASSWORD('test');  -- only during legacy credential migration

→'378b243e220ca493' (16-char hash — cryptographically worthless)

◆MariaDB Note

Retained in MariaDB for backward compatibility only.

⇄ vs MySQL: Present in MySQL for legacy reasons; behavior is identical. Removed from MySQL 8.0 along with PASSWORD().

⇄ vs PostgreSQL: PostgreSQL has no equivalent function.

ENCRYPT

MariaDB 5.5+→ VARCHAR

Encrypts a string using the Unix crypt() system call with the provided salt. On platforms where crypt() is not available (notably Windows), ENCRYPT() always returns NULL. The output depends on the underlying system's crypt() implementation. Deprecated — use AES_ENCRYPT() for portable encryption.

Signature

ENCRYPT(str[, salt])

Parameters

Parameter	Type	Description
str	VARCHAR	The string to encrypt
salt	VARCHAR	Optional two-character salt; if omitted, a random salt is chosen by the system

Examples

Unix crypt with explicit salt

sql

SELECT ENCRYPT('hello', 'ab');

→-- 'ab....' (system-dependent crypt output)

Random salt chosen by system

sql

SELECT ENCRYPT('hello');

→-- random-salted crypt output

Same salt produces same result

sql

SELECT ENCRYPT('hello', 'ab') = ENCRYPT('hello', 'ab');

→-- 1 (true)

NULL input returns NULL

sql

SELECT ENCRYPT(NULL, 'ab');

→-- NULL

Returns NULL on Windows (no crypt())

sql

-- On Windows:
SELECT ENCRYPT('hello', 'ab');

→-- NULL

⚠Anti-Pattern— Using ENCRYPT for cross-platform deployments

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.

ENCRYPT() wraps the OS crypt() function which is not available on all platforms. On Windows it always returns NULL, breaking any code that depends on it. Use AES_ENCRYPT() with aes-256-cbc for portable, standards-based encryption.

example

-- Avoid: SELECT ENCRYPT('hello', 'ab');  -- NULL on Windows
-- Use instead:
SET @@session.block_encryption_mode='aes-256-cbc'; SELECT HEX(AES_ENCRYPT('hello','key32chars__________',RANDOM_BYTES(16)));

→hex ciphertext (works on all platforms)

◆MariaDB Note

Returns NULL on Windows builds of MariaDB.

⇄ vs MySQL: Identical behavior in MySQL; also deprecated there.

⇄ vs PostgreSQL: PostgreSQL has no ENCRYPT() function; pgcrypto provides crypt() which wraps the same OS function.

ENCODE

MariaDB 5.5+→ VARBINARY

Encrypts a string str using password as the key with a simple XOR-based cipher and returns a binary string the same length as str. This is a weak, non-standard symmetric cipher — it provides minimal security. Deprecated in MariaDB. The counterpart function is DECODE().

Signature

ENCODE(str, password)

Parameters

Parameter	Type	Description
str	VARCHAR	The plaintext string to encrypt
password	VARCHAR	The key string used to generate the cipher stream

Examples

Basic encryption

sql

SELECT HEX(ENCODE('hello', 'mykey'));

→-- hex ciphertext (same length as 'hello')

Round-trip

sql

SELECT DECODE(ENCODE('hello', 'key'), 'key');

→-- 'hello'

NULL input returns NULL

sql

SELECT ENCODE(NULL, 'key');

→-- NULL

Output length equals input length

sql

SELECT LENGTH(ENCODE('hello', 'k'));

→-- 5

Empty key — avoid

sql

SELECT ENCODE('msg', '');

→-- same as ENCODE with empty key (weak)

⚠Anti-Pattern— Using ENCODE for sensitive data

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.

ENCODE uses a weak XOR-based cipher derived from MD5 key material. It offers virtually no security. DECODE does not detect wrong-key decryption — it returns garbled data silently. Migrate all ENCODE/DECODE usage to AES_ENCRYPT/AES_DECRYPT with aes-256-cbc.

example

-- Migration: decrypt old data and re-encrypt
UPDATE legacy SET encrypted = AES_ENCRYPT(DECODE(encrypted, 'old_key'), 'new_aes_key', RANDOM_BYTES(16));

→existing ENCODE ciphertext re-encrypted with AES-256-CBC

◆MariaDB Note

Deprecated in MariaDB; may be removed in a future major version.

⇄ vs MySQL: Identical in MySQL. Deprecated in MySQL 5.7, removed from MySQL 8.0.

⇄ vs PostgreSQL: PostgreSQL has no ENCODE() encryption function (there is an encode() for base64/hex encoding, which is different).

DECODE

MariaDB 5.5+→ VARCHAR

Decrypts a string that was encrypted with ENCODE() using the same password. Returns the original plaintext. Uses the same weak XOR-based cipher as ENCODE(). Deprecated — use AES_DECRYPT() for real encryption.

Signature

DECODE(crypt_str, password)

Parameters

Parameter	Type	Description
crypt_str	VARBINARY	The encrypted binary string produced by ENCODE()
password	VARCHAR	The key string; must match the key used with ENCODE()

Examples

Basic round-trip

sql

SELECT DECODE(ENCODE('hello', 'key'), 'key');

→-- 'hello'

Decrypt stored values

sql

SELECT DECODE(payload, 'mypassword') FROM legacy_table;

→-- plaintext strings

NULL returns NULL

sql

SELECT DECODE(NULL, 'key');

→-- NULL

Wrong key produces garbage, not NULL

sql

SELECT DECODE(ENCODE('msg', 'k'), 'wrong_key');

→-- garbled text

Verifying round-trip fidelity

sql

SELECT DECODE(ENCODE('abc', 'pass'), 'pass') = 'abc';

→-- 1 (true)

⚠Anti-Pattern— Trusting non-NULL output as successful decryption

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.

Because ENCODE/DECODE use XOR, there is no authentication tag. DECODE never returns NULL for wrong-key input — it silently returns meaningless bytes. This makes key rotation errors extremely hard to detect. Migrate to AES_DECRYPT which returns NULL on mode/key mismatches.

example

SELECT DECODE(ENCODE('hello','key'), 'wrong_key');  -- silently returns garbage

→non-NULL garbled bytes — no indication that the key was wrong

◆MariaDB Note

Deprecated in MariaDB.

⇄ vs MySQL: Identical in MySQL. Deprecated in MySQL 5.7, removed in MySQL 8.0.

⇄ vs PostgreSQL: PostgreSQL has no DECODE() encryption function (decode() in PostgreSQL handles base64/hex, not encryption).

COMPRESS

MariaDB 5.5+→ VARBINARY

Compresses a string using zlib compression and returns the result as a binary string. The returned value stores the uncompressed length in the first four bytes. COMPRESS is not encryption — the data is not secret, only smaller. Use UNCOMPRESS() to restore the original data.

Signature

COMPRESS(string_to_compress)

Parameters

Parameter	Type	Description
string_to_compress	VARCHAR	The string to compress; binary strings are also accepted

Examples

Compression ratio for repetitive data

sql

SELECT LENGTH(COMPRESS(REPEAT('a', 1000)));

→-- ~20 (highly compressible)

Small inputs may grow

sql

SELECT LENGTH(COMPRESS('x'));

→-- 13 (overhead > savings for tiny input)

Round-trip

sql

SELECT UNCOMPRESS(COMPRESS('hello world'));

→-- 'hello world'

NULL returns NULL

sql

SELECT COMPRESS(NULL);

→-- NULL

Store compressed data

sql

INSERT INTO blobs (data) VALUES (COMPRESS(@large_text));

→-- 1 row inserted

⚠Anti-Pattern— Confusing COMPRESS with encryption

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

COMPRESS adds a 4-byte length header and zlib overhead. For inputs under ~100 bytes the compressed output is larger than the input. JPEG, ZIP, and PNG files are already compressed and will not shrink. Always benchmark COMPRESS on real data samples.

example

SELECT LENGTH(src) AS original, LENGTH(COMPRESS(src)) AS compressed, LENGTH(COMPRESS(src)) / LENGTH(src) AS ratio FROM (SELECT REPEAT('abcdef', 500) AS src) t;

→original: 3000, compressed: ~32, ratio: 0.011 (highly compressible repeated data)

◆MariaDB Note

Available in all MariaDB versions.

⇄ vs MySQL: Identical in MySQL. Requires the server to be compiled with zlib support.

⇄ vs PostgreSQL: PostgreSQL has no COMPRESS() function; use the pg_lz_compress/pg_lz_decompress internal functions or application-side compression.

UNCOMPRESS

MariaDB 5.5+→ VARCHAR

Uncompresses a string that was compressed with COMPRESS(). Returns the original uncompressed string, or NULL if the argument was not a valid compressed value or is NULL. The input must be a value produced by COMPRESS().

Signature

UNCOMPRESS(string_to_uncompress)

Parameters

Parameter	Type	Description
string_to_uncompress	VARBINARY	A binary string produced by COMPRESS()

Examples

Basic round-trip

sql

SELECT UNCOMPRESS(COMPRESS('hello world'));

→-- 'hello world'

Decompress stored data

sql

SELECT UNCOMPRESS(data) FROM blobs WHERE id = 1;

→-- original text

NULL returns NULL

sql

SELECT UNCOMPRESS(NULL);

→-- NULL

Invalid compressed input

sql

SELECT UNCOMPRESS('not compressed');

→-- NULL (with error warning)

Cast binary result to character string

sql

SELECT CONVERT(UNCOMPRESS(data) USING utf8mb4) FROM blobs;

→-- readable text

⚠Anti-Pattern— Using UNCOMPRESS on arbitrary binary columns

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.

UNCOMPRESS returns a VARBINARY result. String comparisons, LIKE patterns, and client display all require the result to be cast to the appropriate character type using CONVERT(UNCOMPRESS(col) USING utf8mb4).

example

SELECT CONVERT(UNCOMPRESS(data) USING utf8mb4) AS original_text FROM documents WHERE id = 5;

→original_text: 'The quick brown fox...'

◆MariaDB Note

Available in all MariaDB versions.

⇄ vs MySQL: Identical in MySQL.

⇄ vs PostgreSQL: PostgreSQL has no UNCOMPRESS() function.

UNCOMPRESSED_LENGTH

MariaDB 5.5+→ INT UNSIGNED

Returns the length that the compressed string would be after decompression. It reads the 4-byte length header stored at the start of a COMPRESS() result without actually performing decompression, making it fast for size checks.

Signature

UNCOMPRESSED_LENGTH(compressed_string)

Parameters

Parameter	Type	Description
compressed_string	VARBINARY	A binary string produced by COMPRESS()

Examples

Uncompressed size without decompressing

sql

SELECT UNCOMPRESSED_LENGTH(COMPRESS('hello world'));

→-- 11

Correct length regardless of compression ratio

sql

SELECT UNCOMPRESSED_LENGTH(COMPRESS(REPEAT('x', 10000)));

→-- 10000

Compare compressed vs original sizes

sql

SELECT UNCOMPRESSED_LENGTH(data), LENGTH(data) FROM blobs;

→-- original_size, compressed_size per row

NULL returns NULL

sql

SELECT UNCOMPRESSED_LENGTH(NULL);

→-- NULL

Invalid input gives meaningless result

sql

SELECT UNCOMPRESSED_LENGTH('not_compressed');

→-- (arbitrary number from the first 4 bytes)

⚠Anti-Pattern— Calling UNCOMPRESSED_LENGTH on non-compressed data

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().

UNCOMPRESSED_LENGTH reads the stored size header without decompressing. Check it before calling UNCOMPRESS() to reject suspiciously large payloads (decompression bombs) that could exhaust memory.

example

SELECT id FROM documents WHERE UNCOMPRESSED_LENGTH(data) > 10485760;  -- flag rows > 10MB uncompressed

→IDs of documents that would expand to more than 10MB when decompressed

◆MariaDB Note

Available in all MariaDB versions.

⇄ vs MySQL: Identical in MySQL.

⇄ vs PostgreSQL: PostgreSQL has no equivalent function.

RANDOM_BYTES

Available since MariaDB 10.0.15.→ VARBINARY

Returns a binary string of the requested length filled with cryptographically random bytes sourced from the operating system's secure random number generator. Useful for generating initialization vectors, salts, nonces, and temporary secrets. Length must be between 1 and 1024.

Signature

RANDOM_BYTES(length)

Parameters

Parameter	Type	Description
length	INT	Number of random bytes to return; must be between 1 and 1024

Examples

Generate a 128-bit random value

sql

SELECT HEX(RANDOM_BYTES(16));

→-- 32 hex chars of random data

Generate IV for AES-CBC

sql

SET @@session.block_encryption_mode = 'aes-256-cbc';
SELECT HEX(AES_ENCRYPT('msg', 'key', RANDOM_BYTES(16)));

→-- random hex ciphertext

Create a password salt

sql

SELECT HEX(RANDOM_BYTES(32)) AS salt;

→-- 64-char hex salt

Zero length returns NULL

sql

SELECT RANDOM_BYTES(0);

→-- NULL (or error)

Over 1024 returns NULL

sql

SELECT RANDOM_BYTES(1025);

→-- NULL (out of range)

⚠Anti-Pattern— Using RAND() or UUID() for cryptographic randomness

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

RANDOM_BYTES uses the OS cryptographically secure random number generator. Never reuse IVs for AES-CBC — generate a fresh RANDOM_BYTES(16) per row. For salts and one-time tokens, use RANDOM_BYTES(32) for 256 bits of entropy.

example

INSERT INTO sessions (user_id, token, expires_at) VALUES (42, HEX(RANDOM_BYTES(32)), DATE_ADD(NOW(), INTERVAL 1 HOUR));

→token: 64-character cryptographically random hex string

◆MariaDB Note

Available in all MariaDB versions.

⇄ vs MySQL: Identical in MySQL 5.6.17+.

⇄ vs PostgreSQL: PostgreSQL pgcrypto provides gen_random_bytes(n); also available as gen_random_uuid() for UUIDs.

DES_ENCRYPT

MariaDB 5.5+→ VARBINARY

Encrypts a string using DES (Data Encryption Standard) with a key from the DES key file, or with an explicitly provided key string. Returns a binary string. DES uses only 56-bit keys and is completely broken by modern standards. Deprecated — use AES_ENCRYPT() instead. Requires the server to have been started with --des-key-file.

Signature

DES_ENCRYPT(str[, {key_num|key_str}])

Parameters

Parameter	Type	Description
str	VARCHAR	The plaintext string to encrypt
key_num	INT	Optional key number (0–9) referencing an entry in the DES key file
key_str	VARCHAR	Optional explicit key string to use instead of the key file

Examples

DES encrypt with explicit key

sql

SELECT HEX(DES_ENCRYPT('hello', 'mykey'));

→-- DES-encrypted hex ciphertext

Round-trip

sql

SELECT DES_DECRYPT(DES_ENCRYPT('hello', 'k'), 'k');

→-- 'hello'

NULL returns NULL

sql

SELECT DES_ENCRYPT(NULL, 'key');

→-- NULL

Requires --des-key-file at startup

sql

SELECT DES_ENCRYPT('data');

→-- NULL if no key file configured

Key number referencing key file entry

sql

SELECT HEX(DES_ENCRYPT('msg', 0));

→-- uses key 0 from key file

⚠Anti-Pattern— Using DES_ENCRYPT for any new application

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.

DES has a 56-bit key space exhaustively searched by a $250,000 machine in 1998. Any modern GPU can brute-force a DES key in minutes. There is no scenario where DES_ENCRYPT is acceptable for new or existing production data.

example

-- Migration step: decrypt DES, re-encrypt with AES
SELECT AES_ENCRYPT(DES_DECRYPT(legacy_col, 'old_des_key'), 'new_aes_256_key', RANDOM_BYTES(16)) AS new_ciphertext FROM legacy_table LIMIT 1;

→new_ciphertext: VARBINARY AES-256-CBC encrypted value

◆MariaDB Note

Deprecated in MariaDB. Requires the server to be started with --des-key-file for key-number mode. Returns NULL if SSL is not initialized or the key file is not loaded.

⇄ vs MySQL: Identical in MySQL. Deprecated in MySQL 5.7, removed in MySQL 8.0.

⇄ vs PostgreSQL: PostgreSQL has no DES_ENCRYPT; pgcrypto historically supported 3DES but it is also deprecated there.

DES_DECRYPT

MariaDB 5.5+→ VARCHAR

Decrypts a string encrypted with DES_ENCRYPT() using the same key. Returns the original plaintext, or NULL if decryption fails. DES is completely broken by modern standards. Deprecated — use AES_DECRYPT() instead.

Signature

DES_DECRYPT(crypt_str[, key_str])

Parameters

Parameter	Type	Description
crypt_str	VARBINARY	The encrypted binary string produced by DES_ENCRYPT()
key_str	VARCHAR	Optional key string; required if DES_ENCRYPT was called with an explicit key string rather than a key number

Examples

Basic round-trip

sql

SELECT DES_DECRYPT(DES_ENCRYPT('hello', 'mykey'), 'mykey');

→-- 'hello'

Decrypt legacy stored values

sql

SELECT DES_DECRYPT(payload, 'secret') FROM legacy_table;

→-- plaintext values

NULL returns NULL

sql

SELECT DES_DECRYPT(NULL, 'key');

→-- NULL

Wrong key

sql

SELECT DES_DECRYPT(DES_ENCRYPT('msg', 'k'), 'wrong');

→-- NULL or garbage

Key number embedded in ciphertext header

sql

SELECT DES_DECRYPT(crypt_col) FROM t;

→-- decrypted using key file

⚠Anti-Pattern— Leaving DES-encrypted data in production

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.

DES_DECRYPT is only useful during migration. When reading legacy DES-encrypted data, re-encrypt it with AES_ENCRYPT in the same transaction and update the column, then remove all DES_DECRYPT references from the codebase.

example

UPDATE legacy_table SET payload = AES_ENCRYPT(DES_DECRYPT(payload, 'des_key'), 'aes_key_32_chars____', RANDOM_BYTES(16)), iv_col = @iv WHERE processed = 0 LIMIT 1000;

→1000 rows migrated from DES to AES-256-CBC per batch

◆MariaDB Note

Deprecated in MariaDB.

⇄ vs MySQL: Identical in MySQL. Deprecated in MySQL 5.7, removed in MySQL 8.0.

⇄ vs PostgreSQL: PostgreSQL has no DES_DECRYPT function.

Related MariaDB Categories

Miscellaneous →System & Info →