Mysql – DB encryption for Django web app without storing key on server

djangoencryptionMySQLsqlitevps

We are setting up a virtual private server (not hosted by us), on which we will be user testing our Django-based web application. The user-generated content produced in these tests will be very sensitive. We would like to keep this content encrypted, for example in case back-up media goes missing. The content will be stored in a MySQL or SQLite database.

As I understand it, we cannot encrypt the file system of the VPS. If we encrypt the database, using something like SQLcipher (http://sqlcipher.net/), is there a way of passing the key to Django without storing it on the server? We will be booting up the server for each test, so that part is not a concern.

Best Answer

As you said you can't encrypt the filesystem of the VPS. I don't know which feature offers your VPS but you can try to create a second filesystem mounted on /var/lib/mysql (in the case of mysql Server) and encrypt it (for example using the software LUKS).

Another alternative is create a encrypted loop-device filesystem using a file of your VPS and mount it on /var/lib/mysql; here there is some references of this solution https://wiki.archlinux.org/index.php/Dm-crypt/Encrypting_a_non-root_file_system

Related Solutions

Sql-server – Do I need to back up the SQL Server Encryption Password and Symmetric Key

After doing some more research I found this article on "Cloning" Symmetric Keys.

You can instruct the CREATE SYMMETRIC KEY function to generate the Symmetric Key using a KEY_SOURCE and IDENTITY_VALUE. You can regenerate the same key later on by passing the same values for the KEY_SOURCE and IDENTITY_VALUE.

Here is a quick example:

CREATE CERTIFICATE CreditCards
   WITH SUBJECT = 'Customer Credit Card Numbers';
GO

CREATE SYMMETRIC KEY CreditCards_Key_01
WITH KEY_SOURCE = 'A pass phrase from which to derive the key.',
    IDENTITY_VALUE = 'An identity phrase from which to generate a GUID for tagging data that is encrypted with a temporary key',
    ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE CreditCards;
GO

OPEN SYMMETRIC KEY CreditCards_Key_01
   DECRYPTION BY CERTIFICATE CreditCards

UPDATE MyTable
SET EncryptedCreditCardNumber = EncryptByKey(Key_GUID('CreditCards_Key_01'), CreditCardNumber);
GO

To quote Michael Coles from his article

"For my tastes, it would make more sense to require IDENTITY_VALUE and KEY_SOURCE options by default."

Not sure why they didn't do it that way, or at least highlight that in the example they have out there on the MSDN!

Mysql – How to encrypt the passwords of all datasets in the thesql table

update users set password= md5('MyPassword') where password="myPassword";

It is not safe to use MD5 as a hash. MD5 is deprecated

Safe hash algorithms are PBKDF2, bcrypt, scrypt etc. And additionally all hash algorithms have to be used with salt. @Gerald Schneider posted a very good link for this topic: https://security.stackexchange.com/questions/211/how-to-securely-hash-passwords

The problem here is, that none of these save hash algorithm are implemented in mysql. There is an authentication mode for PBKDF2 in ProFTPD in the mod sql module. But there is no way OOTB you can generate a PBKDF2 hashed password, or a password, that is hashed with another safe algorithm, in a mysql database.

A possible solution would be to create an HTML page with PHP. PHP has functions, implemented by default, for generating safe hashed passwords.

I asked in the ProFTPD forum, if anyone there knows another, maybe better answer, to the problem: https://forums.proftpd.org/smf/index.php/topic,12110.0.html

Yes, I know the original question was, how could one hash all the passwords in my database at once with one command. But I think, first, I should look for a safe hash algorithm.

Best Answer

Related Solutions

Sql-server – Do I need to back up the SQL Server Encryption Password and Symmetric Key

Mysql – How to encrypt the passwords of all datasets in the thesql table

Related Topic