title: convert php mcrypt_encrypt and decrypt to mysql function
body: <p>I was encrypting and decrypting data using php <code>mcrypt_encrypt</code> and <code>mcrypt_decrypt()</code> with the following arguments: </p>
  
  <ul>
  <li>$cipher : <code>MCRYPT_RIJNDAEL_256</code></li>
  <li>$key : 32 bit hash sha1, generated as follow <code>substr(hash("sha1", $some_var),0,32)</code></li>
  <li>$data : can be number or string</li>
  <li>$mode : <code>MCRYPT_MODE_CBC</code></li>
  <li>$iv : 32 bit hash sha1 generated as follow <code>substr(hash("sha1", $other_var),0,32)</code></li>
  </ul>
  
  <p>Then the generated string is passed to <code>base64_encode</code> and the result is the encrypted data. 
  The final function look something like: </p>
  
  <pre><code>function encrypt($data, $key1, $key2) {
      return
              trim( base64_encode( mcrypt_encrypt(
                      MCRYPT_RIJNDAEL_256,
                      substr($key1,0,32),
                      $data,
                      MCRYPT_MODE_CBC,
                      substr($key2,0,32)
              )));
      }
  </code></pre>
  
  <p>i want to do the same thing using <code>mysql</code>, so i can decrypt data while selecting them. 
  I want to write <strong>mysql function</strong> that encrypt my data the same as the php <code>encrypt</code> function above. </p>
  
  <p>I searched mysql documentation and found that <a href="https://dev.mysql.com/doc/refman/5.6/en/encryption-functions.html#function_aes-encrypt" rel="nofollow"><code>AES_ENCRYPT</code></a> can do the same thing as <code>mcrypt_encrypt</code> and  <a href="http://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_to-base64" rel="nofollow"><code>TO_BASE64(str)</code></a> can do the function of <code>base64_encode</code> </p>
  
  <pre><code>CREATE DEFINER=`root`@`localhost` FUNCTION `mcrypt_encrypt`(
      _data text,
      _key1 varchar(40),
      _key2 varchar(40)  
  ) RETURNS varchar(40) CHARSET utf8
  BEGIN
      DECLARE encryData varchar(40);        
      SET @@session.block_encryption_mode = 'aes-256-cbc';
      SET encryData = AES_ENCRYPT(
          _data,
          SUBSTRING(key1,1,32),
          SUBSTRING(key2,1,32)
      );
  
  RETURN TO_BASE64(encryData);
  
  END
  </code></pre>
  
  <p>but it not working as the php function. </p>
  
  <p>the output of <code>echo encrypt('abcdefg', $key1, $key2);</code> where </p>
  
  <ul>
  <li><code>$key1</code> is '5c2787d529149d3016ce50451290df1779ec93c7'</li>
  <li>and <code>$key2</code> is '33db3475db4badb7f6ba86489257c873610e9298'
  is 'FO51NxIIdOO/UQYtCTGhf2O0eq52C0AnRNTr9CxdOZc='</li>
  </ul>
  
  <p>while the output of </p>
  
  <pre><code>Select mcrypt_encrypt(
      'abcdefg',
      '5c2787d529149d3016ce50451290df1779ec93c7',
      '33db3475db4badb7f6ba86489257c873610e9298'
  ) as a
  </code></pre>
  
  <p>is 'FQ=='</p>
  
  <p>i use:  </p>
  
  <ul>
  <li>XAMPP for Linux 5.6.8-0!</li>
  <li>PHP Version 5.6.8</li>
  <li>MySQL 5.6.24 - Source distribution</li>
  <li>i disable case sensitivity in mysql</li>
  </ul>