Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

How to secure SQLite3 in Windows IIS CGI

by RedJeep (Sexton)
on May 30, 2019 at 17:20 UTC ( #11100732=perlquestion: print w/replies, xml ) Need Help??

RedJeep has asked for the wisdom of the Perl Monks concerning the following question:

Hello friends,

I am working on a rather simple web site. Windows IIS Perl CGI. I have a SQLite3 database to collect use information (Name, address, etc.). I encrypt user passwords with Argon... so good there. The question is how do I best secure the database itself? I have read up on different opensource/vendor solutions to encrypt but those are mostly structured around C# or seem overly complex. I have read up on password protecting SQLite3 databases but I can't tell if that just applies a simple password access or actually encrypts the database. I am hoping to hear from some of you as to how you protect SQLite3 databases in similar circumstances? I know I should move on from CGI but the site is only like seven pages.

I appreciate your insight and help.

---RedJeep (Full disclosure, I sold the Jeep cj5, the roof leaked too much when it rains it Portland. I have a Land Rover Discovery now. That roof also leaks too much. :-)
  • Comment on How to secure SQLite3 in Windows IIS CGI

Replies are listed 'Best First'.
Re: How to secure SQLite3 in Windows IIS CGI
by talexb (Canon) on May 30, 2019 at 17:53 UTC

    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.

    Alex / talexb / Toronto

    Thanks PJ. We owe you so much. Groklaw -- RIP -- 2003 to 2013.

Re: How to secure SQLite3 in Windows IIS CGI
by kennethk (Abbot) on May 30, 2019 at 18:52 UTC
    SQLite has a cheap, for-fee extension called SQLite Encryption Extension. My group's used it extensively, where you'll just need to recompile your drivers. It encrypts the entire database, and connecting via the PRAGMA approach is straight forward (Search for Using the "key" PRAGMA in the docs). No edits to DBI/DBD::SQLite necessary.

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

      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". ;-)
Re: How to secure SQLite3 in Windows IIS CGI
by aitap (Curate) on May 31, 2019 at 08:26 UTC
    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.

Re: How to secure SQLite3 in Windows IIS CGI
by RedJeep (Sexton) on May 30, 2019 at 22:00 UTC
    Thanks for the info. Moving *.db out of the web path is super easy to do. I will also explore the encryption extension.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://11100732]
Approved by marto
Front-paged by haukex
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (9)
As of 2020-12-04 19:53 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    How often do you use taint mode?





    Results (62 votes). Check out past polls.

    Notices?