Encrypt Database

Question

I have a website written in PHP with MySql database support.

I want to Encrypt entire database.
Basically what I want is if anyone get database login credential, must not be able to read the data.

I have read about mysql ENCODE() and AES_ENCRYPT() functions. But this way leads to a lot of changes in php script and also some performance issues.

Is there any technique to encrypt entire database file OR any other way to achieve this?

Could you elaborate on your threat model; in particular, who might the attacker be, what resources might they have, how valuable might the data be to them, from where might they launch an attack (i.e. physical or remote)? Could you elaborate on the required response; in particular, what do you mean by "*must not be able to read the data*"? Given enough time and resource, an attacker will be able to break any security system you devise - security is a question of balancing the threat with your response. So far as performance goes, nothing comes without a cost. — eggyal, Mar 25 '13 at 07:52
A huge consideration in any encryption system is key management: encryption will be useless if it's trivial for an attacker to access the decryption keys. But yet, for your application to work, such keys will have to be stored somewhere that they can be accessed. This is a hugely complicated subject... and most often when people come on SO asking "*how does one encrypt a database*" the answer is "*don't bother*". — eggyal, Mar 25 '13 at 07:54
what are you trying to achieve? read here an interesting article http://i.amniels.com/mysql-database-encryption-using-public-private-keys — i100, Mar 25 '13 at 07:56
@eggyal.. i am only worry about remote attack. i just don't want them understand the data. — Ashwini Agarwal, Mar 25 '13 at 09:43
Wait, for example, I'm attacked on server, if I have access to database I can via shell execution using PHP code to directory listing and view a PHP file. Now, I have access in PHP code so, no problems is grabbing a data from database and a code which returns and encode database system. But you speak about remote attack, but what about if remote attack is via FTP/SSH/Telnet/... and grabbed some PHP file and seen how encryption works? (example attacker copies all files on attacker local server)? — Marin Sagovac, Mar 25 '13 at 11:38

Scott Arciszewski · Answer 1 · 2018-10-28T18:55:56.460

If you're looking for standards compliance, just make sure the filesystem your database uses is protected by full-disk encryption.

For PostgreSQL, this is often attainable by shutting PostgreSQL down, copying /var/lib/postgresql to an encrypted drive, then renaming the old directory and mounting the new drive at /var/lib/postgresql before starting the database server back up again. (Be sure to shred the contents of the old directory once you're sure your migration was successful.)

For MySQL/etc., your mileage will vary, but it should also be possible.

Avoid using AES_ENCRYPT() and equivalent. ENCODE() doesn't help either, because encoding is not encryption.

If you need to encrypt individual records, you may be interested in our strategy for searchable encryption in PHP and SQL, but that's probably a bit out of scope for this question. We have a library called CipherSweet that implements searchable encryption in PHP + SQL.

Marin Sagovac · Answer 2 · 2013-03-25T08:04:38.823

Use SHA512 with bcrypt_encode() function for passwords and checksums.

For other specific data use with own SALT from PHP file + encode with some mechanism base64_encode($your_db_table_data) + "MY_SALT_FROM_WEB". And backwards, base64_decode($your_db_table_data) + MY_SALT_FROM_WEB.

You can use gzdeflate which decrpyt and compress your data + use salt to make hard to decrypt. Example:

$data = gzdeflate($your_data) + YOUR_SALT;

This will produce as simple like in database: ��O�KWH�KQpI�r\��*JZ<��AR

For decoding deflate use as: gzinflate(gzdeflate($your_data_from_db + YOUR_SALT_FROM_WEB));

This is reference: http://php.net/manual/en/function.gzinflate.php

If you want more complicate decoding use for char and smaller data base64_enocode / base64_decode + YOUR_SALT_HIDDEN_FROM_WEB with data in database for CHAR() and smaller texts.

This is my idea how would I encrpyted data in database for VARCHAR() usage. For text data use encrpypted way with compression with combination of SALT.

I have currently no idea another solution, or checkout encrypt MySQL data using AES technics: http://thinkdiff.net/mysql/encrypt-mysql-data-using-aes-techniques/

This uses AES_ENCRYPT and AES_DECRYPT integrate in MySQL but this is limited to VARBINARY(150) or VARCHAR(100). Using compression with encrpyted method can do this instead using integrated MySQL AES functions.

Hope this some helps.

As i already said.. I don't want to encrypt particular column. I want to encrypt entire database. — Ashwini Agarwal, Mar 25 '13 at 09:48
Use as MySQL function to encrypt all data and set as BINARY. All functions for MySQL for encryption are available only for row data http://dev.mysql.com/doc/refman/5.5/en/encryption-functions.html. You should avoid MySQL if you focus on encryption all database in MySQL. Use for supported or avoid MySQL for this, http://stackoverflow.com/questions/8054503/storing-encrypted-data-in-postgres — Marin Sagovac, Mar 25 '13 at 10:08
SQLite have transparent encryption of all data. Store for specific sensible data for encryption: http://sqlite-crypt.com/documentation.htm — Marin Sagovac, Mar 25 '13 at 10:10

Encrypt Database

2 Answers2