How the DECODE() function works in Mariadb?

The MariaDB DECODE() function is used to decode data that was previously encoded using the ENCODE() function. It is often utilized in scenarios where sensitive data needs to be stored in an encoded format and then retrieved and decoded for use in applications.

Syntax

The syntax for the MariaDB DECODE() function is as follows:

DECODE(crypt_str, pass_str)

crypt_str is the string that has been encrypted using the ENCODE() function.
pass_str is the password string that was used for encryption with ENCODE().

Examples

Example 1: Decoding an Encoded String

To decode a string that was previously encoded:

SELECT DECODE(ENCODE('hello', 'password'), 'password') AS decoded_string;

The output will be the original string before encoding:

+----------------+
| decoded_string |
+----------------+
| hello          |
+----------------+

Example 2: Decoding with an Incorrect Password

Attempting to decode with an incorrect password:

SELECT DECODE(ENCODE('hello', 'password'), 'wrong_password') AS decoded_string;

The output will not return the original string:

+----------------+
| decoded_string |
+----------------+
| ??i??              |
+----------------+

Example 3: Decoding a Null Value

Decoding a NULL value:

SELECT DECODE(NULL, 'password') AS decoded_string;

The output will be NULL:

+----------------+
| decoded_string |
+----------------+
| NULL           |
+----------------+

Example 4: Decoding an Empty String

Decoding an empty string:

SELECT DECODE('', 'password') AS decoded_string;

The output will be an empty string:

+----------------+
| decoded_string |
+----------------+
|                |
+----------------+

Example 5: Using `DECODE()` in a Table

Decoding data from a table:

DROP TABLE IF EXISTS encoded_data;
CREATE TABLE encoded_data (
    id INT,
    encoded_value BLOB
);
INSERT INTO encoded_data VALUES (1, ENCODE('Hello, World!', 'password'));

SELECT DECODE(encoded_value, 'password') AS decoded_value FROM encoded_data WHERE id = 1;

The output will show the decoded value from the table:

+---------------+
| decoded_value |
+---------------+
| Hello, World! |
+---------------+

Here are a few functions related to the MariaDB DECODE() function:

MariaDB ENCODE() function is used to encode a string using a password.
MariaDB AES_ENCRYPT() and AES_DECRYPT() functions provide encryption and decryption using the AES algorithm.
MariaDB PASSWORD() function is used to create a hashed password for use in MariaDB authentication.

Conclusion

The DECODE() function in MariaDB serves as a counterpart to the ENCODE() function, allowing for the secure storage and retrieval of sensitive data. By using DECODE() in conjunction with ENCODE() and other encryption functions, developers can ensure that data is protected and only accessible to those with the correct password or key, enhancing the security of their applications.