Building a web app with encrypted MySQL database entries?

Question

I have some experience in building PHP based websites with MySQL access, storing encrypted user details, but all other fields being plain text. My most recent project will require sensitive data to be stored in the database. All of which I'm hosting myself.

I want to set up a system where a user can access his own entries and see the plain text results, but even if he was able to access someone else's they would be encrypted, unintelligible strings.

I have an idea of how to accomplish this, but perhaps it's not optimal or there exist tools to do this already in a more efficient way.

1. Store username as plain text, and password encrypted with sha1(). Or both encrypted with sha1().
2. Take the user's password (not the encrypted one, but the one he typed in and use it do define a key specific to that username and password, which will then be stored as a session variable.
3. Encrypt, or decrypt all of that users data with that key.

In my opinion even if someone gained access to the database and saw a list of plain text usernames and encrypted passwords they couldn't figure out the specific key since it's not stored anywhere. Therefore even if access was gained, they couldn't decipher the content of the sensitive database fields. Of course I'm building in ways to stop them accessing the database anyway, but as a catch-all effort this seems like a good set of steps.

Could the experts please comment on this, and offer some advice? Thanks a lot.

Answer 1

You can certainly encrypt each user's data in the table itself, but do so only after recognizing the downside. If you encrypt the data, your search options will be limited. You wont, for example, be able to do searches such as name like 'Bob%' or cost > 1000 if the entries are encrypted. Similarly, you could use multiple databases - one per user (or even one table per user), but you'll start running into problems keeping the schemas in sync should your app ever grow and evolve.

Ultimately, schemes like that may not be as secure as you think they are. They still wont prevent, for example, the primary way people access other people's data... by simply using their computer or guessing their password.

I believe, quite honestly, your question is being asked out of inexperience with handling this kind of data.

My advice to you is to resist the urge to use security gimmicks. Design the database and tables as you normally would. And then put all the effort you would have spent encrypting the data into ensuring your server side application is secure. Make sure every request properly verifies user authorization to the data being requested. Make sure you have no possible injection issues. Use ssl for the connections. Make sure the proper firewalls are running on the server.

Answer 2

According to MySQL, AES encryption (Advanced Encryption Standard) is the best method available for providing reversible encryption and decryption in SQL.

Formerly known as Rijndael, the AES_ENCRYPT and AES_DECRYPT functions are now built-in to MySQL so you can take user data, encrypt it with a salt, store it in your database, then extract it again later and decrypt it.

Define your salt

You'll need to apply a salt to the data that you encrypt. This is a special code that the encryption algorithm uses which works a bit like a key.

You'll need to provide the exact same key back to decrypt the data, and if an attacker should gain access to your database, they won't be able to decipher it without knowing the salt.

If you define your salt in PHP like this, you'll be able to pull the constant into your SQL statements more easily.

if(!define('SALT')) define('SALT','897sdn9j98u98jk');

To insert data into your MySQL database and encrypt the sensitive information, you'll need to issue a command like this, along with your salt.

INSERT INTO your_table (username,email,shoe size) VALUES ('$username', AES_ENCRYPT('$email','".SALT."'), AES_ENCRYPT('$shoesize','".SALT."'));

This will insert the username in plain text, as it's non-sensitive, but encrypt the user's email and shoesize, to prevent them from being viewed without access to the salt.

At some point, you're going to need to access some of the data you stored in its encrypted form, and you can do this very easily using the AES_DECRYPT function of MySQL and the same salt you used when you encrypted the data and inserted it.

SELECT username, AES_DECRYPT('email','".SALT."') AS email, AES_DECRYPT('shoesize','".SALT."') AS shoesize FROM your_table WHERE username ='fred';

If you SELECT the encrypted data without running it through AES_DECRYPT or with the wrong or no salt, you'll get an ugly, unreadable string of odd characters. This means if an attacker manages to access your database, but does not have access to your server to view the salt, they won't be able to read any of the data you've stored. At least, not without going to great lengths to try and decrypt the data.

Updating encrypted records is very similar to insertion. Basically, you just apply the same salt and re-issue the AES_ENCRYPT command to re-encrypt the data again and lock it away safely.

