Re: ENCODE() Encryption Algorithm
I assume you're going through a PCI audit.. good luck with that! I've been trough a PCI audit every year for the last 3 years and have encountered this question many times.
The ENCODE and DECODE MySQL functions are very low level encryption methods that do not use specific published algorithms (i.e. AES256, AES128, DES, RSA, etc). MySQL docs don't specifically state the algorithm used because I suspect A) they are using a proprietary method to encrypt and B) they don't want you to use this function to "encrypt" really sensitive data (i.e. credit card numbers).
I will tell you right now, you will NOT pass a PCI audit if you use ENCODE and DECODE to store credit card numbers, even if it is temporary.
So what are you to do? You can use either the (AES_ENCRYPT and AES_DECRYPT) or (DES_ENCRYPT and DES_DECRYPT) functions which obviously use the mentioned algorithms. Our auditor didn't really like these methods either because MySQL doesn't publish the level of encryption these functions use (i.e. 128, 256, etc). You "might" be able to get around this depending on your auditor but you are taking a risk.
Here is what I suggest instead...
Encrypt your card numbers in code BEFORE sending them to MySQL. For example if you are using Perl or PHP... when the card number is submitted (say from a web form) to your code, have you code encrypt the card number. Perl and PHP both have many modules available to do this. Once the card number is encrypted, you can then pass the value to MySQL and store it in a binary column. The good thing about doing it this way, if someone sniffs the packet being sent from your code to MySQL, the card number will be encrypted in the packet. If you were to say use AES_ENCRYPT with MySQL, you would be passing the card number unencrypted to the database. Sure it's encrypted once you store it... but what happens if someone captures the packet BEFORE it hits MySQL?
From my experience, DO NOT use MySQL's built in functions to encrypt credit card numbers. MySQL is a wonderful database engine, but when it comes to encryption software (i.e. Perl, PHP, etc) is your best friend.
Hope this helps, good luck!