One way is:
- When the user logs in, store a session ID in a cookie on the client's computer (not the username or password).
- Tie session to IP address, so an individual session ID only works with the computer it was started on.
Depending on what framework you are using to develop your site, this behavior may be available as a built-in feature.
Note that, because the HTTP protocol is stateless anyway, there is actually no functional difference between keeping someone logged in during a single session of using the website and "auto-login" the next time they use the site; it is solely a matter of how much time you allow before the session expires.
Update: Also, use HTTPS for increased security, obviously.
Update 2: Note that this approach has limitations, in that it doesn't work well for users who change their IP address a lot. However, it does provide an increased level of security and may be useful in some situations.
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
Best Answer
There is no 100% secure solution. What ever you implement must be executable by a deterministic computer, so someone and substitute that and unpick what ever technical barriers you put in their way. You can make it more technically challenging and a more involved and slower process, but in time if the incentives are there, someone will break it.
If you are implementing this in .NET then, by default it is trivial for someone to obtain C# style source code to your application, and then to step through their C# code in a debugger. It is a little more complex in C/C++, but again not impossible. I do not know how much of a challenge this is with JAVA, but I would not expect it to be a significant hurdle.
There are processes that can be applied to make the code harder for a human to read, but do very little to make it harder for a computer to single step through the code. Someone can put a breakpoint in the system library and then back track up the call stack to see if this call to a network socket is of interest without having to understand your code base.
I have also seen encryption techniques used, where a program decrypts the actual code during run time. However, again, a determined hacker can just run this part of the code, obtain the decrypted code and then reverse engineer this into source code.
The best solution is to assume it will become compromised, and then ensure that the process for changing the sensitive data is trivial for you, and to process to compromise the sensitive data is as hard as possible for everyone else. That's why some systems require a web call to obtain the data. You can then block calls from IP's or from client certificates that you no longer trust, and give everyone else 'this months' application key.
In the end, you need to consider the consequences for having the data publicly known, and then decide how far you need to go, to make the process for obtaining it more skilled and and more time consuming.