Great Job David!

[davbaron]

Clearly this is not an 'issue'. I too needed to include an encrypted db inside my Flutter app. The originator of the data (within the pre-loaded database) did not want users 'copying and emailing' the db file to other non-paying users. Encryption is the way to go then. I downloaded DB Browser for SQLite... created a test database, loaded it with some data (4,000 rows in a single table for an example), encrypted it, and copied it into my Flutter assets. Then I referenced the plug-in on David's site, followed some example code on the 'net, and boom, it works like a charm. 4,000 rows are almost instantly loaded into a true object list.

Great work David! Do you think you will 'finalize' this project and host it at pub.dartlang.org?

Thanks again,

-David
Centennial, CO USA

[davidmartos96]

@davbaron That's great to hear! Publishing it was not in my plans but maybe I'll give it a spin again and perhaps publish 1.0 version.

[hellowtisch]

@davidmartos96 Does your code allow to create encrypted databases or does it require an encrypted db to be there already?
Also, do you have a sample on how to open an encrypted db? When I tried to add the password as a separate argument to the openDatabase method in your example, I got an error that only 1 argument is allowed.

[davidmartos96]

@hellowtisch It is the same behavior as normal sqflite, you can open a prepopulated db from assets or let the library create an empty one in the device.
I have just added a new test page with both cases opening an encrypted database.

sqflite_sqlcipher/sqflite/example/lib/sqlcipher_test_page.dart

Line 12 in 8f7a389

