BLOB data returned in MySQL using AES_DECRYPT with ORDER clause

aes, blob, database, mysql, sql-order-by

Solution

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 SQLFiddle demo

Problem

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?

Original source