java aes mysql blob,使用带有ORDER子句的AES_DECRYPT在MySQL中返回的BLOB数据

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.

Here is my posts table:

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;

And my users table:

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 ;

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:

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?

解决方案

UPDATED CAST it to 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

Sample output:

| ID | USER | ... | REALBODY |

---...------------------------

| 2 | 2 | ... | Post 2 |

| 1 | 1 | ... | Post 1 |

Here is