Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo

SQL Functions - SQL Encryption Functions

SQL encryption

  • Please log in to reply
8 replies to this topic

#1 chili5

chili5

    CC Mentor

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3038 posts
  • Programming Language:Java, C#, PHP, JavaScript, Ruby, Transact-SQL
  • Learning:C, Java, C++, C#, PHP, JavaScript, Ruby, Transact-SQL, Assembly, Scheme, Haskell, Others

Posted 03 September 2009 - 02:27 AM

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:

http://forum.codecal...=1&d=1251984069

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:

AES_DECRYPT(encrypted,key)

So applying the AES_DECRYPTWiki method to the result of AES_ENCRYPTWiki 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:

http://forum.codecal...=1&d=1251984069

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 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.

Example:

SELECT MD5("hiii");

Output:

14e1f4b73f7d55ecf03b55f0c46fd235



SHA1



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.

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

Hope this helps.

Attached Thumbnails

  • AES_Encrypt.JPG
  • decrypt.JPG

  • 2

#2 Guest_Jordan_*

Guest_Jordan_*
  • Guest

Posted 03 September 2009 - 04:08 AM

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

#3 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 03 September 2009 - 04:55 AM

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
  • 0

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#4 chili5

chili5

    CC Mentor

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3038 posts
  • Programming Language:Java, C#, PHP, JavaScript, Ruby, Transact-SQL
  • Learning:C, Java, C++, C#, PHP, JavaScript, Ruby, Transact-SQL, Assembly, Scheme, Haskell, Others

Posted 03 September 2009 - 05:01 AM

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.
  • 0

#5 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 03 September 2009 - 07:09 AM

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.
  • 0

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#6 BlaineSch

BlaineSch

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1559 posts

Posted 03 September 2009 - 02:47 PM

Wow your dishing out these tuts! lol

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

#7 Guest_Jordan_*

Guest_Jordan_*
  • Guest

Posted 03 September 2009 - 04:12 PM

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%. :)
  • 0

#8 relapse

relapse

    CC Addict

  • Just Joined
  • PipPipPipPipPip
  • 323 posts

Posted 04 September 2009 - 05:01 AM

** 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)
  • 0

#9 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 04 September 2009 - 08:40 AM

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 :)
  • 0

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/






Also tagged with one or more of these keywords: SQL, encryption