Opinion Request: Looking for the best method to store data and protect it (encryption?)

[CoffeeJoe]

I am working on a program to keep accounts with usernames / passwords and manage a recurring monthly schedule.

Essentially like this:

===================================

Netflix

URL: https://...

U:

P:

Payment $x.xx on the 14th of every month

Autopay out of account Blah Blah...

===============================

And

A visual schedule that will show a line (or block) entry for this upcoming payment when it gets close...

================================

I have some plans on how to put it all together, but I'm not sure how to store the data while not running.

I have read several threads on the subject but nothing current.

Is there a best solution method for storing the data?

 

I have toyed with encrypted text in an INI file for smaller applications but that's not going to work here.

I'll take any suggestions anyone wants to offer.

 

Thanks

[TheDcoder]

It's best not to make your own password manager, it is incredibly hard and time consuming to patch all the holes... not counting the effort you'd need to go through to make it usable, especially outside Windows computers, like your phone.

Just use KeePass, it's a secure and open-source password manager with all of the features you'd need, there are clients for Android and the main program itself is cross-platform.

If you want to store files in a secure way, try VeraCrypt (TrueCrypt's defacto successor).

[CoffeeJoe]

TheDcoder -

Ok I read and understand what you wrote. Thanks

... But - I'm set on building this myself so Keypass is not an option because it doesn't integrate...

I like the idea of a veracrypt volume to hold both the app and the data so that could essentially take the protection issue off the table.

I can even wrap the CL interface as part of my app - I like it more the longer I think about it...

What about the storage method? I can't be the only person looking to store more than keys and values with Autoit?

FileWriteLine has no structure for reading anything out

INIRead is too 1 dimensional

? What would you use?

 

[TheDcoder]

2 hours ago, CoffeeJoe said:

? What would you use?

For a bit more complex storage I would use JSON

There are a few JSON UDFs, you can use them to create JSON objects and manually read/write them to your files.

If the storage is more like a database I would consider SQLite, which is supported by the official UDF in AutoIt

[jchd]

You can use an encrypted SQLite database by using a version of system.data.sqlite.dll <= 1.0.112.0 as more recent releases drop the encryption layer.

Alternatively you can use any release of sqlite.dll and place the database on an encrypted volume/directory. Then access protection and password management is pushed back to the OS, making your life simpler as you noted.

There are many advantages from using such a database:
  o) zero installation or maintenance required
  o) everything can be embedded inside your application
  o) the RDBMS engine gives you ACID properties for free
  o) it's powerful, fast and secure
  o) you can easily refactor your design, should your needs evolve over time
  o) it's portable accross any platform
  o) it's very well documented
  o) ...

[CoffeeJoe]

2 hours ago, jchd said:

it's very well documented

This is the Autoit Motto - Where would any of us be without the documentation and this community?

[TheDcoder]

6 hours ago, jchd said:

You can use an encrypted SQLite database by using a version of system.data.sqlite.dll <= 1.0.112.0 as more recent releases drop the encryption layer.

Interesting, I assume there's a good reason behind it? Or did they just not want to hassle with an extra layer of maintenance?

[jchd]

1 hour ago, TheDcoder said:

Or did they just not want to hassle with an extra layer of maintenance?

The reason is elsewhere: the SEE (SQLite Encryption Extension) is payware and the possibility to use system.data.sqlite using the very same API made it possible to get encryption for free. The SQLite team has to earn some money to maintain, enhance and expand one of the most used free piece of software ever. They decided to drop free encryption from the system.data.sqlite bundle.

Note that the whole SQLite codebase is public domain so there's nothing preventing you or anyone else to add the encryption layer of your choice to your derivation, modulo you know exactly what you're doing!

[TheDcoder]

@jchd So if I am understanding correctly, they exposed two APIs which practically do the same thing but locked one behind a paywall?

[jchd]

SQLite itself is a C library, the core of the RDBMS, while system.data.sqlite is an ADO.NET provider for SQLite. The former is due to Dr. Richard Hipp and maintained by SQLite dev team and the latter originates from Robert Simpson and is now maintained by Joe Mistachkin, a member of the team.

The .NET wrapper follows the core SQLite development with a delay since eventual new SQLite APIs demand new code and anyway extra testing is required beyond the huge SQLite testing harnesses, see https://www.sqlite.org/testing.html

system.data.sqlite comes as source code or a number of precompiled bundles, some offering both the legacy C and .NET interfaces (see https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki). That's why our AutoIt code or any C code can invoke either sqlite.dll or system.data.sqlite.dll transparently.

[TheDcoder]

@jchd Wow, that's even worse. I read some stuff about SEE earlier today and their licensing terms don't allow redistributing the source or even the binaries for any purpose other than for use in a program.

And to put salt on the wound, they have been hosting it on same server too

