SQL Functions - SQL Encryption Functions

[chili5]

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:

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:

happyPepper

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

cheeseFries

I would call this function:

SELECT AES_ENCRYPT("cheeseFries","happyPepper");

The output is:



I had to use the MySQL^Wiki command prompt to show this because notice how there is a lot of really weird characters? My PHPMyAdmin^Wiki 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:

AES_DECRYPT(encrypted,key)

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

SELECT AES_DECRYPT(AES_ENCRYPT("cheeseFries","happyPepper"),"happyPepper")

Output:

cheeseFries

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

Try this:

SELECT AES_DECRYPT(AES_ENCRYPT("cheeseFries","happyPeppr"),"happyPeppr")

Output:



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^Wiki 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^Wiki digits. This function implements the message digest algorithm.

In PHP^Wiki, this function is often used to hash passwords so we are not storing plain text passwords in the database.

Example:

SELECT MD5("hiii");

Output:

14e1f4b73f7d55ecf03b55f0c46fd235

SHA1

The SHA1 function uses the Secure Hash Algorithm^Wiki. 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:

SELECT SHA1("hiii")

Output:

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

Hope this helps.

[Jordan]

Nice work, these methods come in handy for user login systems. +rep

[WingedPanther]

This is a list of functions I would slap any developer for using. Why? Because if your DB server is on a separate machine from the application, you are transmitting unencrypted data to store it in an encrypted format. That's just BAD. It should be encrypted/decrypted by the application! +rep

[chili5]

I wonder what the point of these functions are? I agree it is bad to submit them to the database then encrypt them. The time it takes your sensitive information to get to the database server, could easily be picked up by someone.

So what is the point of them?

However, with that in mind. If you are using PHP to decrypt something, then it has to be transmitted back to the browser. Couldn't some get the information after PHP has decrypted it? Thus it would make sense to decode it with a client side language?

I might be wrong about that though.

[WingedPanther]

Presumably, you get encrypted data from the server to PHP. Decrypt it via PHP and then send it (encrypted) over a secure connection to the browser.

[BlaineSch]

Wow your dishing out these tuts! lol

I never realized MySQL could do MD5/SHA1 very useful!

[Jordan]

Presumably, you get encrypted data from the server to PHP. Decrypt it via PHP and then send it (encrypted) over a secure connection to the browser.

lol, I wouldn't go as far as "slapping" someone for doing it - thats pretty mean. I think I read that 99% of websites using PHP have the database local so there is no transmission across media that can be sniffed.

For MySQL, the default connection is not secure and data is transferred in plain text so there is a security risk for transmitting the data. FTP is the same, transmitting every password in clear text yet it is still around and used on nearly every website, often the username/password being the same as the website owner. But, unless you own the data center for the site, do you realize how hard it would be to sniff anything on the Internet? Maybe I'm out of my element here and don't know what I'm talking about (wouldn't be the first time) but it seems like there are too many packets floating around in cyberspace for this to be considered a viable threat. Else, I would imagine FTP would be abolished (I suppose telnet was though).

This is all in light of bad security measures. This would be assuming I've granted the user of said database access to the database from any IP (or even unblocked the port from my firewall if local) once they even have the password.

However, I agree with you 100%.

[relapse]

Hell yeah it is effing possible. Police do it all the time to bust drug deals and **** like that. Packet Sniffing - Part 1 (wiretaps, protocol decoding and surveillance)

[WingedPanther]

Actually, you can telnet into SQL Server if you specify the port. telnet is useful for testing if a computer is listening on a particular port