UPDATE your_table SET email = AES_ENCRYPT('$email','".SALT."'), shoesize = AES_ENCRYPT('$shoesize','".SALT."') WHERE username= 'fred';

Searching encrypted data using both AES_ENCRYPT and AES_DECRYPT

Things get a little bit more complicated when you need to search for data that's encrypted and then display it in its unencrypted form.

Say you wanted to search for a user using their email address, but you'd encrypted that in the database. First, you'd need to encrypt the email address you want to search for with AES_ENCRYPT and your salt, and then you'd need to use AES_DECRYPT to ensure that MySQL decrypted it, returning it in a readable format.

You can achieve this, using code a bit like this:

SELECT user_username, AES_DECRYPT(email,'".SALT."') AS email, AES_DECRYPT(shoesize,'".SALT."') AS shoesize FROM your_table WHERE (email = AES_ENCRYPT('$q','".SALT."'));

For further informations, please see this link: http://dev.mysql.com/doc/refman/5.1/en/encryption-functions.html

Answer 3

To encrypt & decrypt mysql data we will use AES_ENCRYPT() and AES_DECRYPT() functions. These functions used the official AES (Advanced Encryption Standard) algorithm & encode data with a 128-bit key length. 128 bits is much faster and secure enough for most purposes.

Because AES_ENCRYPT() encrypts a string and returns a binary string. AES_DECRYPT() decrypts the encrypted string and returns the original string.

AES is a block-level algorithm. So when data encrypted it is padded. So you can calculate the length of the result string using this formula:

1.)16 × (trunc(string_length / 16) + 1)

So if your address field structure is = VARCHAR(100) ; //100 length of varchar Then before your encryption it should be converted

= 16 * (trunc(100/ 16) + 1) = 16 * (6.25 + 1) = 16 * 7.25 = 116 

So VARCHAR(100) should be converted to VARBINARY(116) minimum. I suggest use little more like VARBINARY(150) in this case.

Because if AES_DECRYPT() detects invalid data or incorrect padding, it will return NULL. But it is also possible for AES_DECRYPT()to return a non-NULL value (possibly garbage) if the input data or the key is invalid.

Syntax:

1.)AES_ENCRYPT(str, key_str);

2.)AES_DECRYPT(crypt_str,key_str);

Please remember, the encryption and decryption will occur based on a key. So you’ve to keep that key in a secret place and using variable you could pass the key to mysql to encrypt and decrypt data.

AES_ENCRYPT() and AES_DECRYPT() can be considered the most cryptographically secure encryption functions currently available in MySQL.

Inserting data as follows:

INSERT into user (first_name, address) VALUES (AES_ENCRYPT('Obama', 'usa2010'),AES_ENCRYPT('Obama', 'usa2010')); 

Retrieving data as follows:

SELECT AES_DECRYPT(first_name, 'usa2010'), AES_DECRYPT(address, 'usa2010') from user; 

additional notations:

You'll need to apply a salt to the data that you encrypt. This is a special code that the encryption algorithm uses which works a bit like a key.

You'll need to provide the exact same key back to decrypt the data, and if an attacker should gain access to your database, they won't be able to decipher it without knowing the salt.

If you define your salt in PHP like this, you'll be able to pull the constant into your SQL statements more easily.

Answer 4

Sha1() is not an encryption function, it is a hashing function. This means that it is one way. You can encode a string using sha1() and it will produce a hash but you can't go from hash to string.

Second, a password should ALWAYS be stored as a hash and never as plain text. But ofcourse the security depends on the strength of the hash, and sha1() isnt the best you can use. It is out dated and shouldn't be use for passwords. Use blowfish or pbkdf2 or similar to store the passwords and use a different random Salt for every user.

Then as ott allready commented. You could use the mysql AES_Encrypt and Decrypt functions. But then store the encryption string somewhere safe. For instance on the client-side, but then you must use SSL. Or as a session in php, but then it is stored as plain text in a file on the filesystem.

Or you could send the encrypted data encrypted to the client and decrypt it on client side. Then when sending data you encrypt it on client side an store the encrypted file in the database. I'm however not that at home in encryption with javascript.

Answer 5

In one project, we use digital certificates to do the encryption.

The encryption/decryption are done on the client side and require the use of an activex component or a java applet.

Of course, this may be overkill for your purpose.