I am using MySQL as a back end storage. I was asked by our risk management team to encrypt all the data prior storing it into the database.
Since then I have been doing research on how to secure the data going in and out the database.
I found couple ways one of them was MySQL Encryption Software
A second solution was to encrypt and decrypt data in MySQL using AES_ENCRYPT() AND AES_DECRYPT(). But I will need to create a 128,196 or 256 bit key in order to be able to encrypt and decrypt the data. Then every time I want to execute INSERT/UPDATE I will call AES_ENCRYPT() and supply it with a key to encrypt the data. Then when I execute SELECT then I will have to call AES_DECRYPT() and supply the same key to convert the data to a plain text.
This means that I will have to define a variable in my PHP script that have the private key so I can encrypt/decrypt by supplying the defined variable to both AES_ENCRYPT() and AES_DECRYPT()
My question is Where/How to a store this private key to prevent a hacker from reading it. if someone hacks my server reads the key then he can simply read the data and the encryption would be mean less.
And what is the best way to go about securing my data?
Thank you
The issue you are facing is not a key issue but an issue of security of the rest of your computer. Using mysql means that mysql (if running safely) is running in it's own account. You would in fact put the keys in your mysql-owned directory. That secures much of mysql. MySql itself needs to have access to that key, so there is not much more you can do for that account. Just make sure it is readable only by the owning account.
If you hide your key and the hacker gets to your PHP code the hacker could do the following:
A. Echo/Print the KEY (it's a variable). He doesn't have to find the code where you define it, he could look for the function where you use it.
B. Forget about the KEY and use your own decrypting function to see the data and export it.
Most of the hacks are exploits to your PHP code, you should secure your computer, system and database.
Use MYSQL Stored Procedures
If only an authenticated user can get the key you could do this... Hide the KEY in a MYSQL STORED PROCEDURE and only give the key to the user when he completes the log in. All the data in the database should be encrypted and the key is not accesible as plain stored data. It should be inside the SQL Stored Procedure that a user having a password will get as query result. Users table should not be encrypted, only the password as one way.
This last solution should work only for authenticated users, like apps as example. You could protect the user profiles easily with this solution.
Users (basic data) --> Profiles (all personal details that depend upon log in)
I don't know much about stored procedures, but for sure you could apply it to the entire database and make your data accesible only through them.
Look at this article: http://www.sitepoint.com/stored-procedures-mysql-php/
And you will find PROS and CONS here: http://code.tutsplus.com/articles/an-introduction-to-stored-procedures-in-mysql-5--net-17843