MySQL中使用带有ORDER子句的AES_DECRYPT在MySQL中返回的BLOB数据 [英] BLOB data returned in MySQL using AES_DECRYPT with ORDER clause
问题描述
我正在创建一个系统,用户可以在其中使用MySQL数据库通过PHP存储消息,并且正在使用MySQL AES_ENCRYPT函数对这些消息的内容进行加密.
I'm creating a system in which users can store messages via PHP with a MySQL database, and I am using the MySQL AES_ENCRYPT function to encrypt the contents of these messages.
这是我的帖子表:
CREATE TABLE IF NOT EXISTS `posts` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user` int(11) DEFAULT NULL,
`group` int(11) DEFAULT NULL,
`body` varbinary(1000) NOT NULL,
`ip` varchar(45) NOT NULL,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`replyto` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `replyto` (`replyto`),
KEY `user` (`user`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
ALTER TABLE `posts`
ADD CONSTRAINT `posts_ibfk_3` FOREIGN KEY (`replyto`) REFERENCES `posts` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
ADD CONSTRAINT `posts_ibfk_4` FOREIGN KEY (`user`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;
和我的 users 表:
CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ip` varchar(45) NOT NULL,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`email` varchar(100) NOT NULL,
`name` varchar(100) NOT NULL,
`hash` varchar(128) NOT NULL,
`salt` varchar(32) NOT NULL,
`guid` varchar(36) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
我用于消息正文的AES加密密钥是前缀和后缀字符串之间的SHA-512哈希连接,中间是发布用户的GUID.因此,我可以使用以下SQL查询来选择最新消息:
The AES encryption key I'm using for the message body is a SHA-512-hashed concatenation between a prefix and suffix string, and the posting user's GUID in the middle. Thus, I have the following SQL query to select the most recent messages:
SELECT AES_DECRYPT(`posts`.`body`, SHA2(CONCAT('prefix',(`users`.`guid`),'suffix'),512)) AS 'realbody'
FROM `posts`
INNER JOIN `users` ON `posts`.`user` = `users`.`id`
ORDER BY `posts`.`id` DESC
不幸的是,如屏幕截图所示,它不会返回解密的消息:
Unfortunately, this does not return the decrypted messages, as you can see in the screenshot:
相反,我正在获取此BLOB数据.但是,如果我从查询中删除ORDER BY子句:
Instead, I'm getting this BLOB data. However, if I remove the ORDER BY clause from the query:
SELECT AES_DECRYPT(`posts`.`body`, SHA2(CONCAT('prefix',(`users`.`guid`),'suffix'),512)) AS 'realbody'
FROM `posts`
INNER JOIN `users` ON `posts`.`user` = `users`.`id`
然后突然,它起作用了:
Then suddenly, it works:
我真的不知道是什么原因造成的.有人有什么主意吗?
I really don't know what could be causing this. Does anybody have any ideas?
推荐答案
更新 CAST
到CHAR
SELECT `posts`.*, CAST(AES_DECRYPT(`posts`.`body`,SHA2(CONCAT('prefix',`users`.`guid`,'suffix'),512)) AS CHAR) as 'realbody'
FROM `posts` JOIN `users`
ON `posts`.`user` = `users`.`id`
ORDER BY `posts`.`id` DESC
示例输出:
| ID | USER | ... | REALBODY |
---...------------------------
| 2 | 2 | ... | Post 2 |
| 1 | 1 | ... | Post 1 |
这里是 SQLFiddle 演示
Here is SQLFiddle demo
这篇关于MySQL中使用带有ORDER子句的AES_DECRYPT在MySQL中返回的BLOB数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!