In response to your question, the main reason is I didn't know those existed. I suppose now that I do, a second reason would be questions of compatibility. For example, can these functions be used in a shared hosting environment, do they work uniformly on all recent versions of MySQL, and is there a Postgres equivalent? We don't officially support Postgres, but we've been including contributed support from community users who use Postgres themselves.
I'd appreciate any insights you have.
Ryan,
I'll try to answer....
The functions work fine in shared hosting, in fact encrypting is more important there to protect your database against users on the same box.
AES_DECRYPT() and AES_ENCRYPT() have been MySQL functions since 4.x and they are still in ver 6.0 so I don't think they are going away. The docs don't mention any compatibility issues and I haven't seen any in my limited tested.
The MySQL docs are here:
http://dev.mysql.com/doc/refman/4.1/en/encryption-functions.html
I don't work in Postgres so I can't answer your question about that.
In implementing this protecting the passphrase is crucial. I suggest putting the passphrase in a text file out of the server's path (say ~/password.txt) and chmod it so only the store owner can read/write it. Then you can read the file into a variable as needed to encrypt and decrypt.
Note that the encrypted cc column type must be VARBINARY or BLOB, not STRING
A more secure option is to use GPG (open source PGP clone) to encrypt the data. Because this doesn't require storing a passphrase on the server it even protects you against a malicious root user. The downside is that GPG is a little harder to implement and doesn't integrate directly with MySQL.
I hope this helps,
Bob
Joined: 06/03/2008