title: mysql aes_encrypt into longtext column
body: <p>Is it possible to store a MySQL AES_ENCRYPT into a LONGTEXT column?</p>
  
  <p>I know I'm <em>suppose</em> to use varbinary or blob, but I have a table that I'm storing a bunch of random "settings" in, and the settings_value column is longtext.</p>
  
  <p>I went to store a "smtp mail password" in there, and got a little stuck.</p>
  
  <p>If not, I guess, I'll store it as a hex string through php.</p>
  
  <p><strong>SOLUTION:</strong></p>
  
  <p>My query was something like this:</p>
  
  <pre><code>INSERT INTO table (setting_value)VALUES(AES_ENCRYPT('password', 'key')) ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value)
  </code></pre>
  
  <p>As you will see in my comments below, I tried changing my column encoding from utf8_unicode_ci to utf8_bin and still it failed.  I changed to latin1_bin and it worked.</p>
  
  <p>I switched back to utf8_unicode_ci and changed my query to the following:</p>
  
  <pre><code>INSERT INTO table (setting_value)VALUES(HEX(AES_ENCRYPT('password', 'key'))) ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value)
  </code></pre>
  
  <p>That worked since it just turned my value into a hex string.</p>
  
  <p>Took me a second to figure out how to get the value back out correctly, so for documentation purposes:</p>
  
  <pre><code>$pass = SELECT AES_DECRYPT(BINARY(UNHEX(setting_value)), 'key') as orig_text FROM table
  echo $pass-&gt;orig_text
  </code></pre>