Let's look at the flow of data; you decide whether such encryption is worth anything:
Case 1: client and server are on same machine; using socket for communication:
1. INSERT ... aes_encrypt(..., 'plaintext key')
2. that INSERT statement is sent unencrypted thru socket
3. mysqld performs encryption, and stores encrypted data on disk.
4... decryption is pretty much the reverse (SELECT, unencrypted transfer, check in client)
Case 2: client and server are on different machines; using TCP/IP, but no SSL for communication:
1. INSERT ... aes_encrypt(..., 'plaintext key')
2. that statement is sent unencrypted thru across network -- any network sniffer and the NSA can easily see the key.
3. mysqld performs encryption, and stores encrypted data on disk.
4... decryption is pretty much the reverse.
Case 3: client and server are on different machines; using TCP/IP with SSL (Heartbleed notwithstanding) for communication:
1. INSERT ... aes_encrypt(..., 'plaintext key')
2. that statement is sent unencrypted thru across network -- the key is very secure, thanks to SSL
3. mysqld performs encryption, and stores encrypted data on disk.
4... decryption is pretty much the reverse.
The points:
* The key is sent to the server as plaintext; it is up to the connection to make it secure.
* The key is sitting in plaintext in the source code. If this is some webserver CGI (eg, PHP), then the question is how secure the source is. Note that it is all too easy to expose PHP code to the outside world via a webserver.
Case 4: This attempts to hide the key behind "root" on a unix system:
* When web server (eg, Apache) starts up, it starts as root. At this point it can load a .so and fetch a file that is visible only to root (chmod 400, chown root, chgrp root) that contains the key. Within the webserver, there would be an API to fetch the key. Now the key is available only to a hacker that read all of RAM; this is reasonably secure.
* Passing the key between client and server is still a vulnerability (as discussed above).
Is it perfect? No. Is it the best available? Perhaps.
Your use case involved needing to decrypt. The "password" use case never needs decryption. It works like this, which is inherently safer:
* Storing: $salt = 'xyz'; $trapdoor = MD5($pwd . $salt); INSERT ... ($trapdoor);
* Checking: $trapdoor = SELECT ...; if (trapdoor != MD5($pwd . $salt)) echo "Bad pwd";
Notes:
* The user-entered pwd ($pwd) is never sent to the server (TCP snooping not directly useful)
* The $salt is probably exposed, but at least it prevents a "dictionary attack" by anyone that can read the encrypted pwd directly from the database.
* Again, hacking into RAM can get at things.
* The likely security breech requires the hacker to do two things, and the users to be sloppy:
** Finding the salt
** Being able to read the database (see "SQL Injection")
** Weak passwords (hence 'dictionary attack' with the salt)
Would anyone care to improve on what I said?
The final word is not in yet on the OpenSSL vulnerability code named Heartblead, but his may be a good summary, especially as it relates to MySQL:
http://www.mysqlperformanceblog.com/2014/04/09/heartbleed-separating-faq-from-fud/