SQLite 3 Database Encryption


#1

I have been looking all over in this forum and other places online to find a good method of using an Encrypted SQLite Database with ExeOutput for PHP…

I have not been able to come up with a good solution. Does anyone have a good working solution that they can help me with.

I was playing around with using something like this example:

http://www.riptutorial.com/php/example/25499/symmetric-encryption-and-decryption-of-large-files-with-openssl

Where I would encrypt the decrypt the database to a temp folder when my app starts up then rewrite and update the encrypted data as any changes were made to the temp database, but this would open a security hole as once someone found out where the temp database was, they could modify it before it was written back to the encrypted version.

What about decrypting the Database to Memory/Ram then writing encrypted changes as needed. Again a Memory Dump may compromise the data.

I have seen a few projects and commercial plugins that look like they may work like these:

…but no sample code to test or look at, and no way of knowing if they would even work with exe php, as they mostly require custom builds of the SQLite engine. This is the last stage of my development with exe output for php then my app is done. Any help with this from anyone would really help!


#2

The samples shown are pure SQLite libraries, they seem not to have their own PHP extension port.
There is a workaround in a similar thread here:


#3

Ok so I have done some testing and managed to get php_pdo_sqlcipher.dll built for php 5.6.15 into the PHPEXE ext folder so i can include it in the build of my exe… I have also converted my database to an encrypted format using DB Browser of SQL .

Here is my question and issue… How do I call/open an encrypted databases using php? It seems like this should be obvious and the answer should be all over the internet… But it is not. I was using SQLite3 to access the database so the code looked like this:

 <?php
   class MyDB extends SQLite3
   {
      function __construct()
      {
         $this->open('mydatabasefile.db');
      }
   }
   $db = new MyDB();
   if(!$db){
      echo $db->lastErrorMsg();
   } else {
      echo "Opened database successfully\n";
   }

   $sql =<<<EOF
      SELECT * FROM combo_calcs WHERE options='easy';
EOF;

   $ret = $db->query($sql);
   while($row = $ret->fetchArray(SQLITE3_ASSOC) ){
      echo "ID = ". $row['ID'] . "\n";
   }
   echo "Operation done successfully\n";
   $db->close();
?>

I realize that I will have to switch to PDO format to use the php_pdo_sqlcipher.dll , but I can not find any information anywhere on how to pass an encryption_key using PDO. SQLite3 makes mention of passing an encryption_key to the database but I can not find a connection string or any sample code for PDO anywhere. Any help anyone can give would be much appreciated… I cannot believe how difficult it has been to get encryption going on an SQLite DB in windows. When I am done I am going to create a tutorial for anyone else that needs to do this starting from the basics.


#4

There is an explanation here: