title: BLOB data returned in MySQL using AES_DECRYPT with ORDER clause
body: <p>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.</p>
  
  <p>Here is my <em>posts</em> table:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>And my <em>users</em> table:</p>
  
  <pre><code>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 ;
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Unfortunately, this does not return the decrypted messages, as you can see in the screenshot: </p>
  
  <p><img src="https://i.stack.imgur.com/mLnnQ.png" alt="https://i.imgur.com/N1B1iqR.png"></p>
  
  <p>Instead, I'm getting this BLOB data. However, if I remove the ORDER BY clause from the query:</p>
  
  <pre><code>SELECT AES_DECRYPT(`posts`.`body`, SHA2(CONCAT('prefix',(`users`.`guid`),'suffix'),512)) AS 'realbody'
  FROM `posts`
  INNER JOIN `users` ON `posts`.`user` = `users`.`id` 
  </code></pre>
  
  <p>Then suddenly, it works: </p>
  
  <p><img src="https://i.stack.imgur.com/Pny6G.png" alt="https://i.imgur.com/6ArQPHN.png"></p>
  
  <p>I really don't know what could be causing this. Does anybody have any ideas?</p>