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.

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

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

Plain text

Copy to clipboard

Open code in new window

EnlighterJS 3 Syntax Highlighter

<code>= 16 * (trunc(100/ 16) + 1)

= 16 * (6.25 + 1)

= 16 * 7.25

= 116

</code>

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

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

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.

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.