Why Can't I Decrypt Encrypted Data in MySQL?

Unable to Decrypt Encrypted Data in MySQL

In MySQL, the AES_ENCRYPT() function is used to encrypt data, while AES_DECRYPT() is used to decrypt the encrypted data. However, when querying encrypted data, users may encounter an issue where the decrypted data is not visible.

To resolve this issue, it's important to understand that AES_ENCRYPT() returns a binary string, while AES_DECRYPT() returns the original string. Therefore, when selecting encrypted data, the decrypted result must be explicitly cast back to a character string.

For example, consider the following table:

CREATE TABLE `user` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `first_name` VARBINARY(100) NULL,
  `address` VARBINARY(200) NOT NULL,
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

Data can be inserted into the table using the AES_ENCRYPT() function:

INSERT INTO user (first_name, address) VALUES (AES_ENCRYPT('Obama', 'usa2010'), AES_ENCRYPT('Obama', 'usa2010'));

However, when querying the encrypted data, the following query will not display the original string:

SELECT AES_DECRYPT(first_name, 'usa2010'), AES_DECRYPT(address, 'usa2010') FROM user;

Instead, the decrypted result will be a binary string that is not visible. To fix this issue, the query can be modified to cast the decrypted data back to a character string:

SELECT *,
       CAST(AES_DECRYPT(first_name, 'usa2010') AS CHAR(50)) first_name_decrypt
FROM user

The first_name_decrypt column will now contain the original string, allowing users to view their data as expected.

The above is the detailed content of Why Can't I Decrypt Encrypted Data in MySQL?. For more information, please follow other related articles on the PHP Chinese website!