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:
Code: Select all
AES_ENCRYPT(str,key)
Let us use this below as our key:
Code: Select all
ROBOT.LK
Code: Select all
cheeseFries
Code: Select all
SELECT AES_ENCRYPT("cheeseFries","ROBOT.LK");
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:
Code: Select all
AES_DECRYPT(encrypted,key)
Code: Select all
SELECT AES_DECRYPT(AES_ENCRYPT("eCoRe","ROBOT.LK"),"ROBOT.LK")
Code: Select all
eCoRe
Try this:
Code: Select all
SELECT AES_DECRYPT(AES_ENCRYPT("eCoRe","ExpertCr"),"ExpertCr")
Hashing Methods
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.
MD5
The MD5 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 hexadecimal digits. This function implements the message digest algorithm.
In PHP, this function is often used to hash passwords so we are not storing plain text passwords in the database.
Example:
Code: Select all
SELECT MD5("hiii");
Code: Select all
14e1f4b73f7d55ecf03b55f0c46fd235
SHA1
The SHA1 function uses the Secure Hash Algorithm. 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.
Example:
Code: Select all
SELECT SHA1("hiii")
Code: Select all
1abedcd9967cc42ea624432d356a5f0bce7ae3a9