SQL Encryption Functions
When you want to store really sensitive data in a database, you will want to encrypt it. This is especially important on web sites with passwords and credit card numbers. You wouldn't want a bad person looking at all your passwords would you?
There are two ways of securing data in a database: hashing and encryption. Hashing usually prevents you from obtaining the original string. Encryption allows you to obtain the original string (through decrypting). This is only possible if you know the encryption method, and the key that was used to encrypt it.
The encryption functions return a binary string, so you should use a binary column to store it. Ex: VARBINARY or BLOB. If you use things like VARCHAR, you might run into problems with a character set changing values. Making it impossible to get your data back.
Advanced Encryption Algorithm (AES)
The first algorithm, we are going to look at is the AES algorithm. The function prototype is:
With encryption functions we need a key to encrypt the string. This is something that only you should know. On our web site, we may ask the user to input a key that is used to encrypt their password or some other information. As long as they don't give out the key, then all is fine. Another thing we could do is use a really complicated string as a key, and this would allow us to retrieve passwords from the database.
Let us use this below as our key:
We want to encrypt a password and say my password is:
I would call this function:
The output is:
I had to use the MySQLWiki command prompt to show this because notice how there is a lot of really weird characters? My PHPMyAdminWiki software ran into problems displaying the characters.
Advanced Encryption Algorithm - The Decryption Method
As long as we know the encrypted string, and the key we can get the original text back.
The syntax for this method is:
So applying the AES_DECRYPTWiki method to the result of AES_ENCRYPTWiki will return the original string.
Note: this only works if you know the key. Let us try changing the key even slightly.
Notice, how just slightly changing the keys used produces totally different results.
An alternative to encryption is hashing functions. The difference being with hash functions you can't get the plain text back. Two hash algorithms are md5 and sha1.
The MD5Wiki function returns a binary string of 32 hex digits. One thing you might want to do with this function is use it as a key for the AES encryption function. It is pretty hard to guess a string of 32 hexadecimalWiki digits. This function implements the message digest algorithm.
In PHPWiki, this function is often used to hash passwords so we are not storing plain text passwords in the database.
The SHA1 function uses the Secure Hash AlgorithmWiki. Given a string as a parameter, it returns a binary string of 40 digits. This is also used for storing passwords in a database and storing other sensitive information.
The return values here are going to be a lot larger than the return values of MD5.
This value also makes a good key to use for encryption functions. Note, there is another encryption function called DES. This function uses the Triple-DESWiki algorithm. I didn't go over this function because I like AES.
Hope this helps.