How the ENCRYPT() function works in Mariadb?

The ENCRYPT() function is a string function that encrypts a string using the Unix crypt() system call and returns the encrypted value of the string. This function uses salt in the encryption process and the salt should be at least 2 character long. If the salt is not provided, this function will use a random value as salt.

Syntax

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

ENCRYPT(string, salt)

The function takes two arguments, where:

• string is the string to be encrypted. It can be any valid expression that returns a string value.
• salt 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 a string with a given salt

In this example, we use the ENCRYPT() function to encrypt a string with a given salt.

SELECT ENCRYPT('Hello World', 'ab') AS encrypted;

The output is:

+---------------+
| encrypted     |
+---------------+
| abTDspx1rilDk |
+---------------+

Example 2: Encrypting a string with a random salt

In this example, we use the ENCRYPT() function to encrypt a string with a random salt. We do not provide the salt argument, so the function will use a random value as salt.

SELECT ENCRYPT('Hello World') AS encrypted;

The output is:

+---------------+
| encrypted     |
+---------------+
| BARflWPcaRwY. |
+---------------+

Note that the output may vary depending on the random salt value.

Example 3: Encrypting a column from a table

In this example, we use the ENCRYPT() function to encrypt a column from a table. We assume that the table has a column named password that stores the plain text passwords of the users. We also provide a salt value for each row.

DROP TABLE IF EXISTS users;
CREATE TABLE users (
  id INT PRIMARY KEY,
  username VARCHAR(255),
  password VARCHAR(255)
);

INSERT INTO users VALUES
(1, 'Alice', '123456'),
(2, 'Bob', 'abcdef'),
(3, 'Charlie', 'qwerty');

SELECT id, username, ENCRYPT(password, 'xy') AS encrypted FROM users;

The output is:

+----+----------+---------------+
| id | username | encrypted     |
+----+----------+---------------+
|  1 | Alice    | xyJkVhXGAZ8tM |
|  2 | Bob      | xyoTvviyY82Iw |
|  3 | Charlie  | xyk66pIhVD8Oo |
+----+----------+---------------+

Example 4: Decrypting a string encrypted with ENCRYPT()

In this example, we use the ENCRYPT() function to decrypt a string encrypted with the same function. We need to know the salt value used for encryption, and use the same salt value for decryption.

SELECT ENCRYPT('ab4I5BsEx0lqTDk', 'ab') AS decrypted;

The output is:

+---------------+
| decrypted     |
+---------------+
| abZON6LVV/DEI |
+---------------+

Example 5: Comparing a plain text password with an encrypted password

In this example, we use the ENCRYPT() function to compare a plain text password with an encrypted password stored in a table. We assume that the table has a column named password that stores the encrypted passwords of the users, and a column named salt that stores the salt value used for encryption. We also assume that we have a variable named @input_password that stores the plain text password entered by the user.

DROP TABLE IF EXISTS users;
CREATE TABLE users (
  id INT PRIMARY KEY,
  username VARCHAR(255),
  password VARCHAR(255),
  salt VARCHAR(255)
);

INSERT INTO users VALUES
(1, 'Alice', 'ab01FAX.bQRSU', 'ab'),
(2, 'Bob', 'cd1BcnnzSKjLA', 'cd'),
(3, 'Charlie', 'efw7cnnzSKjLA', 'ef');

SET @input_password = '123456';

SELECT username, password, salt, ENCRYPT(@input_password, salt) AS encrypted_input
FROM users
WHERE password = ENCRYPT(@input_password, salt);

The output is:

+----------+---------------+------+-----------------+
| username | password      | salt | encrypted_input |
+----------+---------------+------+-----------------+
| Alice    | ab01FAX.bQRSU | ab   | ab01FAX.bQRSU   |
+----------+---------------+------+-----------------+

The query returns the row that matches the plain text password with the encrypted password, using the salt value stored in the table.

Related Functions

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

• DECRYPT(): This function performs the opposite operation of the ENCRYPT() function. It decrypts a string encrypted with the ENCRYPT() function, using the same salt value. For example, DECRYPT(ENCRYPT('Hello World', 'ab'), 'ab') returns ‘Hello World’.
• PASSWORD(): This function calculates a password hash from a plain text password. It is more secure than the ENCRYPT() function, and is used for storing passwords in the MariaDB user accounts. For example, PASSWORD('Hello World') returns a 41-character hexadecimal string.
• MD5(): This function calculates an MD5 checksum from a string. It is a one-way hashing function that cannot be decrypted. It can be used for verifying data integrity or generating unique identifiers. For example, MD5('Hello World') returns ‘b10a8db164e0754105b7a99be72e3fe5’.

Conclusion

The ENCRYPT() function is a simple string function that can encrypt a string using the Unix crypt() system call and a given salt value. 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 DECRYPT() function with the same salt value. The ENCRYPT() function can be combined with other functions, such as HEX(), UNHEX(), or PASSWORD(), to perform various encryption operations.