Sqlitebrowser: Open Encrypted Database

[techie31]

I can't seem to figure out how to open an encrypted sqlite database? If this is not currently possible, I think it would be an appropriate enhancement.

Thanks!

[piacentini]

It is a nice enhancement, but be aware that by itself SQLite does not include an encryption library. One is available from HWCI (http://www.hwaci.com/sw/sqlite/see.html), for a fee.
As sqlitebrowser is distributed with source we can not include SEE with sqlitebrowser. You can, however, license the extension and compile your own binary with SEE included (I have done this in the past for the Arca Database Browser, the product from which sqlitebrowser was derived from).

[rp-]

For Linux/MacOSX and the mentioned see extension this should already be possible if you build the shared sqlite library(with see) yourself and get it loaded before the default system sqlite lib.

For Windows you would need to build sqlitebrowser by yourself as the current builds are fully statically linked.

If you mean a different kind of encrypted sqlite database you would need to further explain.

[justinclift]

As a data point, SQLCipher might be useful to investigate:

https://github.com/sqlcipher/sqlcipher

[rahmadid]

Hi,
Yes, please add support for SQLCipher.
Thanks.

[techie31]

Thanks for all of the comments/suggestions. I'm pretty sure I don't have the know-how to link in an encryption library and compile my own version of the software for Windows. If anyone can do this and make it available for download, that would be great.

[justinclift]

... and if there are clear steps that can be scripted, I can probably add nightly builds on OSX including it. :smile:

[Z4us]

"Coolissimo", Justin, will make the upcoming version "awesomer" than it already would be without this free and open encryption {isn't this kind of a "contradictio in terminis" ?-)} Can't wait :-)

[Z4us]

This is specifically useful for databases/tables containing (or going to contain) passwords like one I'm creating and e.g. Fossil. Of the latter passwords are in normal UTF-8....

[justinclift]

Awesome! Martin's code for SQLCipher is working (very easily too). Here is an initial "once off" build for OSX with SQLCipher support enabled:

    http://mirror.salasaga.org/sqlitebrowser/onceoffs/sqlitebrowser_201411051855.dmg

That successfully opened a simple test database, allowing me to see the data structures and browse the test data in there.

Everyone that can, please try it out and report back success/failure/etc for this! :grinning:

[justinclift]

Apologies for the rough screenshots. I'm screenshoting them through a remote session to an OSX box, then cutting out the appropriate window pieces using the Gimp. Not fast, and it's been ages since I used the Gimp. :wink:

[justinclift]

New build for OSX available:

    http://mirror.salasaga.org/sqlitebrowser/onceoffs/sqlitebrowser-sqlcipher_201411101930.dmg

This one changes the default page size to 1024 (as recommended by the SQLCipher dev's), and also (should) fix a weird inconsistency of (previously) not always opening encrypted files properly.

Please try it out, and let us know how it goes. :)

[justinclift]

Btw, this is being discussed and tried out by the SQLCipher devs+users on their upstream Forum here:

    https://discuss.zetetic.net/t/sqlcipher-support-being-added-to-db-browser-for-sqlite/243

[techie31]

Although I don't get a lot of time to play around with this, I have been keeping track of the comments and enhancements. It's great to see that even with "free" software, there is some support and people that care to enhance it.

I just downloaded what I thought was the latest version for Windows. I didn't see any of these changes in it. Is there any possibility of getting a test/beta version that has the new encryption ability rolled up into it?

Thanks!!

[justinclift]

Not for Windows yet. We build the Windows binaries by cross-compiling from a Linux system using MXE SQLCipher doesn't seem to be available in MXE yet, so we're not sure of the right approach to take.

We may need to get SQLCipher into MXE first, and then build our Windows binaries with SQLCipher support using that.

[justinclift]

Thanks to @MKleusberg, our SQLCipher on *nix based platforms is up and running. :smile:

Our OSX nightly builds also include a SQLCipher enabled version now. For example, today's builds:

    http://mirror.salasaga.org/sqlitebrowser/nightly/sqlitebrowser-sqlcipher_20141114.dmg
    http://mirror.salasaga.org/sqlitebrowser/nightly/sqlitebrowser_20141114.dmg

Next, we need to figure out the Windows side of things. Anyone have ideas? :question:

[MKleusberg]

So then, I've tried my luck creating a sqlcipher package for mxe. I'm not really sure how to test this in a not insanely large amount of time because mxe doesn't support my operating system version and installing an older one in a virtual machine and then compiling gcc and all these dependencies in there would take forever.
But if someone is willing to try it (@rp- maybe?), here's the mxe package: https://github.com/MKleusberg/mxe/tree/add-sqlcipher
If it turns out to be working we can open a pull request at mxe and have nightly builds for Windows :)

