If you would like to know more about encryption there are two resources I would recommend:
- The Code Book by Simon Singh. This is about the history of cryptography, but it also includes a lot of information about crypto which is currently in use. This is also a very entertaining read.
- Crypto 101, a free/opensource book which gives a intro to crypto. The webpage also has a video of the talk on which the book is based.
- Connections with a UNIX socket (or shared memory on Windows) should be safe. Local TCP/IP connections are also safe in most circumstances.
- Remote connections which use the AES encryption should use a protected connection (SSL, VPN, etc). Otherwise someone might be able to sniff the network traffice which contains your encryption key.
- Replication traffic should also be protected (especially if you're using statement based replication. Mixed or row based might be safer).
- There is a trick to prevent the key to be logged:
- SET @encrypted := AES_ENCRYPT('foo', 'bar');
- INSERT INTO t1(c1) VALUES(@encrypted);
- MySQL 5.6 prevents logging of passwords, but this doesn't include the AES functions (yet). (Bug #72158). So be careful with your logging.
- It is possible to use the AES_DECRYPT in a view definition, but then your probably storing your key with your data, which defeats the purpose. The same is true for storing your key in a stored procedure, function or a virtual column in case of MariaDB.
The AES functions in MySQL are a good solution if you only want to encrypt specific data (which is often the case) or if you need to search on the decrypted data (WHERE AES_DECRYPT(..)='foo')
