mysql 中 AES_ENCRYPT 后无法 AES_DECRYPT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16556375/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Unable to AES_DECRYPT after AES_ENCRYPT in mysql
提问by PSR
I created user 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;
I inserted one row:
我插入了一行：
INSERT into user (first_name, address) VALUES (AES_ENCRYPT('Obama', 'usa2010'),AES_ENCRYPT('Obama', 'usa2010'));
To select this row i used:
要选择这一行，我使用了：
SELECT AES_DECRYPT(first_name, 'usa2010'), AES_DECRYPT(address, 'usa2010') from user;
I am getting the following result.What i need to do see my data.No data is visible for me.
我得到以下结果。我需要做什么才能看到我的数据。我看不到任何数据。
回答by John Woo
According to the Manual:
根据手册：
AES_ENCRYPT() encrypts a string and returns a binary string. AES_DECRYPT() decrypts the encrypted string and returns the original string.
AES_ENCRYPT() 加密一个字符串并返回一个二进制字符串。 AES_DECRYPT() 解密加密字符串并返回原始字符串。
I don't know why it is still returning a binary string in your case. Anyway, try this:
我不知道为什么在你的情况下它仍然返回一个二进制字符串。无论如何，试试这个：
SELECT *,
CAST(AES_DECRYPT(first_name, 'usa2010') AS CHAR(50)) first_name_decrypt
FROM user
And use first_name_decryptinstead of first_name.
并使用first_name_decrypt代替first_name.
回答by Viktor
From mysql command line client there is no need to use CAST:
从 mysql 命令行客户端无需使用CAST：
mysql> SELECT AES_DECRYPT(AES_ENCRYPT('admin','abc'),'abc');
+-----------------------------------------------+
| AES_DECRYPT(AES_ENCRYPT('admin','abc'),'abc') |
+-----------------------------------------------+
| admin |
+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT CAST(AES_DECRYPT(AES_ENCRYPT('admin','abc'),'abc') AS CHAR (50));
+------------------------------------------------------------------+
| CAST(AES_DECRYPT(AES_ENCRYPT('admin','abc'),'abc') AS CHAR (50)) |
+------------------------------------------------------------------+
| admin |
+------------------------------------------------------------------+
1 row in set (0.02 sec)
As you can see using cast in command line is little bit slower. But I have noticed that if you use some tools like phpmyadmin, then you need to use CAST, otherwise result will be wrong.
正如您所看到的，在命令行中使用 cast 会慢一点。但是我注意到，如果你使用一些像phpmyadmin这样的工具，那么你需要使用CAST，否则结果将是错误的。
回答by Dhivya
if (isset($_POST['user_name']) and isset($_POST['user_password'])){
$user_name = $_POST['user_name'];
$user_password = $_POST['user_password'];
$query = "SELECT * FROM `user_tbl` WHERE user_name='$user_name' and AES_DECRYPT(user_password , '@ert') = '$user_password'";
$result = mysqli_query($connection, $query) or die(mysqli_error($connection));
$count = mysqli_num_rows($result);
