Unable to decrypt database with command line

[Coder_RB]

I have successfully compiled my iphone app with SQLCipher. I have
confirmed that SQLCipher is creating and encrypting. I am apparently
able to decrypt within my app because the data is retrieved. I'm
getting some unexpected behavior so I need to look at the data. I
have also successfully compiled SQLCipher in a separate directory from
my Xcode version using the following commands:

./configure
--prefix=/path/to/install/target/sqlcipher CFLAGS="-
DSQLITE_HAS_CODEC"
LDFLAGS="-lcrypto"
make

When I try to open the database using the new command line bin. It
looks like it opens fine. I issue the PRAGMA key command and
everything looks OK. However, when I execute the query it tells me
that it can't open because the file is encrypted or not a database.

Please help! I am about to throw this thing out of the window.

What am I doing wrong?

[Steven Parkes]

Wrong key? Including the null in the key length in sqlite_key which won't match the cli which doesn't include the null?

[Coder_RB]

I used exactly the same key in my app code to create the file.

[Steven Parkes]

I do this routinely, so it's something in your setup.

Either the host-side binary isn't working (try creating a db with the cli with and without a key and make sure it's encrypted, e.g., can't be opened w/o the key). This would verify Billy's statement. I forget that sqlite3 ignores pragmas it doesn't understand.

Otherwise, the keys aren't the same. Your key had better be an ascii string to work via the cli, i.e., make sure you're passing it an ascii/UTF8 string on the iOS side (not an NSString).

And if you're doing that and using sqlite_key, make sure you're passing the number of characters correctly. I presume that means you don't include the null in the length, but I don't use sqlite_key so I couldn't say if it's getting that wrong.

[Stephen Lombardo]

Hello,

If the suggestions by Billy and Steven don't work, also double check to make sure that the database you are opening is *actually encrypted*. Either try running a query on the command line without running PRAGMA key first, or hexdump -C the database file and make sure it's actually encrypted. 

If the database is not encrypted by your application (due to a build problem or configuration problem in your app) then executing PRAGMA key on the command line with any value would cause that error. 

Cheers,

Stephen

[Billy Gray]

On Mon, May 16, 2011 at 1:12 PM, Coder_RB <rb...@skyetechsolutions.com> wrote:

When I try to open the database using the new command line bin.  It
looks like it opens fine.  I issue the PRAGMA key command and
everything looks OK.  However, when I execute the query it tells me
that it can't open because the file is encrypted or not a database.

Please help! I am about to throw this thing out of the window.

What am I doing wrong?

How are you calling the sqlite3 binary command? Typically you need to specify the path so that your shell knows to use the one you just compiled, and not some resident sqlite3 binary on your system that's inside your PATH.

Fer instance, if the binary I want to use is in my current working directory, this will do the trick (assume $ is the shell prompt):

$ ./sqlite3

If I just type it like this:

$ sqlite3

I'll get the one that comes with my local operating system, which doesn't understand the PRAGMA "key".

Cheers,

Billy

--
Team Zetetic
http://zetetic.net

[Coder_RB]

First of all, thanks so much for your help guys.

OK. I have figured out that the command line tool is not encrypting
or decrypting. My original database that I was trying to open and was
created with my app is indeed encrypted. See below.

MyComputers-MacBook-Pro:bin mycomputer$ hexdump -C /Users/mycomputer/
Tracker.sqlite
00000000 97 c4 9f b2 e7 d2 0f 1a eb 55 94 fa 72 03 88 b7
|.........U..r...|
00000010 77 ef f5 10 e6 c9 bb 5c 25 d0 eb 40 ad d3 be e1 |w......\
%..@....|
00000020 2b 61 46 13 5c e5 c9 a0 a3 f3 be 4d dd 4c 9b b3 |+aF.
\......M.L..|
00000030 e7 6f d4 d1 36 9f 8d dc bb 7b b8 a6 56 25 7f b7 |.o..6....
{..V%..|
00000040 1e b6 9c c1 77 bb 90 9e cd 0c 9f 9a bc ed 09 a2
|....w...........|
00000050 b6 29 00 cd 05 e4 0b 4e ff da c0 66 7b 01 f7 cb
|.).....N...f{...|
00000060 d0 d4 b5 a1 7e f1 67 98 7a 08 3e 60 c9 db fe 81
|....~.g.z.>`....|
00000070 c5 e0 e4 6b 88 a8 94 67 ac c9 e2 a5 d8 ea 0b 51
|...k...g.......Q|
00000080 cb 8c 95 99 1e 05 3b 07 16 fc 5d 78 69 d8 56 ca
|......;...]xi.V.|
00000090 2c f5 ee e3 54 32 fd ba 61 2d 7a 5c a9 e8 44 5a |,...T2..a-
z\..DZ|
000000a0 e4 35 4e 2d 57 2d 45 af 4d f1 c1 df 72 62 1d 4a |.5N-W-
E.M...rb.J|

I must not have set up the command line sqlcipher correctly.

Can someone please send me the exact instructions on how to install
and compile it for use by command line? I have tried piecing together
many different instructions from various posts.

What should my directory structure look like for openssl and
sqlcipher? What commands should I execute to compile it?

On May 16, 2:03 pm, Stephen Lombardo <sjlomba...@zetetic.net> wrote:
> Hello,
>
> If the suggestions by Billy and Steven don't work, also double check to make sure that the database you are opening is *actually encrypted*. Either try running a query on the command line without running PRAGMA key first, or hexdump -C the database file and make sure it's actually encrypted.
>
> If the database is not encrypted by your application (due to a build problem or configuration problem in your app) then executing PRAGMA key on the command line with any value would cause that error.
>
> Cheers,
> Stephen
>
>
>
> On Monday, May 16, 2011 at 1:49 PM, Steven Parkes wrote:
> > I do this routinely, so it's something in your setup.
>
> > Either the host-side binary isn't working (try creating a db with the cli with and without a key and make sure it's encrypted, e.g., can't be opened w/o the key). This would verify Billy's statement. I forget that sqlite3 ignores pragmas it doesn't understand.
>
> > Otherwise, the keys aren't the same. Your key had better be an ascii string to work via the cli, i.e., make sure you're passing it an ascii/UTF8 string on the iOS side (not an NSString).
>
> > And if you're doing that and using sqlite_key, make sure you're passing the number of characters correctly. I presume that means you don't include the null in the length, but I don't use sqlite_key so I couldn't say if it's getting that wrong.

[Stephen Lombardo]

Hi,

You should be able to use these commands to build the source (which are similar to the one you used earlier):

./configure -enable-tempstore=yes --prefix=/path/to/install/target/sqlcipher CFLAGS="-DSQLITE_HAS_CODEC" LDFLAGS="-lcrypto"

make

Then, as Billy mentioned in an earlier thread, you need to make sure you are running the sqlite3 binary in the current directory by preceding it with a ./, i.e.

./sqlite3 /pat/to/database

If you don't prefix the command with ./ then it will most likely use your system default sqlite3 which isn't compiled with SQLCipher support. 

Can you give that a try?

Cheers,

Stephen

[Coder_RB]

It worked!!! I was able to open up the database that my app created
finally. I thought I did all the steps before but I have included it
below for others that may want to do the same thing. Thanks so much
for your help!!

1. Downloaded sqlcipher 1.1.8 from SQLCipher.net
2. Extracted source code to folder called sqlcipher-local
3. Compiled source
⁃ My-computers-MacBook-Pro:~ Mycomputer$ cd /Users/Mycomputer/
Development/SQLCipher-local
⁃ My-computers-MacBook-Pro:SQLCipher-local Mycomputer$ ./configure -
enable-tempstore=yes --prefix=/Users/Mycomputer/Development/SQLCipher-
binaries CFLAGS="-DSQLITE_HAS_CODEC" LDFLAGS="-lcrypto"
⁃ checking build system type... i386-apple-darwin10.6.0
⁃ checking host system type... i386-apple-darwin10.6.0
⁃ checking for gcc... gcc
⁃ ***OUTPUT OMITTED***
⁃ config.status: creating Makefile
⁃ config.status: creating sqlite3.pc
⁃ config.status: creating config.h
⁃ config.status: executing libtool commands
4. Make
⁃ My-computers-MacBook-Pro:SQLCipher-local Mycomputer$ make
5. Create encrypted database with new sqlite3 binary.
⁃ My-computers-MacBook-Pro:SQLCipher-local Mycomputer$ ./sqlite3 /
Users/Mycomputer/enc_test.sqlite
⁃ SQLite version 3.7.2
⁃ Enter ".help" for instructions
⁃ Enter SQL statements terminated with a ";"
⁃ sqlite> PRAGMA key = 'testpw';
⁃ sqlite> CREATE TABLE Accounts ("pk" INTEGER PRIMARY KEY
AUTOINCREMENT NOT NULL UNIQUE , "description" VARCHAR NOT NULL ,
"username" VARCHAR NOT NULL , "password" VARCHAR);
⁃ sqlite> PRAGMA key = 'testpw';

[Ted Rolle Jr.]

I've had similar problems with my SQLCipher.  ./configure issued the message "
checking for Tcl configuration... configure: error: no directory doesn't contain tclConfig.sh"
find / -name tclConfig.sh gave no result (I'm running on Ubuntu).
The TCL absence is a show-stopper.
What to do, including installing TCL from source to provide the necessary files?  This, of course, results in two copies of TCL, but Ubuntu installs software in non-standard places.

Ted

--
+-------------------------------------------------------------------------------------------------+
| 3.14159 26535 89793 23846 26433 83279 50288   May the Spirit     |
|   41971 69399 37510 58209 74944 59230 78164      of pi spread      |
|   06286 20899 86280 34825 32411 70679 82148  around the world.  |
|   08651 32823 06647 09384 46095 50582 ...        PI VOBISCUM!    |
+-------------------------------------------------------------------------------------------------+