test('Open and query database', () async {

The example project not picking up the parameter password, that was my bad. The pubspec.yml of the example wasn't up to date. Sorry for that.

[gongqinjing]

nice! thank you, David!

[davidmartos96]

The package has been published in pub.dev sqflite_sqlcipher 🎊 🎊

[MojtabaTavakkoli]

Clearly this is not an 'issue'. I too needed to include an encrypted db inside my Flutter app. The originator of the data (within the pre-loaded database) did not want users 'copying and emailing' the db file to other non-paying users. Encryption is the way to go then. I downloaded DB Browser for SQLite... created a test database, loaded it with some data (4,000 rows in a single table for an example), encrypted it, and copied it into my Flutter assets. Then I referenced the plug-in on David's site, followed some example code on the 'net, and boom, it works like a charm. 4,000 rows are almost instantly loaded into a true object list.

Great work David! Do you think you will 'finalize' this project and host it at pub.dartlang.org?

Thanks again,

-David
Centennial, CO USA

Hi.
Thanks for your instruction story :D. It really helped me to understand I'm in the right library finally. Since I'm a newbie into the database things I would like to ask you if possible please tell in details that:

1. how safe is this way?
2. how did you encrypted the database?
3. what is the password we have to use while we're trying to open database? Is it the encryption key or we have to set a password for the database? sample code below:
   Database db = await openDatabase( path, password: "my password", // onCreate, onUpgrade... );
4. If we have to set a password for database then how should we do it?
5. can you please provide a link to that project examples which you mentioned?
6. is there any example code for this kind of usage of the database?

Thank you in advance. <3

[davidmartos96]

@MojtabaTavakkoli This is just a wrapper around the SQLCipher native libraries. You can research about SQLCipher online to see if it is appropriate for your use case.
With the library you have the option to create an encrypted database from scratch inside the device (you choose the password), or open an existing SQLCipher database, which could have been created using the SQLCipher binaries for Windows, Linux or macOS.
I'd encourage you to do some research about Sqlite databases. The usage of the library is the same as the sqflite library, for which there are lots of tutorials online.

[MojtabaTavakkoli]

Thanks for your response and great library.
well I did some research as you mentioned. I know the General workout and usage of the sqlite and cipher. But mostly I'm looking for some simple example code that I can get my feet wet.

[davidmartos96]

@MojtabaTavakkoli If you are looking for examples on how to use the library I'd recommend checking out the sqflite docs which show how to insert and query a database.
https://github.com/tekartik/sqflite/blob/master/sqflite/doc/how_to.md

There are also a couple of resources with more complex scenarios.
https://github.com/tekartik/sqflite/blob/master/sqflite/doc/external.md

All of that applies to this package. Encryption is just an extra.

[MojtabaTavakkoli]

Thanks.
well actually my goal is that extra😅.
I have a preset sqlite database that want to be secured from users and any one who tries to decompile the app. It has to be filled before the first launch of the app with any GUI sqlite apps (like DB browser for sqlite or sqlite studio etc) and accessed and read data through the app.

[davidmartos96]

@MojtabaTavakkoli Alright, I see what you mean. I personally have the same use case as you. What I do is I create a preloaded database from my development environment (Linux), I encrypt it with a script using the SqlCipher binaries from the platform and then I copy it to the Flutter assets folder. Then, from the app, you would have to copy the asset database to the device itself before opening it.
Here is the script I use #20 (comment)

The example project in this plugin has an example where a database is copied from assets if you want to look at that.

[MojtabaTavakkoli]

Hola!
That sounds like what I exactly looking for🎉🎉🎉
I let you know how did it go
Thank you.

[MojtabaTavakkoli]

Hello.
Sorry I was abroad and was unable to Thank you for your great library. I found some tutorials and finally could encrypt the database and your library was working like a charm.
Thank you again and your responses.
Any time you needed a free design (up to 4 pages 😅) you can just mention me here or email me.
Have fun coding.

[Guillaume-Fortin]

just one question, i can't open my database on my pc (ubuntu). i had try with db browser without success.
the database is create on Android device with default parameter and a password.

j'ai besoin de connaître les parametres d'encryption par defaut .

do you have any information?

[davidmartos96]

@MojtabaTavakkoli Thanks! Happy you got it working.

@Thetyne I'm using SqliteBrowser on Linux, but in order to have SqlCipher support you need to compile it yourself. That is what I did and it works like a charm. You get an option to provide a password when opening.
Here is the link. It is pretty simple to compile. https://github.com/sqlitebrowser/sqlitebrowser/blob/master/BUILDING.md#build-with-sqlcipher-support

[MojtabaTavakkoli]

@MojtabaTavakkoli Thanks! Happy you got it working.

@Thetyne I'm using SqliteBrowser on Linux, but in order to have SqlCipher support you need to compile it yourself. That is what I did and it works like a charm. You get an option to provide a password when opening.
Here is the link. It is pretty simple to compile. https://github.com/sqlitebrowser/sqlitebrowser/blob/master/BUILDING.md#build-with-sqlcipher-support

yea its pretty simple and awesome. I came up to it in last searches but yet it was very handy so I'm using it from now on.

[davidmartos96]

@Thetyne I forgot to mention, you need to make sure that the sqlcipher version installed on your machine is 4.x and not 3.x. This is because the version of the database on Android will be 4.x. You can check it by running this on a terminal.

[davidmartos96]

@Thetyne Another option that may be simpler is to decrypt the database from command line and open it with regular SqliteBrowser to inspect.
This is a script I use:

#!/bin/bash
if [ -z "$1" ]
  then
    echo "No argument supplied"
    exit
fi

echo "Decrypting $1"
rm decrypted.db
echo "PRAGMA KEY = 'mypassword'; ATTACH DATABASE 'decrypted.db' AS decrypted KEY ''; SELECT sqlcipher_export('decrypted'); DETACH DATABASE decrypted;" | sqlcipher $1

echo "Done."

You can change the password in the script and run it like ./myscript.sh <dbpath>

[fauzy6493]

hi @davidmartos96 i cant open my db in native side..
im using this:
implementation "net.zetetic:android-database-sqlcipher:4.4.0"
implementation "androidx.sqlite:sqlite:2.1.0"

and this is my code to opendb

import net.sqlcipher.database.SQLiteDatabase;
import net.sqlcipher.database.SQLiteOpenHelper;
..........
SQLiteDatabase.openDatabase(DATABASE_PATH + "my.db",
                "password",
                null,
                SQLiteDatabase.CREATE_IF_NECESSARY);

it say "File is not database"..

what's wrong with mine?

[davidmartos96]

@fauzy6493 At first glance it looks like it's ok. It's basically how the database is opened in the plugin implementation. Are you able to open it from Flutter?

Make sure the following is correct:

• Path of the database
• Password
• If it's a database from assets, make sure that it was built with SQLCipher version 4.x

You could also create a small repo reproducing that behavior so that I can take a look at it, but it should work.

[fauzy6493]

@fauzy6493 At first glance it looks like it's ok. It's basically how the database is opened in the plugin implementation. Are you able to open it from Flutter?

Make sure the following is correct:

• Path of the database
• Password
• If it's a database from assets, make sure that it was built with SQLCipher version 4.x

You could also create a small repo reproducing that behavior so that I can take a look at it, but it should work.

1. path is ok
2. password is correct
3. i create database from flutter like this:

Directory directory = await getApplicationDocumentsDirectory();
    String path = directory.path + '/my.db';

    bool isDbExist = await databaseExists(path);
    if(isDbExist)
    {
      var todoDatabase = openDatabase(path, version: 1, password: "password");
      return todoDatabase;
    }else{
      var todoDatabase = openDatabase(path, version: 1, password: "password", onCreate: _createDb);
      return todoDatabase;
    }

and it work for crud operation in flutter. but if i open in native side.. it say "file is not database"

[fauzy6493]

how can i open it using terminal?
for make sure this file are correct.

i try this step for check in terminal:

• sqlite3
• sqlite> .open my.db
• sqlite> select * from mytable;
  Error: file is not a database

[davidmartos96]

i try this step for check in terminal:

• sqlite3
• sqlite> .open my.db
• sqlite> select * from mytable;
  Error: file is not a database

You can test it on a desktop terminal using sqlcipher, not sqlite3. To open it you would do the following. The PRAGMA KEY statement is important:

➜ sqlcipher my.db
SQLCipher version 3.30.1 2019-10-10 20:19:45
Enter ".help" for usage hints.
sqlite> PRAGMA KEY = "password";
ok
sqlite> SELECT * FROM sqlite_master;

Since the database is created on Flutter, you could create a small demo project so that I can test it out.

[fauzy6493]

@fauzy6493 At first glance it looks like it's ok. It's basically how the database is opened in the plugin implementation. Are you able to open it from Flutter?

Make sure the following is correct:

• Path of the database
• Password
• If it's a database from assets, make sure that it was built with SQLCipher version 4.x

You could also create a small repo reproducing that behavior so that I can take a look at it, but it should work.

hi @davidmartos96 my problem is solved, i forgot to change :
implementation "androidx.sqlite:sqlite:2.0.0" to implementation "androidx.sqlite:sqlite:2.1.0"
and rebuild gradle. and it work thank you very much!

[davidmartos96]

@fauzy6493 Why do you need androidx.sqlite:sqlite as a dependency? That will not encrypt the database. You need to use this one:
implementation 'net.zetetic:android-database-sqlcipher:4.4.0'

[fauzy6493]

@fauzy6493 Why do you need androidx.sqlite:sqlite as a dependency? That will not encrypt the database. You need to use this one:
implementation 'net.zetetic:android-database-sqlcipher:4.4.0'

omg, i guess i still need that dependency..
just now i try to remove androidx.sqlite:sqlite and still work..

Thank you.!

[fauzy6493]

i try this step for check in terminal:

• sqlite3
• sqlite> .open my.db
• sqlite> select * from mytable;
  Error: file is not a database

You can test it on a desktop terminal using sqlcipher, not sqlite3. To open it you would do the following. The PRAGMA KEY statement is important:

➜ sqlcipher my.db
SQLCipher version 3.30.1 2019-10-10 20:19:45
Enter ".help" for usage hints.
sqlite> PRAGMA KEY = "password";
ok
sqlite> SELECT * FROM sqlite_master;

Since the database is created on Flutter, you could create a small demo project so that I can test it out.

ok thank you, it work!

[ahzmie]

Hello,
I have a preset SQLite dB in my flutter app and I just want to set a password to it so I can open it later in the app by using the password parameter.
Can I set the password for my DB using DB Browser or I need something else? if else, Can you refer me to this way.
Thanks

[davidmartos96]

Hello,
I have a preset SQLite dB in my flutter app and I just want to set a password to it so I can open it later in the app by using the password parameter.
Can I set the password for my DB using DB Browser or I need something else? if else, Can you refer me to this way.
Thanks

Yes, you can set a password with the help of Sqlcipher. To do it you need to install SQLCipher v4.x on your development machine. Encrypt it using the CLI and then use that db in the Flutter app.
You can use the following script to encrypt your database with some password. I believe there is a version of Sqlite browser with Sqlcipher integration, but I prefer to set the password via the script to automate the process.

#20 (comment)