Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question
    
You could use the mysql functions AES_ENCRYPT() and AES_DECRYPT() for each column, but where to keep the key-string? See dev.mysql.com/doc/refman/5.5/en/encryption-functions.html for an overview. –  ott-- Jul 16 '13 at 12:58
    
In this case a seperate database per user seems like easier security? Then you have one point to secure and validate database access. –  Luc Franken Jul 16 '13 at 13:16
2  
As a relative expert my initial analysis of this is STOP NOW. You should probably move this to Security.SE, where they can explain in more detail why rolling your own security is A Bad Idea (tm). –  James Snell Jul 16 '13 at 13:32
    
@JamesSnell Thanks, I've posted there. –  Joey O Jul 16 '13 at 13:42
2  
@LucFranken multiple databases is a horrendous idea and really doesn't offer any additional security. The security point is going to be the layer above the database. –  Bill Leeper Jul 16 '13 at 16:03

5 Answers 5

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.

share|improve this answer

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

share|improve this answer
    
Yes, this is also correct, however you may want to consider a little less complex approach. On your provided link, it says: "As noted in Bug #16713 (AES_Encrypt / AES_Decrypt functions is low documented), the full specs of the encryption function are not given." –  Adromil Balais Jan 30 at 19:12

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.

share|improve this answer

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.

share|improve this answer
    
Thanks for your replies, I'm aware sha1() is one-way, but I only need one-way for passwords. Thanks for pointing out the better alternatives for hashing. Apart from leaving a temporary file on a user's computer, would there be any other drawbacks from using a session variable to store the unique user's key? –  Joey O Jul 16 '13 at 13:23
1  
@JamesSnell did you read my answer? "... use a different random Salt for every user." –  Pinoniq Jul 16 '13 at 13:30
    
@JoeyO Storing the user's key in a Session sets a SessionId coockie on the users computer and creates a Session file on the server. With the sessionId coockie the client get's access to that session file and it's values. So the strength of your security then depends on the security of the sessionID generation. And don't forget all the MITM, CSRF, ... atacks. So as you can see, there is no really good answer. there will always be a weak link –  Pinoniq Jul 17 '13 at 8:04

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.