At this point I am thinking it was a deliberate decision and they hoped who ever used that API would pay the fee... perhaps after using it as an unofficial free trial.

[CoffeeJoe]

Sooo for my question

I am using the sqlite UDF and my first test app was surprisingly easy to get running.

I would put my DB skill level at about 30% so I have some things to learn before tackling this project.

Luckily the internet is full of information about SQL and its different flavors.

@jchd I think I'll get most if not all I'll ever need from the W3Schools link in your signature.

@TheDcoder I'm sticking with the encrypted container idea so SQL encryption is a moot point for this project.

Thanks for the feedback guys

[jchd]

@CoffeeJoe your best source of information about SQLite is the official webpage. SQLite differs from most other modern RDBMS in that it's an embedded engine contrary to client-server designs: you link your application with the source and compile the whole cake, or build you app and use a DLL as we do with AutoIt.

SQLite is by far the most prevalent RDBMS ever, is used by many embedded devices like routers, smart TVs, GPSes, your smartphone (many instances there), your PC (Windows 10 now comes with one SQLite DLL, your navigator(s) use it, your car is full of it, etc. None of these devices use a client-server design. Just like many websites use it with great success.

That means that there are several basic points you need to be aware of. Think of SQLite in this context as a hyper-powerful fopen()/fread()/fwrite()/fclose() combination with a huge lot of goodies.

SQLite tries to follow most of Postgress SQL features, yet keeping in mind the "lite" suffix.

[seadoggie01]

On 10/23/2021 at 12:53 AM, CoffeeJoe said:

Keypass is not an option because it doesn't integrate

Have you checked out KPScript? I have a script for wrapping some KPScript commands in AutoIt functions, but most people hardcode them. Personally, I wouldn't store passwords in anything I've written because I don't trust myself to catch all the bugs. On the other hand, if it's a personal application on your personal device, I completely understand and I've even stored passwords in the scripts before

[CoffeeJoe]

@seadoggie01

I didnt know this existed - thanks for the info

this will likely be a new tangent for me after reading a bit about it - care to share what you put together?

I got hooked by one commenter complaining that each command is load DB from file / decrypt / perform command / reencrypt / write back to disk...

thinking there should be a better way I plan to do some testing... as well as look at what they did in C# as the site described.

 

[jchd]

4 hours ago, CoffeeJoe said:

I got hooked by one commenter complaining that each command is load DB from file / decrypt / perform command / reencrypt / write back to disk...

It doesn't work that way. Using SQLite and with or without any OS-layer encryption it'll always work like this:

1. App start
2. open DB
3. while app duration
4.     read DB
5.         [the engine determines which pages it should read in order to locate the row(s) you want and fetch them]
6.     update DB
7.         [the engine determines which pages it should read in order to locate the row(s) you want, then fetch, change and rewrite changed pages]
8.     write DB
9.         [the engine determines which pages it should change/add in order to add/delete what you want, then fetch, change and rewrite changed pages]
10. wend
11. close DB
12. app termination

The encryption/decryption of data is done transparently by the OS/driver at the page/cluster level.

There is zero difference between using an unencrypted DB, an encrypted DB placed on an OS-layer encrypted volume. Using system.data.sqlite with an encrypted DB placed on an unencrypted volume the only thing you have to do is issue a new step at 2.5 sending a pragma to supply the encryption key. Note that you can add that step in all 3 cases since this pragma is ignored when the DLL has no support for encryption.

In the last case it's however your responsability to manage the encryption key, while in the 2 former cases your OS credentials can do that for you with good level of security.

Edited October 27, 2021 by jchd

[JockoDundee]

1 hour ago, jchd said:

It doesn't work that way.

When the commenter said:

Quote

…each command is load DB from file / decrypt / perform command / reencrypt / write back to disk...

I don’t think he meant each of these had to be run separately, rather he may have been just complaining about the extra overhead that goes on under the hood due to encryption.

[Skysnake]

may have been just complaining about the extra overhead that goes on under the hood due to encryption

🙈

[CoffeeJoe]

haha - you're all wrong 🙃

I was "quoting" a commenter on the subject of KPScript... THEY said each command requires that process....

I'm not doing SQLite encryption - I'm going with TheDCoder's suggestion and putting the whole thing in an encrypted container using Veracrypt

When its open its accessible, when its not its not. Currently playing with SQLite 3.8.xxx something - Its a huge step from IniRead / IniWrite 😵

I said I'd seen that users comment on the KPScript page and immediately started thinking about a tangent project to see how KPScript works and if I can make use of it.

 

 

[jchd]

2 minutes ago, CoffeeJoe said:

Currently playing with SQLite 3.8.xxx something

3.8.xxx is at least 6 years old. Please download 3.36.0 stuff from the release page: there have been dramatic improvements since 2015, see changelog and dig in the SQL documentation to see how today's SQLite core has evolved (maintaining backward compatibility).