How to encrypt database in symfony

Question

I work with symfony framework and Propel and i was wandering what is the easiest way to encrypt a database. I'm not talking about passwords but all the database . I built a small web application for a client that manages some information (user accounts, passwords etc) and i don;t want it to be visible if someone has access to phpmyadmin for example.

Answer 1

Generally you would handle this with MySQL's built-in permissions.

The only way someone could use phpMyAdmin is if it is installed and configured on the server - the easiest way to fix that is to simply uninstall it, or if you can't do that use an SSL connection and strong passwords.

Other things to consider would be locking down the server - for a start you can ensure that MySQL only accepts connections from localhost or the local network.

As to the actual question - I know of no way to encrypt an entire database in an effective way. You can certainly encrypt the data, but given that you would need to place the decryption code on the server anyway, the benefit would be moot ... if someone has access to the server, they would still be able gain access to the data. The best thing is to prevent that access in the first place.

Answer 2

This is a 3 level question. You must protect several things :

1. The input / ouput data so it cannot be intercepted.
2. The admin rights, so your system can't be access by the wrong person while working.
3. The hard drive itselft, so if somebody has direct access to the computer, he won't be able to access it even by extracting the hardware.

For 1, you can use SSL and HTTPS.

For 2, follow the best practices of sysadmin for setting passwords, rights and updates (serverfault.com is your friend). This includes the DB admin.

For 3, you must encrypt the entire hardware. Using encryption for the DB only will kill your server performances. Using encryption will slow down your system anyway, but doing it at a level above the file system if a dead end for web apps. Plus, tools for running an entire Linux system on a encrypted hard drive are pretty matures (on Ubuntu, LVM let you do that almost transparently).

As you noticed, this has nothing to do with Symfony, what so ever.

Answer 3

Well this might be a bit naive, but how about simply encrypting on the server [php I assume] side before you write to the DB and then decrypting it back when you do reads ? It looks like your issue is you don't want a server admin to easily read your data. If you use Symfony, you can probably do this deep enough in the model that your code won't have to change.

---

After some digging, here is how I would do it if I were feeling particularly brave.

Edit buildParams() in xxx/symfony/vendor/propel/util/BasePeer.php, on like 860 you will find

$params[] = array('column' => $crit->getColumn(), 'table' => $crit->getTable(), 'value' => $crit->getValue());

change to

$params[] = array('column' => $crit->getColumn(), 'table' => $crit->getTable(), 'value' => your_encryption_method($crit->getValue()));

(unless I missed something) all symfony/propel writes to the database will eventually end up calling that function, so everything you write to the db gets encrypted with your_encryption_method().

Try this out and if it works and you're still feeling brave, modify the addHydrate() function in xxx/symfony/vendor/propel-generator/classes/propel/engine/builder/om/php5/PHP5BasicObjectBuilder.php (line 668) so that the hydrate() code propel generates for you when you call symfony propel-build-model or symfony-propel-build-all will automatically decrypt everything from the database.

it should currently look like

                                    case PropelTypes::DATE:
         case PropelTypes::TIME:
         case PropelTypes::TIMESTAMP:
          $script .= "
      \$this->$clo = \$rs->get$affix(\$startcol + $n, null);
";
          break;
         default:
          $script .= "
      \$this->$clo = \$rs->get$affix(\$startcol + $n);
";

you want to modify it to something along the lines of

case PropelTypes::DATE:
         case PropelTypes::TIME:
         case PropelTypes::TIMESTAMP:
          $script .= "
      \$this->$clo = \$rs->get$affix(\$startcol + $n, null);
";
          break;

         default:
          $script .= "
      \$this->$clo = \my_decryption_function($rs->get$affix(\$startcol + $n));
";

After you make this change, run symfony propel-build-model and let me know if it worked :-). Good luck and you probably already know this, but make backups of these files before you modify them.... oh and since you're modifying core symfony, your other projects will have issues unless you want them all to write encrypted data to the db