[justinclift]

Hey, that's cool. :grin:

@rp- Do you have time to try out the SQLCipher package? Also, which version of Arch are you using? We might be able to get build VM's for that online (eg the Rackspace sponsorship thing), so we can all access it and try stuff out on it.

[rp-]

maybe tomorrow, pretty busy right now.

[justinclift]

k, no worries. Which version of Arch are you running, that MXE is known to work on for cross-compiling for us? I won't have time to look at it this weekend, but it's a useful data point in case someone does have time in the future. :smile:

[rp-]

Arch has no version, It's a rolling release distribution.
But mxe is not tied to a distribution anyway, it compiles its own cross compiler and so on...
http://mxe.cc/#requirements

[justinclift]

Ahhh, I'd forgotten that about Arch. Installed it in a VM one time, then haven't touched it since. One day... :wink:

[rp-]

@MKleusberg cherry-picked your sqlcipher.mk but couldn't get it compiled.
I'm using the stable branch of mxe.

libtool: compile:  gcc -Os -DSQLCIPHER_CRYPTO_OPENSSL -DSQLITE_OS_UNIX=1 -I. -I./src -I./ext/rtree -D_HAVE_SQLITE_CONFIG_H -DBUILD_sqlite -DNDEBUG -I/usr/include -DSQLITE_THREADSAFE=1 -DUSE_TCL_STUBS=1 -c ./src/tclsqlite.c -o tclsqlite.o >/dev/null 2>&1
cp tsrc/shell.c tsrc/sqlite3ext.h .
./libtool --mode=compile --tag=CC gcc   -Os -DSQLCIPHER_CRYPTO_OPENSSL -DSQLITE_OS_UNIX=1 -I. -I./src -I./ext/rtree -D_HAVE_SQLITE_CONFIG_H -DBUILD_sqlite -DNDEBUG  -I/usr/include -DSQLITE_THREADSAFE=1    -DSQLITE_TEMP_STORE=1 -c sqlite3.c
libtool: compile:  gcc -Os -DSQLCIPHER_CRYPTO_OPENSSL -DSQLITE_OS_UNIX=1 -I. -I./src -I./ext/rtree -D_HAVE_SQLITE_CONFIG_H -DBUILD_sqlite -DNDEBUG -I/usr/include -DSQLITE_THREADSAFE=1 -DSQLITE_TEMP_STORE=1 -c sqlite3.c  -fPIC -DPIC -o .libs/sqlite3.o
sqlite3.c:28261:40: error: ‘mmap’ undeclared here (not in a function)
   { "mmap",       (sqlite3_syscall_ptr)mmap,     0 },
                                        ^
sqlite3.c:28264:42: error: ‘munmap’ undeclared here (not in a function)
   { "munmap",       (sqlite3_syscall_ptr)munmap,          0 },
                                          ^
sqlite3.c:28268:42: error: ‘mremap’ undeclared here (not in a function)
   { "mremap",       (sqlite3_syscall_ptr)mremap,          0 },
                                          ^
sqlite3.c: In function ‘unixShmMap’:
sqlite3.c:32275:36: error: ‘PROT_READ’ undeclared (first use in this function)
             pShmNode->isReadonly ? PROT_READ : PROT_READ|PROT_WRITE, 
                                    ^
sqlite3.c:32275:36: note: each undeclared identifier is reported only once for each function it appears in
sqlite3.c:32275:58: error: ‘PROT_WRITE’ undeclared (first use in this function)
             pShmNode->isReadonly ? PROT_READ : PROT_READ|PROT_WRITE, 
                                                          ^

....

[rp-]

-DSQLITE_OS_UNIX=1

I think this is wrong.

[MKleusberg]

Googling the error messages brought me to https://github.com/sqlcipher/sqlcipher/issues/78 where it is suggested to add a define which I did in https://github.com/MKleusberg/mxe/commit/569a2be7448108c556e5436137ff4e4179c457bd
So maybe this new version will work. I hope.

