Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a MYSQL server on a linux server. I am using AES encryption functions to store data in the DB server. Commands are like this ones:

INSERT into userc (name, town) VALUES ('john',AES_ENCRYPT('nebraska', 'usa2010'));
SELECT CAST(AES_DECRYPT(town, 'usa2010') AS CHAR(50)) town_decrypt from userc;

My concern on this kind of encryption is that everything needed to access my data is travelling in clear, so a sniffer or a Debug level log are capable of capturing everything.

Is there a way of not sending the key in the command, but having it stored on a file (/home/user/key.txt) and so calling the encryption in a way similar to this:

INSERT into userc (name, town) VALUES ('john',AES_ENCRYPT('nebraska', key1));

Where key1 is the reference to the file where the key is stored?

share|improve this question

1 Answer 1

up vote 0 down vote accepted

Depending in your perspective, the preferable approach would be to continue doing it the way you're doing it, but to use SSL to encrypt communications between the application server and the database -- because if you don't, then not only are you sending the encryption key in the clear, you're also sending the decrypted data in the clear.

It seems a little bit pointless to protect the encryption key from packet sniffer snooping, while continuing to transmit the data you're trying to protect, over the same channel, also in the clear.

Granted, the exposure would theoretically be less, since you wouldn't be giving away the key, itself, but you're still exposing the "secure" data.

You could avoid passing the key over the wire by declaring a stored function that returns the key, and using a call to the stored function in place of the constant in the queries, but that creates what is arguably a worse scenario, because a breach of your database's security exposes both the encrypted data and the keys necessary for decryption. But, this would avoid the specific issue that you're intending to avoid.

mysql> CREATE FUNCTION aes_key () RETURNS TINYTEXT DETERMINISTIC NO SQL RETURN 'usa2010';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT aes_key();
+-----------+
| aes_key() |
+-----------+
| usa2010   |
+-----------+

Now, your queries look like AES_ENCRYPT('nebraska', aes_key()). As I say, this does accomplish the direct purpose you intend, and the permissions on the stored function would serve to keep honest people honest, but putting it right out there on the server along with the data seems like a particularly bad idea.

Perhaps slightly more orthodox would be to compile a simple User Defined Function (UDF) in C, which would return a constant string -- the decryption key -- when called, and reference that function in the queries. The only advantage of this approach is that the UDF wouldn't be returned if an attacker did a full database dump to extract the data. One potentially significant disadvantage is that user-defined functions are global and are not controlled by permissions -- they are accessible to any user who can connect to the database server.

And, of course, either of these alternatives, like the mechanism you proposed in the question, involves storing the encryption key on the hard drive of the database server, where it is more vulnerable if the database server is breached. If the key is stored with the application code, then both platforms have to be breached for your sensitive data to be effectively stolen.

So, no... I would suggest that the appropriate place for storing the encryption key would be somewhere other than the database server itself, specifically in the application server, and using SSL to connect from the application to MySQL if data on the wire between the two systems is a security concern.

share|improve this answer
    
I guess that the UDF is the good one. It is true that I allow the data travel clear but I plan to protect the file at low level (presumably SELINUX). –  jordi Feb 11 at 8:20

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.