The most obvious suggestion that comes to mind is to put the SQLite3 database in a location that isn't accessible to a web browser. If your public-facing files are in a location like public_html which is a sub-directory of your user directory, put the database flle in a location outside of that, but still accessible by yourself and the web server userid.

SQLite has a cheap, for-fee extension called SQLite Encryption Extension.

Without even looking at the feature: You have a webserver stupidly serving the encrypted database file. And some code (Perl script, whatever) that holds the key to the encrypted database. All that an attacker now needs is either a second misconfiguration that allows reading the perl script (or whatever binary cooperates with the webserver) to get the key, or a flaw in the SQLite encryption.

The clean way is to place the database file somewhere outside the document root of the web server. Encrypting it does not hurt, except when you have performance issues. But as long as you provide both the encrypted file and the en-/decryption key (it must be a symmetric key), all encryption is just expensive obfuscation.

Oh, and by the way: The second misconfiguration does not have to be in the web server. A simple bug in a CGI script may be sufficient. One of my favorite ones was in gzip_cnc, partly because I caused that red security warning at the top of the page. gzip_cnc runs as a CGI, and up to version 1.10, it did not validate which files it compressed and returned to the browser (see security). If you appended any URL, it bypassed all webserver security measurements and returned any file, nicely compressed with gzip. Including its own code, the code of any CGI, the contents of every file accessible to the webserver. No passwords required, all limits bypassed.

Alexander

--
Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)

I probably should have included in the post that this is but one step in a defense-in-depth strategy. If one explicitly encodes a password in a Perl program, one must assume that password is fairly visible. There are more sophisticated ways of hiding that information (e.g., password daemon) but ultimately any proof-of-identity is imperfect. Cost of hurdles must be considered relative to value of information. But simply moving the unencrypted file to a path still accessible to the web server userid leaves the DB still vulnerable.

Thank you for describing so thoroughly the risks here. Detailed examples are very helpful in educating newer programmers, and I'm reading this as a rhetorical trick directed toward the GP.

---

#11929 First ask yourself `How would I do this without a computer?' Then have the computer do it the same way.

SQLite Encryption Extension

Second paragraph on that page (emphasis mine):

Some of the documentation on this website is open to viewing by the general public. However, access to the SEE source code and precompiled binaries requires a username and password, which is available only to licensees. Perpetual source-code licenses are available for purchase separately.

So, security by obscurity? Sorry, but that has a certain unwelcome smell. See Kerckhoffs's principle.

https://www.sqlite.org/see/doc/release/www/readme.wiki lists the following algorithms:

• RC4 with security enhancements
• AES-128 in OFB mode
• AES-128 in CCM mode
• AES-256 in OFB mode
• Simple XOR (in the text below the list)

XOR is explicitly documented as "demonstration module that does not do real encryption". Consider it broken.

RC4 is insecure, if not broken. Period. See RC4. There are no agreed "security enhancements".

AES-128 is probably still ok, if implemented correctly. But side channel attacks can be very efficient, recovering the entire 128-bit key using 6 to 7 blocks of encrypted content. See Advanced Encryption Standard.

AES-256 should be ok, if implemented correctly.

How can you check if AES is implemented correctly? How can you check the "security enhancemments" for RC4? Well, buy the pig in the poke, then look at what you bought. Security by obscurity.

Alexander

--
Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)

The question is how do I best secure the database itself?

There is no universal answer since it depends on what you are securing the database from. (What is your threat model?)

Not serving the database file itself for everyone to download as a static file is a great first measure. But what is the password supposed to protect from? Other users on the same server? That should be handled by file ownership and permissions provided by the operating system. Hackers with access to files belonging to the web server? They would just read the password from the same place the web server reads it from to serve the database content.

Nevertheless, if you do have a use for database encryption and you don't have the budget for $2000 SEE, another option to consider is free SQLCipher.