title: MySQL/SQL Server vs. PHP encryption
body: <p>According to documentation, the most-secured encryption methods are:</p>
  
  <p><code>AES_ENCRYPT()</code> for MySQL</p>
  
  <p><code>ENCRYPTBYPASSPHRASE()</code> for SQL Server</p>
  
  <p><code>MC_ENCRYPT()</code> for PHP</p>
  
  <p>Which one should be used?</p>
  
  <p>Recently on many blogs and e-magazines is being posted information, that pre-encryption with PHP is the best way, since if the My/SQL server is compromised, the attacker can scan the logs.</p>
  
  <p>Example of mcrypt pre-encrypting the string before database insertion (vs the others):</p>
  
  <pre><code>&lt;?php
  define('ENCRYPTION_KEY', '555d6c18e7b8aa109bfda854df942088a9984cccf2a979bd21b99e50aedc1976');
  
  function Cryp($action, $string, $key) {
      $key = pack('H*', $key);
  
      if($action == 'en') {
          $string = serialize($string);
          $iv = mcrypt_create_iv(mcrypt_get_iv_size(MCRYPT_RIJNDAEL_256, MCRYPT_MODE_CBC), MCRYPT_DEV_URANDOM);
          $string = base64_encode(mcrypt_encrypt(MCRYPT_RIJNDAEL_256, $key, $string . hash_hmac('sha256', $string, substr(bin2hex($key), -32)), MCRYPT_MODE_CBC, $iv)) . '|' . base64_encode($iv);
      }
  
      if($action == 'de') {
          $string = explode('|', $string);
          $iv = base64_decode($string[1]);
          $string = unserialize(substr(trim(mcrypt_decrypt(MCRYPT_RIJNDAEL_256, $key, base64_decode($string[0]), MCRYPT_MODE_CBC, $iv)), 0, -64));
      }
  
      return $string;
  }
  
  $data = 'Sample string here';
  
  $encrypted = Cryp('en', $data, ENCRYPTION_KEY);
  $decrypted = Cryp('de', $encrypted, ENCRYPTION_KEY);
  
  echo $encrypted;
  ?&gt;
  </code></pre>