How the ENCODE() function works in Mariadb?

The ENCODE() function is a string function that encrypts a string using a given key. It is not considered cryptographically secure, and should not be used for password encryption. To decrypt the result, use the DECODE() function.

Syntax

The syntax of the ENCODE() function is as follows:

ENCODE(string, password_string)

The function takes two arguments, where:

• string is the string to be encrypted. It can be any valid expression that returns a string value.
• password_string is the key to be used for encryption. It can be any valid expression that returns a string value.

The function returns a binary string of the same length as string. If either argument is NULL, the function returns NULL.

Examples

Example 1: Encrypting and decrypting a string

In this example, we use the ENCODE() function to encrypt a string using a password, and then use the DECODE() function to decrypt it.

SELECT
    HEX(ENCODE('Hello World', 'secret')) AS encrypted,
    DECODE(ENCODE('Hello World', 'secret'), 'secret') AS decrypted;

The output is:

+------------------------+-------------+
| encrypted              | decrypted   |
+------------------------+-------------+
| 78EA3607EAF4984C7F54F1 | Hello World |
+------------------------+-------------+

Example 2: Encrypting and decrypting a column

In this example, we use the ENCODE() function to encrypt a column from a table, and then use the DECODE() function to decrypt it.

CREATE TABLE messages (
  id INT PRIMARY KEY,
  message VARCHAR(255)
);

INSERT INTO messages VALUES
(1, 'This is a secret message'),
(2, 'Do not share this with anyone'),
(3, 'Only authorized users can read this');

SELECT id, HEX(ENCODE(message, 'key123')) AS encrypted FROM messages;

The output is:

+----+------------------------------------------------------------------------+
| id | encrypted                                                              |
+----+------------------------------------------------------------------------+
|  1 | BBF61D4497EB252AAA20755B39C6FD73CF02F5C6B8240FA5                       |
|  2 | A9E1958FBF52CE9221CED20310F353D638A524A273A2E9DE5FA0CD5EA9             |
|  3 | 0EEE2BE28590A61C780A8514C807C155F40CE62BAAC0DEDB5AE1FEAFCA502C0FD55FC8 |
+----+------------------------------------------------------------------------+

To decrypt the messages, we use the DECODE() function with the same key.

SELECT id, DECODE(encrypted, 'key123') AS decrypted FROM (SELECT id, ENCODE(message, 'key123') AS encrypted FROM messages) AS t;

The output is:

+----+-------------------------------------+
| id | decrypted                           |
+----+-------------------------------------+
|  1 | This is a secret message            |
|  2 | Do not share this with anyone       |
|  3 | Only authorized users can read this |
+----+-------------------------------------+

Related Functions

Some of the functions that are related to the ENCODE() function are:

• DECODE(): This function performs the opposite operation of the ENCODE() function. It decrypts a string using a given key. For example, DECODE(ENCODE('Hello World', 'secret'), 'secret') returns ‘Hello World’.
• AES_ENCRYPT(): This function encrypts a string using the AES algorithm and a given key. It is more secure than the ENCODE() function. For example, AES_ENCRYPT('Hello World', 'secret') returns a binary string.
• AES_DECRYPT(): This function decrypts a string using the AES algorithm and a given key. It is the inverse of the AES_ENCRYPT() function. For example, AES_DECRYPT(AES_ENCRYPT('Hello World', 'secret'), 'secret') returns ‘Hello World’.

Conclusion

The ENCODE() function is a simple string function that can encrypt a string using a given key. It can be used to obfuscate data or prevent casual snooping. However, it is not cryptographically secure, and should not be used for password encryption or sensitive data protection. To decrypt the result, use the DECODE() function with the same key. The ENCODE() function can be combined with other functions, such as HEX(), UNHEX(), or AES_ENCRYPT(), to perform various encryption operations.