Recent

Author Topic: How Do I Add A Password to a SQLite Database File?  (Read 1260 times)

PasCoder

  • New Member
  • *
  • Posts: 34
How Do I Add A Password to a SQLite Database File?
« 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
« Last Edit: December 06, 2022, 10:28:15 am by PasCoder »

tt

  • Full Member
  • ***
  • Posts: 239
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: How Do I Add A Password to a SQLite Database File?
« Reply #1 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.
« Last Edit: December 06, 2022, 10:53:36 am by tt »
FPC 3.2.0/Lazarus 2.0.10+dfsg-4+b2 on Debian 11.5
FPC 3.2.2/Lazarus 2.2.0 on Windows 10 Pro 21H2

PasCoder

  • New Member
  • *
  • Posts: 34
Re: How Do I Add A Password to a SQLite Database File?
« Reply #2 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

Zvoni

  • Hero Member
  • *****
  • Posts: 1603
Re: How Do I Add A Password to a SQLite Database File?
« Reply #3 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?
« Last Edit: December 06, 2022, 11:39:56 am by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

PierceNg

  • Sr. Member
  • ****
  • Posts: 290
    • SamadhiWeb
Re: How Do I Add A Password to a SQLite Database File?
« Reply #4 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.

mig-31

  • Sr. Member
  • ****
  • Posts: 300
Re: How Do I Add A Password to a SQLite Database File?
« Reply #5 on: December 06, 2022, 01:12:06 pm »
Firebird can work as file-based database.

https://wiki.freepascal.org/Firebird_embedded
Lazarus 2.0.6 - CentOS 7.x, Mageia 7.1

Zvoni

  • Hero Member
  • *****
  • Posts: 1603
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

fabiopesaju

  • Jr. Member
  • **
  • Posts: 88
Re: How Do I Add A Password to a SQLite Database File?
« Reply #7 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

GetMem

  • Hero Member
  • *****
  • Posts: 3948
Re: How Do I Add A Password to a SQLite Database File?
« Reply #8 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.
 

Zvoni

  • Hero Member
  • *****
  • Posts: 1603
Re: How Do I Add A Password to a SQLite Database File?
« Reply #9 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)
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

PierceNg

  • Sr. Member
  • ****
  • Posts: 290
    • SamadhiWeb
Re: How Do I Add A Password to a SQLite Database File?
« Reply #10 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.

GetMem

  • Hero Member
  • *****
  • Posts: 3948
Re: How Do I Add A Password to a SQLite Database File?
« Reply #11 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.
 

jcmontherock

  • Full Member
  • ***
  • Posts: 158
Re: How Do I Add A Password to a SQLite Database File?
« Reply #12 on: December 06, 2022, 05:59:40 pm »
Simply, you can use SQLCipher library. I use it and it's work fine.

ttomas

  • Full Member
  • ***
  • Posts: 236
Re: How Do I Add A Password to a SQLite Database File?
« Reply #13 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.

PierceNg

  • Sr. Member
  • ****
  • Posts: 290
    • SamadhiWeb
Re: How Do I Add A Password to a SQLite Database File?
« Reply #14 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), 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.

 

TinyPortal © 2005-2018