Lazarus

Programming => General => Topic started by: PasCoder on December 06, 2022, 10:26:02 am

Title: How Do I Add A Password to a SQLite Database File?
Post by: PasCoder on December 06, 2022, 10:26:02 am
Dear Friends,
I hope you're all fine and doing well. I'm creating an App using SQlite Database File but I don't want the user to open the database files since they may misuse the data therein. How can I achieve my goal? I want only my App to be able to open the database files. It seems the sqlite3.dll does not allow using passwords!!!

Thanks
Title: Re: How Do I Add A Password to a SQLite Database File?
Post by: Чебурашка on December 06, 2022, 10:50:13 am
I want only my App to be able to open the database files.

You could encrypt/decrypt the db file from inside your application, but this does not prevent your users to open the file while application is running, unless you do not make the temporarily un-encripted file not accessible by means of operating system file access features.

To be onest? SQLite is not intended to support this user/password features, so maybe is not the right tool for your needs. Perhaps your should consider using a DBMS that has this feature built in.
Title: Re: How Do I Add A Password to a SQLite Database File?
Post by: PasCoder on December 06, 2022, 11:27:05 am
Thank you for your quick response but still I don't like to use big RDMS like SqlServer, MySQL, etc. Is there any other file-based database like SQLite that I can use for my purpose while supporting password protection?

Thanks
Title: Re: How Do I Add A Password to a SQLite Database File?
Post by: Zvoni on December 06, 2022, 11:37:23 am
There are two ways to get something like that in SQLite:
1) You have to compile SQLite yourself incl. the "authentication"-module
2) You have to compile (or get it from somewhere) SQLite incl. encryption. Look for SQLite with SEE

"Off-the-Shelf"-SQLite provides neither of those two

EDIT: Why would you need "authentication" for a filebased Database?
Title: Re: How Do I Add A Password to a SQLite Database File?
Post by: PierceNg on December 06, 2022, 12:08:22 pm
I want only my App to be able to open the database files.

You could encrypt/decrypt the db file from inside your application, but this does not prevent your users to open the file while application is running, unless you do not make the temporarily un-encripted file not accessible by means of operating system file access features.

With SQLite transparent encryption, there is no need to decrypt the entire database file.

How it works: As data is read into the application, it is decrypted. Whatever data not read by the application remains encrypted on disk. As data is written, it is transparently encrypted.
Title: Re: How Do I Add A Password to a SQLite Database File?
Post by: mig-31 on December 06, 2022, 01:12:06 pm
Firebird can work as file-based database.

https://wiki.freepascal.org/Firebird_embedded (https://wiki.freepascal.org/Firebird_embedded)
Title: Re: How Do I Add A Password to a SQLite Database File?
Post by: Zvoni on December 06, 2022, 01:50:18 pm
Firebird can work as file-based database.

https://wiki.freepascal.org/Firebird_embedded (https://wiki.freepascal.org/Firebird_embedded)
.... and ignores any Authentication provided by the user --> https://stackoverflow.com/questions/63605057/how-can-i-create-a-user-in-embedded-firebird-3-0
Title: Re: How Do I Add A Password to a SQLite Database File?
Post by: fabiopesaju on December 06, 2022, 02:17:21 pm
you can use wxsqlite3 or sqlcipher... both are sqlite with encryption... maybe you will have to use "pragma" config to setup de cryptography
Title: Re: How Do I Add A Password to a SQLite Database File?
Post by: balazsszekely on December 06, 2022, 02:19:42 pm
@PierceNg
Quote
With SQLite transparent encryption, there is no need to decrypt the entire database file.
How it works: As data is read into the application, it is decrypted. Whatever data not read by the application remains encrypted on disk. As data is written, it is transparently encrypted.
Encrypting/decrypting everything will considerably slow down the application, especially on a large database, besides won't protect the user against memory dump.

@mig-31, @Zvoni
Firebird assumes that the computer on which the server is running is safe. If you physically have access to the database, you can always connect with sysdba/masterkey, no matter if the database is embedded or not. More recent version of firebird(3+) also supports encrypting, but then we bump into performance issues again.

The real question is why OP thinks that the user will misuse the data? Real sensitive information like username/password should be indeed encrypted, but other then this I don't see why an average user should temper with the database.
 
Title: Re: How Do I Add A Password to a SQLite Database File?
Post by: Zvoni on December 06, 2022, 02:39:51 pm
The real question is why OP thinks that the user will misuse the data? Real sensitive information like username/password should be indeed encrypted, but other then this I don't see why an average user should temper with the database.
 
Correct.
It's SQLite, it's filebased, meaning it's not Multi-user.
Why would i need a Username/Password?

If i use a program which has a SQLite in its "basement" then the Data there belongs to me, and any developer thinking "No, this doesn't concern you. Stay out of it" gets hell from me, and his app deleted.

Bottom Line: A (filebased) Database with only a single-user has no authentication (whatever for?), and (at maximum) maybe encryption (with all downsides GetMem mentioned)
Title: Re: How Do I Add A Password to a SQLite Database File?
Post by: PierceNg on December 06, 2022, 02:43:50 pm
Encrypting/decrypting [...] besides won't protect the user against memory dump.

Firebird assumes that the computer on which the server is running is safe.

I have to say, above two sentences in one response to two different posts is funny. :D I mean, if Firebird embedded works on that assumption, then SQLite can too.

More seriously, the "will considerably slow down" part is a common trope. Basically same argument as in HTTP versus HTTPS, but vast majority of websites are now on HTTPS, and the people who continue to run their sites on HTTP aren't doing it because encryption considerably slows down their traffic.
Title: Re: How Do I Add A Password to a SQLite Database File?
Post by: balazsszekely on December 06, 2022, 02:58:01 pm
@GetMem
Quote
Real sensitive information like username/password should be indeed encrypted
Username and password should be hashed. Other sensitive information encrypted.  :) Sorry for that.

@Zvoni
Quote
If i use a program which has a SQLite in its "basement" then the Data there belongs to me, and any developer thinking "No, this doesn't concern you. Stay out of it" gets hell from me, and his app deleted.
I agree 100%!

@PierceNg
Quote
I have to say, above two sentences in one response to two different posts is funny. :D I mean, if Firebird embedded works on that assumption, then SQLite can too.
Well they were talking about firebird explicitly, but yes the same is true for SQLite too.

Quote
More seriously, the "will considerably slow down" part is a common trope. Basically same argument as in HTTP versus HTTPS, but vast majority of websites are now on HTTPS, and the people who continue to run their sites on HTTP aren't doing it because encryption considerably slows down their traffic.
I beg to differ, if you have a few hundred GB database with large blobs fields, encryption will slow down the application, I experienced firsthand.
 
Title: Re: How Do I Add A Password to a SQLite Database File?
Post by: jcmontherock on December 06, 2022, 05:59:40 pm
Simply, you can use SQLCipher library. I use it and it's work fine.
Title: Re: How Do I Add A Password to a SQLite Database File?
Post by: ttomas on December 07, 2022, 02:16:23 pm
You can try mORMot Framework. Static linking SQLite with password protection/encryption of db file.
For internal use I create Password Vault App for keeping all my passwords, keys in 1 internal place, SQLite db.
Title: Re: How Do I Add A Password to a SQLite Database File?
Post by: PierceNg on December 07, 2022, 02:25:15 pm
Quote
More seriously, the "will considerably slow down" part is a common trope. Basically same argument as in HTTP versus HTTPS, but vast majority of websites are now on HTTPS, and the people who continue to run their sites on HTTP aren't doing it because encryption considerably slows down their traffic.
I beg to differ, if you have a few hundred GB database with large blobs fields, encryption will slow down the application, I experienced firsthand.

Obviously I have no idea what's in your database, but just going by your statement, some thoughts come to mind, assuming you're talking about SQLite with encryption in the context of a single-user GUI application:

- https://sqlite.org/intern-v-extern-blob.html (of course this loses the transparent encryption)
- sharding the database over multiple files, and using ATTACH (perhaps with UNION virtual table (https://sqlite.org/unionvtab.html)), or simply multiple database connections to process the relevant database file

Personally, "encryption causes slow down" is never a reason to not use encryption. If you gotta do it, you gotta do it.

The reason to not use encryption is when it is unnecessary.
Title: Re: How Do I Add A Password to a SQLite Database File?
Post by: Zvoni on December 07, 2022, 02:45:02 pm
The reason to not use encryption is when it is unnecessary.
And in 99.99999% of cases it's not necessary, since it's your own data on your own device
Title: Re: How Do I Add A Password to a SQLite Database File?
Post by: ttomas on December 07, 2022, 03:02:32 pm
PasCoder need to store passwords in database.
Where you keep all your passwords? In Password Managers?
https://password-managers.bestreviews.net/faq/which-password-managers-have-been-hacked/ (https://password-managers.bestreviews.net/faq/which-password-managers-have-been-hacked/)

About "encryption causes slow down", no if properly done with fast NI encryption algorithm.
Title: Re: How Do I Add A Password to a SQLite Database File?
Post by: Zvoni on December 07, 2022, 03:25:16 pm
Where you keep all your passwords? In Password Managers?
In my brain..... Best encryption, no possible way for someone else to hack

Quote
PasCoder need to store passwords in database.
and as already mentioned: Passwords should not be stored in a database (encrypted or not), but its hash!!
Title: Re: How Do I Add A Password to a SQLite Database File?
Post by: PierceNg on December 07, 2022, 03:30:24 pm
PasCoder need to store passwords in database.
Where you keep all your passwords? In Password Managers?
https://password-managers.bestreviews.net/faq/which-password-managers-have-been-hacked/ (https://password-managers.bestreviews.net/faq/which-password-managers-have-been-hacked/)

I use Password Gorilla which is written in Tcl/Tk and portable to Linux, macOS and Windows as a Tclkit.
Title: Re: How Do I Add A Password to a SQLite Database File?
Post by: ttomas on December 07, 2022, 03:42:58 pm
Where you keep all your passwords? In Password Managers?
In my brain..... Best encryption, no possible way for someone else to hack
Good for you, my brain have limit :-)
Just see in my app, I have 53 Win/Linux very strong passwords and 22 VPN config/certificates only for business (production) + 15+ personals passwords.
Title: Re: How Do I Add A Password to a SQLite Database File?
Post by: PasCoder on December 09, 2022, 04:32:30 pm
Guys, thank you for the discussion. I'm really learning a lot from your pieces of advice. Remember, I'm new on this arena of programming in FP and Lazarus. I've my background in VB.net and C# from Microsoft.net Frameworks. There are many file-based databases that support passwords like VistaDB and many others. I thought that its the same this side!

Now, look at my scenario, I'm making a small App for a Savings and Credit Cooperative Society where members' sensitive transactions will be saved in a SQLite database including their savings, withdraws and other crucial information. Isn't it prudent to have such a databse be protected with a password such that a person using the computer at anytime uses another program to open it and may be change the figures?

Title: Re: How Do I Add A Password to a SQLite Database File?
Post by: PierceNg on December 10, 2022, 07:46:32 am
Guys, thank you for the discussion. I'm really learning a lot from your pieces of advice. Remember, I'm new on this arena of programming in FP and Lazarus. I've my background in VB.net and C# from Microsoft.net Frameworks. There are many file-based databases that support passwords like VistaDB and many others. I thought that its the same this side!

SQLcipher (https://www.zetetic.net/sqlcipher) is the most popular open source transparent SQLite encryption add-on.

Now, look at my scenario, I'm making a small App for a Savings and Credit Cooperative Society where members' sensitive transactions will be saved in a SQLite database including their savings, withdraws and other crucial information. Isn't it prudent to have such a databse be protected with a password such that a person using the computer at anytime uses another program to open it and may be change the figures?

One trick other desktop applications storing data in SQLite is known to have used, is to modify the SQLite data file header (https://sqlite.org/fileformat.html). Section 1.3 describes the header format. Some applications change the header string from the default  'SQLite format 3\000' to something else. (SQLcipher does this to an encrypted database.) Then the standard sqlite3 CLI tool is unable to read the file.

As for user authentication like what is talked about for FB embedded in this thread, an extension (https://www.sqlite.org/src/doc/trunk/ext/userauth/user-auth.txt) exists for SQLite.

Ultimately, as many others have pointed out here and elsewhere, when the SQLite database file is physically located on the user's computer, a dedicated user (or an attacker who has access to your application and the database file) will be able to reverse engineer whatever 'protections' you implement in your application. These protections are roadblocks, not impenetrable barriers.
TinyPortal © 2005-2018