SQL Encryption Functions

[Shane]

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:

Code: Select all

AES_ENCRYPT(str,key)

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:

Code: Select all

ROBOT.LK

We want to encrypt a password and say my password is:

Code: Select all

cheeseFries

I would call this function:

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)

So applying the AES_DECRYPT method to the result of AES_ENCRYPT will return the original string.

Code: Select all

SELECT AES_DECRYPT(AES_ENCRYPT("eCoRe","ROBOT.LK"),"ROBOT.LK")

Output:

Code: Select all

eCoRe

Note: this only works if you know the key. Let us try changing the key even slightly.

Try this:

Code: Select all

SELECT AES_DECRYPT(AES_ENCRYPT("eCoRe","ExpertCr"),"ExpertCr")

Notice, how just slightly changing the keys used produces totally different results.


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");

Output:

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")

Output:

Code: Select all

1abedcd9967cc42ea624432d356a5f0bce7ae3a9

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-DES algorithm. I didn't go over this function because I like AES.