so I am making this great webapp for our intranet and I want to use SQLite. For added security I think it would be great to use Zetetic SQLCipher which makes the DB file encrypted and unreadable in case someone would steal it.
I know how to use this with PHP PDO, but the problem is that latest build is 2 years old only for PHP 7.1 and I am not sure if there will be any more development
https://github.com/kynetiv/php_pdo_sqlcipher
I did not find any other PHP PDO library, but maybe I overlooked something.
Next thing is PowerShell which is needed for automation and gathering of data for my application. Here I am thinking if I should write to SQLite via temporary CSV file which would be processed by PHP or if I should access SQLite directly from PowerShell via PSSQLite… However it seems there is no modules to support SQLCipher in PowerShell, so I would have to abandon the encryption.
Ideas?
Advertisement
Answer
We have a great sibling site dedicated to security and one of the first things they ask everyone to consider when dealing with security is what the attack vectors are and what risks you are trying to mitigate. If you’ve done this already, I apologize, you can ignore this then.
The reason I say this, however, is that all-in-one solutions tend to give a false sense of security. For instance, if you believe it is likely and/or possible that your database might be physically compromised, what’s to say that they don’t also take the file holding the password, too? There are ways to mitigate that problem, too, but they also lead to other problems/complexities.
I say all this having just built an application that uses database encryption, so it is very top-of-mind. For our application, we’re using Doctrine as an ORM and we used this to encrypt columns as necessary. Our application was built with Symfony which might not be an option for you, but it greatly simplifies things, including your PowerShell problem (more on that later).
Back to your questions, that repo appears to be the only binary for Windows out there and unless you are willing to build it yourself, I would recommend avoiding it. PHP 7.1 has reached end of life and PHP 8 will be out in Q4 of this year. There are alternatives but they all require a license or building things on your own. So unless you are willing Because of that, I don’t think SqlCipher is a good fit for you
For your automation, once again I can’t stress how wonderful it is to work with Symfony Console applications. With just a couple of lines of code you can have a CLI, and if you want to can compile it into a PHAR to have just a single executable. We have a bunch of internal tools that we do that way.
If you don’t want to go the Symfony (or other framework) route, you can still just call your PHP script with a bunch of parameters. Is it better to write to a CSV file first or call directly? Both are absolutely valid options, so it really depends on your use-case and data set. For me, if my PHP code can read a file instead of making API calls or reading from stdin or similar, that is much easier to debug. So if your PowerShell code can do whatever logic and then call PHP with a “process this file”, that would generally be best from my perspective. But if you are better at PowerShell, then maybe processing there might be better. It honestly really depends.
edit
I just wanted to add more about encrypting at the application level. One thing that sucks about encrypted data is that it isn’t generally searchable, at least using the database itself. There are way out there to solve this, however:
- Building Searchable Encrypted Databases with PHP and SQL
- CipherSweet: Searchable Encryption Doesn’t Have to be Bitter
Additionally, if searching isn’t needed from the database, and you just want a place to store things letting your application handle decryption, PHP has some really wonderful and easy to use crypto via the Sodium library. Unfortunately the official documentation is lacking, but the following do a great job of demystifying things: