How to decrypt an encrypted sqlcipher database file on command line?

Question

The question is simple

What I have is:

• I have a database file which is encrypted using sqlcipher.
• I also have the passphrase which was used to encrypt this db file

What I need is:

• I need to decrypt the database file/ need a database file which is unencrypted/non encrypted/decrypted.

Telespectroscope

Answer 1

Download and Build sqlcipher

--Skip this if sqlcipher is already installed

Pull the code from https://github.com/sqlcipher/sqlcipher in a directory (say ~/sqlcipher)

mkdir ~/bld;        #  Build will occur in a sibling directory
cd ~/bld;           #  Change to the build directory
../sqlcipher/configure --enable-tempstore=yes CFLAGS="-DSQLITE_HAS_CODEC" LDFLAGS="-lcrypto"; 
                    #configure sqlcipher 

make install;       #  Install the build products

Decrypt the database to a plaintext database

$ cd ~/;
$ ./sqlcipher encrypted.db 
sqlite> PRAGMA key = 'testkey'; 
sqlite> ATTACH DATABASE 'plaintext.db' AS plaintext KEY '';  -- empty key will disable encryption
sqlite> SELECT sqlcipher_export('plaintext'); 
sqlite> DETACH DATABASE plaintext; 

Find the decrypted database at ~/plaintext.db which you can use with any sqlite browser like this.

Update : September 2015

http://sqlitebrowser.org now supports sqlcipher databases. That's neat.

Telespectroscope

Hi @vinaywadhwa can you say how I can do this for windows 7?. – Sukkah

The process to build on windows is a complex one. You could use/develop an app on android to do that if that's an option - something like play.google.com/store/apps/details?id=aca.db.tool – Telespectroscope

On Ubuntu I had to install "libssl-dev" and "libcrypto++-dev" otherwise I was getting error at time of configure. – Aristotelianism

how can i implement it in objective c? – Broca

"file is encrypted or is not a database" i got error. when i attach database – Alena

One of machine has installation version "3.8.6 2014-08-15 11:46:33 9491ba7d738528f168657adb43a198238abde19e" with this above worked fine. On another machine with install version 2.2.1 2013-05-20 00:56:22 it's giving same error what @AjayBhayani mentioned. – Flour

This worked great, thanks! I also confirmed that SQLite Browser does indeed support decrypting the database, but the GUI isn't very fun to use. I much prefer Base so I used sqlcipher to decrypt it. I built an interactive bash script that does a lot of the heavy lifting, if anyone is interested. – Lampedusa

Btw, if you're on macOS and use Homebrew, you can install sqlcipher with brew install sqlcipher. – Lampedusa

This may help also PRAGMA cipher_migrate; – Bedfellow

Answer 2

Use SQliteStudio

Select SQLiteCipher and enter the password. The database will be opened.

Olin

Answer 3

This shell script will decrypt a SQLCipher database called mydb.db and create one called mydb-decrypt.db. Params are $1=key, $2, path to read & write from.

#!/bin/bash
echo "Decrypting $2 using key $1"
echo "PRAGMA key='$1';select count(*) from sqlite_master;ATTACH DATABASE '$2/mydb-decrypt.db' AS plaintext KEY '';SELECT sqlcipher_export('plaintext');DETACH DATABASE plaintext;" | sqlcipher $2/mydb.db
echo "Done."

If you wanted to do this in a single command line, the guts of this are:

echo "PRAGMA key='$1';select count(*) from sqlite_master;ATTACH DATABASE '$2/mydb-decrypt.db' AS plaintext KEY '';SELECT sqlcipher_export('plaintext');DETACH DATABASE plaintext;" | sqlcipher $2/mydb.db

Invite

Answer 4

Building on the previous answers , I have a comprehensive answer. I have the configuration- OS X version - 10.10.4 Steps : 1. Donwload and build OpenSSL code:

$ curl -o openssl-1.0.0e.tar.gz https://www.openssl.org/source/openssl-1.0.0e.tar.gz
$ tar xzf openssl-1.0.0e.tar.gz
$ cd openssl-1.0.0e
$ ./Configure darwin64-x86_64-cc
$ make

2. Download and build SQLCipher code.

In another directory,

$ git clone https://github.com/sqlcipher/sqlcipher.git
$ cd sqlcipher

Change '/path/to/libcrypto.a' in the following command to your path

$ ./configure --enable-tempstore=yes CFLAGS="-DSQLITE_HAS_CODEC" LDFLAGS="/path/to/libcrypto.a"
$ make

3. Decrypt to plaintext database (As illustrated in previous post by Vinay)

   $ cd ~/;
   $ ./sqlcipher encrypted.db 
   sqlite> PRAGMA key = 'testkey'; 
   sqlite> ATTACH DATABASE 'plaintext.db' AS plaintext KEY '';  -- empty key will disable encryption
   sqlite> SELECT sqlcipher_export('plaintext');
   sqlite> DETACH DATABASE plaintext;
   

Tis should help you decrypt the encrypted file...

Paragraphia