[justinclift]

We'll get there. Persistence is awesome. :grinning:

[Z4us]

I'm testing the version with sqlcipher with success under osx 10.6. One question: can I add a parameter like e.g. a filename when I start via the command line of Terminal?

[justinclift]

Hmmm, that's a good question. If it's a normal SQLite database file (not encrypted), it should open the file (as per normal) when it's given on the command line. If the file is an encrypted one though, I'm unsure what it'll do. Hopefully it'll attempt opening it, and then pop up a dialog asking for a password.

We should probably add command line switches for giving the page size and password on the command line or something though.

Hmmm, password on the command line might be a security risk. That could probably let other users on the computer see it. eg by anyone running (say) 'ps -ef' to get a process list. We'll probably need to think of a better approach. Suggestions welcome. :wink:

[Z4us]

Of course we don't enter the password visible on the command line, but things like file-path and name, eventually some options like page-size if it's not the default of 1024 bytes. I asked this to get only the question for a password to any user and in case of a wrong answer they have no access (or just read views, their "own" data etc.

[MKleusberg]

@Z4us Yes, you can pass a path to an encrypted database on the command line:

$ ./sqlitebrowser path/to/encrypted.db

At the moment it's not possible to specify the password or the page size using a command line argument though; so it'll just show you the standard encryption dialog in this case.

[Z4us]

Cool @MKleusberg I may have found a bug. When I enter the following SQL-statement:

pragma application_id;

it works well in the GUI (returns 0 - changing it fails), but returns the following error-message to Terminal

2014-12-05 08:29:13.251 sqlitebrowser[20563:903] Failed to get CharCodes from EventRef (-9870)

Not with some other pragmata; didn't try them all yet.

[justinclift]

@Z4us Any idea if this happens with the SQLite version too (not just SQLCipher version)?

If it's for both, lets create a brand new bug report for it. :smile:

[Z4us]

Only with encrypted databases unless the bug was introduced after 25 november the last nightly build normal I installed. I need to do some more tests, but it seems that for an encrypted database the application always returns 0 for application_id even if this field was filled with another value before encryption; I'd like to see this one in the 'Edit Pragmas' tab...

The problem seems solved in the build of 7 December. I changed application_id successfully. Thanks! Good job!!

[justinclift]

Wondering if our new build system for windows would let us add SQLCiper support there...? :smile:

[techie31]

Just tried the latest version again for Windows.
Is there any hopes for a Windows version that will open an encrypted database?

Thanks!

[justinclift]

Hmmmm, it might be possible to make work, now that we're using a new build system.

Someone would have to go through the effort of figuring out how to compile SQLCipher with our source on Windows though. Do you happen to have skill with C++/Qt on Windows? :smile:

[techie31]

Unfortunately, no. I can fumble my way through maintaining some C++ web code, but other than that, I wouldn't be much help.

[justinclift]

As a useful bit of info, @huy-cong managed to compile DB4S + SQLCipher on Windows.

Ideally, we should get the exact requirements + steps for doing so added to our BUILDING.md file. It may make sense to do it directly in there, or it may be better to assemble something in the wiki. No real preference at the moment... though using the wiki would allow for including screenshots. The level of detail needed is probably something like:

• Install Microsoft Visual C++ (which version? Is the free one ok? Include URL to the direct download page if possible)
• Install SQLCipher (which version?, etc, as above)
• Update the hard coded paths in CMakeLists.txt to match your installation:
  
  
  
  • QT5_PATH
  
  
  • SQLITE3_PATH
  
  
  • OPENSSL_PATH
  
  
  • VSREDIST
  
  
• Instructions on how to compile? (GUI? Command line?)

We just need a (even rough) initial set of steps that work. It can be refined & made to look pretty after that. :smile:

[glawrence]

I am working on getting a good and accurate set of steps with what version of what I got from where and exactly what I did. Using a VM means I can go back and repeat, I just need more time but it is coming together, slowly and will be on the wiki when I am done

[huy-cong]

Hi,
I will try my best to explain in detail my build set:

• Miscrosoft visual studio c++ 2013, but I think any version is ok, link download: https://go.microsoft.com/fwlink/?LinkId=517284
• SQLCipher: I just try to link with 2 differents versions, and it works all fine:
  
  
  
  • 1st one is SQLCipher C/C++ DLL for Windows that I requested for a 30 days trial on their main site (https://www.zetetic.net/sqlcipher/trial/)
  
  
  • 2nd one is a self compilation (mingw32 + code source: https://www.zetetic.net/sqlcipher/open-source), here's a guide link: http://stackoverflow.com/questions/29196087/step-by-step-guide-to-build-qt-sql-driver-plugin-for-sqlite-db-with-sqlcipher-ex (part 5)
  
  
• These variables in CMakeLists.txt must be modified to match your preferences:

```

• QT5_PATH
• SQLITE3_PATH (path to foremost folder of SQL Cipher)
• OPENSSL_PATH
• VSREDIST
• LIBSQLITE_NAME (sqlcipher or sqlite3 depends on the name of your .dll file)
• SQLITE3_INCLUDE_DIRS (sqlite3.h)
• SQLITE3_LIBS (name of your .dll file)
  ```
  
  
  
  • Then everything left is just open sqlitebrowser MSVC solution then build
    
    I'm sure that I might miss something, so if there anything wrong, just ask here, I will look back at my build and answer, for now that's all I remember.
  
  

Cheers,

[glawrence]

That's nice @huy-cong thanks for that

[justinclift]

If anyone on 64-bit windows wants to try out an initial version of DB4S with SQLCipher support added in, there's a once-off package built with it here:

    http://nightlies.sqlitebrowser.org/win64/sqlitebrowser-win64-with_sqlcipher-201605041823.exe

Feedback very much welcome. :smile:

Note - I'm still working on smoothing out the integration bits, so hard coding override values in our files isn't needed for SQLCipher to be found. The actual SQLCipher code in the above package should be good as-is though.

[karlosp]

Link to 64-bit windows version is broken.

[justinclift]

Yeah, sorry the server hosting for it is presently at Linode. And they really, really suck. (eg the server flatlined, and didn't get reported)

Rackspace have offered us hosting, I just need to get around to setting it up. :smile:

In the meantime, the 64-bit Windows version with SQLCipher in it is back online.

[Shubhampatni86]

You can try with SQLCipher setup on Ubuntu mentioned here in SO thread.

[justinclift]

Yeah, SQLCipher support definitely works for *nix based OS's (eg Ubuntu).

[justinclift]

Closing this, as we've definitely figured out SQLCipher support on Windows too, and it'll be in the upcoming release. :smile:

[ligadata-alison]

Can someone please help me? I don't know what it means "Please enter the key used to encrypt the database". I downloaded the database from Bluehost. Do I call them to get the key??

[revolter]

A database is encrypted with a password. You have to know that password when trying to open an encrypted database.

[ligadata-alison]

yes, but there do I get the password? Where do I find it?

[ligadata-alison]

I'm a technical writer (not a developer). I'm not sure what I am doing. I backed up my website and I have this file called kamanjao_kaman.sql that I'd like to view to make sure my pages are there.

[ligadata-alison]

is it found in wp-config.php?

[chrisjlocke]

Aah, an .sql file is a series of SQL commands to _recreate_ a database, rather than the _actual_ database. You can open the .sql file in Notepad, or its equivelant (depending on your operating system).

While its human readable text, it might not make much sense to you... (ie, full of gobbledegook! :wink: )

[justinclift]

@ligadata-alison If that's a backup of a Wordpress website, then a text editor should let you see part of the contents, as @chrisjlocke pointed out.

The file itself probably has internal formatting / structure purely for Wordpress to understand though. So it's very likely not going to be super readable.

All of the text strings from the Wordpress site should be present in it, if you're wanting to just verify the backup worked at all.

If the data itself is super important to you, you should try restoring that data to a blank new Wordpress setup, to verify that works.

It might be time consuming to learn how, but it's the only way to know "for sure" you have everything needed. :smile:

Does that help at all?

[ligadata-alison]

Thanks to lulian, Chris, and Justin! Thanks for your detailed reply. I
think I will restore the data to a blank Wordpress setup to verify as
Justin suggests. It's simply to hard to read otherwise. Thanks for the
suggestion!

[justinclift]

You're welcome @ligadata-alison. :smile:

[roneqwerty]

so any way to decrypt the sqlite file encrypted by sqlite cipher without know the password? if you know please send me the way on [email protected]

[justinclift]

without know the password?

There's no practical way to do this.

[chrisjlocke]

If there was, there would be no benefit to encrypting it...

[roneqwerty]

Thank you for your response.
no way? am try to find way ...because always door have a backdoor. newest all companies try to make a program like this . so we have to find way. rone

[chrisjlocke]

You can brute force the password with every possible combination, but even with 7 bytes, you're looking at 7.20e16 possible combinations. Good luck with that. 😆

[roneqwerty]

I will try to do my best...nothing is possible :-) rone

[justinclift]

With using a brute force solution... if you used several rooms filled the very best computer equipment existing right now, it would probably take you several centuries to get the solution. For just one encrypted database. In reality, it would probably a lot longer though.

But, you're welcome to try. Nothing is stopping you. :smile:

[justinclift]

because always door have a backdoor

That is only the case where someone has made a mistake, or where someone (government?) has added a weakness on purpose and hopes no-one else finds out about it.

If the encryption we use, called SQLCipher, has this problem, we don't know about it. If you do find a backdoor in it, please let us know. :smile:

[chrisjlocke]

Or where someone has added a weakness on purpose

Only if you log in as Joshua.....

If you hack into the DB4S servers and you get an option to play Global Thermonuclear War, look for 'chess' instead...

[aquiles2k]

@justinclift So i guess you didnt have success cross compiling with mxe? Are there any infos on what went wrong and if its entirely impossible? We are currently also using mxe and would like to have sqlcipher support. Any answer appreciated :)

[justinclift]

@aquiles2k We went with a different approach, changing to compiling directly on Windows with MSVC (yuk) instead. That let us drop the need for using MXE, so we use SQLCipher directly instead.

Maybe that's something worth looking into?

This might be useful reference info: https://github.com/sqlitebrowser/sqlitebrowser/wiki/Win64-setup-%E2%80%94-Compiling-SQLCipher

Also, I have a fork of the SQLCipher repo with the changes from the above doc in it, which our nightly build script uses for the SQLCipher builds. Might be useful to look at the last few commits for that, to see the changes made to get it working (for us). :smile:

• https://github.com/justinclift/sqlcipher

Alternatively, if you ping the SQLCipher people and ask about MXE, they're pretty friendly so might have useful ideas or suggestions. :smile:

• https://discuss.zetetic.net
• https://github.com/sqlcipher/sqlcipher

[roneqwerty]

Actually they used like winrar encryption type and the way to get any password on DB ( surly if its protected by SQLcipher is brute force with good dictionary). But always there is another way to played inside db from DLL files if its related so depends in your case

[aquiles2k]

@justinclift Ok, thank you very much for your detailed answer!

For various reasons we try to avoid msvc 😄 So i'll check your provided links and try some time with mxe.

As a backup plan we will just use msys2 with mingw-packages as they also started to provide a sqlcipher package.

• https://github.com/Alexpux/MINGW-packages/commit/f083f27fb163b68673d5ac649fae353f44b49c7d

[roneqwerty]

You welcome anyway… just check this file its encrypted by sqlcipher it included expiry time inside it , so if you know any way just to modify what's inside it, appreciated to help me.

Thank you so much in advance.

[justinclift]

@aquiles2k Cool. :smile:

That's good news about SQLCipher being available for msys2 now. That should help spread it's adoption. :smile:

Depending on how paranoid you're feeling :wink:, you might want to add this patch to the MINGW-packages build (maybe send them a PR?):

• https://sqlite.org/src/vpatch?from=0db20efe201736b3&to=66de6f4a9504ec26

It fixes CVE-2017-10989, present in SQLite versions prior to 3.20.0, and also present in SQLCipher 3.4.1.

If you're not using the SQLite "RTree" module though, that CVE won't affect you. :smile:

[roneqwerty]

Cool I tried many thing, but nothing reach …if you can help me on it I appreciate that by the way …if we did this one ….I think most of bigger games and software …we can fix it J

[roneqwerty]

Ok boss thank you for your time , I really like to work with professional people ….lets try and see ….good luck and have a nice day.

[aquiles2k]

@justinclift Just another info. MXE now also has a sqlcipher package.

https://github.com/mxe/mxe/pull/1931

[justinclift]

Good stuff @aquiles2k, that's well done. :smile: